Meditech DR (Data Repository)

We are now using the Data Repository so I thought this would be a good time to start back to posting here.


This is an interesting query to find all patients whose birthday is today.


The WITH TempTable is necessary to only get one record for each patient (UnitNumber).

WITH TempTable AS
(
 SELECT 
  *,
  ROW_NUMBER() OVER(PARTITION BY UnitNumber ORDER BY UnitNumber) AS RowNumber
 FROM 
  AdmVisits
)
 
SELECT
 UnitNumber,
 Age,
 FLOOR(DATEDIFF(day, BirthDateTime, GETDATE()) / 365.25) as MyAge,
 BirthDateTime,
 Name,
 Sex
 
FROM 
 TempTable
 
WHERE 
 RowNumber = 1
 AND
 DAY(BirthDateTime) = DATEPART(DAY,GETDATE())
 AND 
 MONTH(BirthDateTime) = DATEPART(MONTH,GETDATE())

ORDER BY
 MyAge

One of the interesting things about it is that "Age" and "MyAge" are not always the same. It looks to me like "Age" stores the patient's age at the time of the visit.