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

Question

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.

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

Actually, while a Calendar Table should be a part of every database, that may be overkill here. You can use `EOMONTH()` -- `UPDATE myTable SET MonthEndVal = EOMONTH(First_Observation_Date,MonthDiff)`. — Shawn, Dec 24 '18 at 17:39
Wait, do you want `MonthEndVal` to always be updated to the current month? You'd have to use a computed column. — Shawn, Dec 24 '18 at 17:42
Thinking about this a little more, your data will become out-of-date when the next month rolls over unless you set up a SQL Job to add the next month's records to the table. But then your table has the potential to grow _massively_. If the `First_Observation_Date` records will be stored one time and then fill in the intervening dates when queried, then you can do this with a Tally Table and a couple of Computed Columns on your original table. — Shawn, Dec 24 '18 at 19:54

score 0 · Accepted Answer · answered Dec 24 '18 at 18:01

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

Technically a recursive CTE is still a loop and not set-based. I'm assuming this initial batch is an initial population and future inserts will need to be handled, correct? Does your workflow include inserting into the temp table and then moving the data to your permanent table, or can you insert directly into your permanent table? And will the temp table be dropped/cleaned up when the insert is done? — Shawn, Dec 24 '18 at 19:17

Gene Stempel · Answer 2 · 2018-12-25T02:56:39.437

How about:

    select MetricFirstGroupReading.*, datediff(month, MetricFirstGroupReading.Observation_Date, months.monthendval) monthdiff, months.* 
    into allmonths
    from
    (
    SELECT 1 patientid, 'Albumin' test, '2018-11-15' Observation_Date UNION
    SELECT 1 patientid, 'Potassium' test, '2018-12-10' Observation_Date UNION
    SELECT 2 patientid, 'Albumin' test, '2018-10-20' Observation_Date UNION
    SELECT 2 patientid, 'Potassium' test, '2018-11-25' Observation_Date) MetricFirstGroupReading 
join 
    (
    select '2018-10-31' monthendval union
    select '2018-11-30' monthendval union
    select '2018-12-31' monthendval 
    ) months on MetricFirstGroupReading.Observation_Date< months.monthendval

Replace the first select union with your table, and add or remove month ends from the second inner select.

score 0 · Answer 3 · answered Dec 25 '18 at 04:04

Consider building a temp table of all 12 month end dates, then join to main table by date range. Use DateDiff for month difference:

CREATE TABLE #MonthEndDates (Month_End_Value DATE)

INSERT INTO #MonthEndDates
VALUES ('2018-01-31'),
       ('2018-02-28'),
       ('2018-03-31'),
       ('2018-04-30'),
       ('2018-05-31'),
       ('2018-04-30'),
       ('2018-06-30'),
       ('2018-07-31'),
       ('2018-08-31'),
       ('2018-09-30'),
       ('2018-10-31'),
       ('2018-11-30'),
       ('2018-12-31')

SELECT m.Patient_Key, m.Metric_Group, m.Observation_Date, 
       DateDiff(month, m.Observation_Date, d.Month_End_Value) AS Month_Diff, 
       d.Month_End_Value

FROM #MetricFirstGroupReading m
INNER JOIN #MonthEndDates d
  ON m.Observation_Date < d.Month_End_Value

GO

Rextester Demo

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

3 Answers3