SQL or Cache Function?

Caché, SQL

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?

 

Thanks,

Blake

  • 0
  • 0
  • 839
  • 7
  • 5

Answers

select DOB from sample.person -- or HSAA.Patient
where
(tochar(current_date,'YYYYMMDD')-tochar(DOB,'YYYYMMDD'))\10000>=13
or ((year(current_date)||'1231')-tochar(DOB,'YYYYMMDD'))\10000=13

The answer to this post has been figured out. Below is a query to get the 12 year olds who will turn 13 by the end of the current year (12/31).

 

select DATEDIFF('YY',birthdate,current_date) AS CalculatedAge, Age
from hsaa.patient
where DATEDIFF('YY',birthdate,current_date) = 13

Never use DATEDIFF for accurate calculation of the age since days and months here are not taken into account.

See example

Hi Vitaliy -

Thank you very much for pointing this out as I didn't think about that!

Blake

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

SELECT COUNT(*) FROM HSAA.Patient WHERE (DATEPART('yy',CURRENT_DATE) - DATEPART('yy',BirthDate)) >= 13

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 Jill -

I plan on trying all the answers out today. Performance will be important for this report/extract so I will be sure to update the post on which approach I take. Thank you for your answer!

Blake

Thanks Blake!  Please report your results to this thread - I think this information will be useful to lots of people.

--Jill

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
    where
    dob<=dateadd('yy',-13,current_date)
    or dob between
          todate((year(current_date)-13)||'0101','yyyymmdd')
      and todate((year(current_date)-13)||'1231','yyyymmdd')

The result will pleasantly surprise you.

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).

Regards,

Hi Everyone -

Apologies for the late response on this but for our needs we have decided to go with Jill's recommendation (see below). It is the most efficient and logical option for us with how my entire 50 line query is structured.

 

AND (DATEPART('YY',CURRENT_DATE) - DATEPART('YY',Patient.BirthDate)) >= 13

 

Thanks,

Blake

Comments

And my 2 cents ..........

 

WHERE 
datediff(year,
todate(to_char(dgi.Birthdate,'YYYY')||':1','YYYY:MM'),
todate(to_char(current_date,'YYYY')||':1','YYYY:MM')
) >= 13

Why so difficult?
This similarly following condition:

WHERE 
(
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.