SQL or Cache Function?

Question

Question

Roger Beeman · Dec 13, 2017

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

Discussion (12)1

Log in or sign up to continue

Vitaliy Serdtsev · Dec 14, 2017

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

0 0

score 0 · Answer 1 · 2017-12-13T12:58:15-05:00

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

score 0 · Answer 2 · 2017-12-14T01:39:22-05:00

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

See example

score 0 · Answer 3 · 2017-12-14T08:47:30-05:00

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

score 0 · Answer 4 · 2017-12-28T01:36:13-05:00

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({d '1990-12-31'},'YYYY')||':1','YYYY:MM'), -- birthday
todate(to_char({d '2003-01-01'},'YYYY')||':1','YYYY:MM') -- report date
)

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

score 0 · Answer 5 · 2017-12-14T08:44:39-05:00

Roger Beeman · Dec 14, 2017

Hi Vitaliy -

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

Blake

0 0

score 0 · Answer 6 · 2017-12-15T10:01:14-05:00

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

--Jill

score 0 · Answer 7 · 2017-12-16T10:46:36-05:00

For better performance you should make a few changes:

add an index on DOB field
rebuild the index and run TuneTable

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.

score 0 · Answer 8 · 2017-12-13T20:27:49-05:00

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

score 0 · Answer 9 · 2017-12-20T08:59:38-05:00

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,

score 0 · Answer 10 · 2017-12-27T18:36:50-05:00

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

score 0 · Answer 11 · 2018-01-16T08:43:55-05:00

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