sql query help

[flaimster]

I am writing a report for Influenza vaccines that will display patient, last vax date, and status of Due or not Due based on last vax date. Each year the flu shot season starts in september and goes thru march. How do i create a query that will basically say whether or not a person is due for a shot without always having to manually change the year of the date range I am searching. I want to know all the people who haven't had a shot during the current flu season so for this year it would be October 2016 until March 2017. I am using the query in a Crystal Report command object so it has to be able to change for the current flu shot year without me changing the year of the range. I hope this makes sense. Any help would be appreciated.

thanks

 

[Olaf Doschke]

Since the flu season spans accross two years it's a bit more complicated to decide which range to test, but it's not imnpossible to do that without any input. So the Crystal reports doesn't play a role, it doesn't need to provide that input. This part of the query is handled via the following ingredients:

1. The server knows "now" as GetDate(). The start date of the current season can be determined from that.
2. The server knows last vax date from patient data, say it's in a datetime column named LastVaccination

The thing to check is whether LastVaccination is before the start of the current season. So how do we find the start of the current season? As each year has the end of previous season until March and the start of this years season from October, the first dicision has to be made depending on GetDate() being before April or not:

CASE WHEN DATEPART(month, GetDate())<4 THEN "YEAR-1/YEAR season start" ELSE "YEAR/YEAR+1 season start" END

Now it gets complicated, as we have to use a little not so easy to understand but well known trickery about the reference day "0" and some DATEADD/DATEDIFF calls.

The previous year first of October results from:
[tt]DATEADD(month, -3, DATEADD(year, DATEDIFF(year, 0, GetDate()), 0))[/tt]

This years first of October results from:
[tt]DATEADD(month, 9, DATEADD(year, DATEDIFF(year, 0, GetDate()), 0))[/tt]

So finally this tests whether LastVaccination is before the start of the season:

 SELECT ...
(LastVaccination <
 CASE WHEN DATEPART(month, GetDate())<4 
      THEN DATEADD(month, -3, DATEADD(year, DATEDIFF(year, 0, GetDate()), 0))
      ELSE DATEADD(month, 9, DATEADD(year, DATEDIFF(year, 0, GetDate()), 0)) 
 END
) AS IsDue,
...
FROM yourpatientstable
WHERE...

IsDue then is a bit value being 1 for Due patients and 0 for already vaccinated patients.

This makes the jump of the season start date to 1st October of this year at 1st April of this year, right at the end of a season. So all patients vaccinated on 3/31 of a year are Due again right away on 4/1. But that due means for next season in October. You can move that switch by not checking for <4 but a later month. July is right in the middle, so perhaps check <7 and then all patients switch to Due at 1st July.

If you have SQL 2012 it gets much simpler, you then have the function DATEFROMPARTS(Y,M,D) and you have YEAR(GetDate()), so you can compute DATEFROMPARTS(YEAR(GetDate()),10,1).

Bye, Olaf.