Skip to content
Advertisement

Set based query to replace loop to populate all month end dates from given date for all records

I have a table that stores patient lab test results. There can be results from multiple tests like Albumin, Potassium, Phosphorus etc. First reading for each patient from each of these categories is stored in a table called #MetricFirstGroupReading.

CREATE TABLE #MetricFirstGroupReading (Patient_Key INT, Metric_Group VARCHAR(100), 
                                       Observation_Date DATE)
ALTER TABLE #MetricFirstGroupReading 
ADD CONSTRAINT UQ_MetricFirst UNIQUE (Patient_Key, Metric_Group);

INSERT INTO #MetricFirstGroupReading
SELECT 1, 'Albumin', '2018-11-15' UNION
SELECT 1, 'Potassium', '2018-12-10' UNION
SELECT 2, 'Albumin', '2018-10-20' UNION
SELECT 2, 'Potassium', '2018-11-25'

Now, I need to populate all month end dates upto current month into a new table, for each record from the #MetricFirstGroupReading table. Following is the expected result when the query run on December 2018.

enter image description here

I know how to do it using WHILE loops. How to do this without loops, using set based SQL queries, in SQL Server 2016?

Advertisement

Answer

Following worked. This is an expansion of the idea present in tsql: How to retrieve the last date of each month between given date range

Query

CREATE TABLE #AllMonthEnds (MonthEndDate DATE)
DECLARE @Start datetime
DECLARE @End datetime

SELECT @Start = '2000-01-01'
SELECT @End = DATEADD(MONTH,1,GETDATE())
;With CTE as
(
    SELECT @Start  as Date,Case When DatePart(mm,@Start)<>DatePart(mm,@Start+1) then 1 else 0 end as [Last]
    UNION ALL
    SELECT Date+1,Case When DatePart(mm,Date+1)<>DatePart(mm,Date+2) then 1 else 0 end from CTE
    WHERE Date<@End
)

INSERT INTO #AllMonthEnds
SELECT [Date]
FROM CTE
WHERE [Last]=1   
OPTION ( MAXRECURSION 0 )



SELECT  T.Patient_Key, T.Metric_Group, T.Observation_Date AS First_Observation_Date,
        DATEDIFF(MONTh,Observation_Date, MonthEndDate) AS MonthDiff, 
         A.MonthEndDate AS IterationDate
FROM #AllMonthEnds A
INNER JOIN
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Patient_Key, Metric_Group ORDER BY Observation_Date) AS RowVal
    FROM #MetricFirstGroupReading M
)T
    ON A.MonthEndDate >= T.Observation_Date
WHERE RowVal = 1
ORDER BY Patient_Key, Metric_Group, T.Observation_Date, A.MonthEndDate
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement