· Dec 13, 2017

SQL or Cache Function?

Hi -

I'm creating a custom report in Health Insight via SQL and one of the requirements says that I must include patients who are >= 13 years old (which is easy) but I also need to include patients who WILL be 13 years old as of December 31st of current year that the report is ran in.

I see that there is an Age column and an AgeInMonths column in HSAA.Patient. and of course a DOB column. I'm wondering if it is possible to do this purely using SQL (and SQL functions) or if I will have to incorporate a COS function to achieve this requirement.

Any suggestions?



Discussion (12)1
Log in or sign up to continue

Why so difficult?
This similarly following condition:

year(current_date) - year(DOB)
) >= 13

It Besides above was already indicated, why does not follow to use such a code, for example:

select datediff(year,
todate(to_char({'1990-12-31'},'YYYY')||':1','YYYY:MM'), -- birthday
todate(to_char({'2003-01-01'},'YYYY')||':1','YYYY:MM') -- report date

This gives an incorrect result - 13, although it should be 12.

For better performance you should make a few changes:

  1. add an index on DOB field
  2. rebuild the index and run TuneTable
  3. modify the query
    select DOB from HSAA.Patient
    or dob between
      and todate((year(current_date)-13)||'1231','yyyymmdd')

The result will pleasantly surprise you.

Another approach, which would give you all patients 13 years old and older in the current calendar year is to write something like this:


This is just an example and there are probably different ways to write this to ensure you're executing the query in the most efficient way possible (if that is important for your needs).

Hi - we may be overcomplicating the solution here. Rather than comparing the age for every record, all you need is to work out a single cut off date to compare against the DOB. This was given in Jill's answer above, but another variation that might be clearer is:

WHERE DOB < todate((tochar(current_date,'YYYY')-12)||'0101','yyyymmdd')

Also, the usefulness  of an index will depend on the ratio of under 13 to over 13 records. If the vast majority are to be included, then use of an index may slow access down as the system flips back and forth over the main global, whereas a straight run without an index could be quicker (hopefully the compiler would work this out for you).