How to move column value into newly created column?

April 5, 2023

I have the following query:

SELECT QueryRun, Count(*) as TotalN, Avg(MDays) as AvgMDays
FROM table_name 
WHERE YN = 1 AND HF = 0
GROUP BY QueryRun
ORDER BY QueryRun DESC;

That returns the following output:

QueryRun      TotalN        AvgMDays
4/4/23        100           10
3/30/23       115           7
3/23/23       90            8
3/16/23       85            6
3/9/23        105           11
3/2/23        95            12

I want to edit my query above so it returns the following:

TotalN       Trailing4WkTotalN        AvgMDays         Trailing4WkAvgMDays
100          105                      10               11

I want to return one record: the TotalN value for the current date, TotalN value from 4 weeks ago (in this case 3/9/23), AvgMDays for the current date, and AvgMDays from 4 weeks ago. I’ve tried using LEAD but it didn’t work.

How can I get this output?

>Solution :

My first instinct is to use an APPLY lateral join. It should work, though I doubt this is the most efficient way:

WITH grouped AS (
   SELECT QueryRun, Count(*) as TotalN, Avg(MDays) as AvgMDays
   FROM table_name 
   WHERE YN = 1 AND HF = 0
   GROUP BY QueryRun
)
SELECT TOP 1 g.QueryRun, g.TotalN, t.TotalN AS Trailing4WkTotalN, g.AvgMDays, t.AvgMDays As Trailing4WkAvgMDays
FROM grouped g
CROSS APPLY (
    SELECT TOP 1 TotalN, AvgMDays
    FROM grouped g0 
    WHERE g0.QueryRun <= DATEADD(week, -4, t.QueryRun)
    ORDER BY g0.QueryRun DESC
) t -- t is for "trailing"
ORDER BY g.QueryRun DESC

I do expect you can get more efficient if you can KNOW, with absolute certainty, there is always a record for the day that is EXACTLY 4 weeks in the past:

WITH cur AS (
   SELECT TOP 1 QueryRun FROM table_Name ORDER BY QueryRun DESC
)
SELECT t.QueryRun, COUNT(*) AS TotalN, MAX(t0.TotalN) As Trailing4WkTotalN
    Avg(MDays) As AvgMDays, MAX(t0.AvgMDays) As Trailing4WkAvgMDays
FROM table_name t
INNER JOIN cur ON cur.QueryRun = t.QueryRun
CROSS APPLY(
    SELECT COUNT(*) As TotalN, Avg(MDays) As AvgMDays
    FROM table_name t0
    WHERE t0.QueryRun = DATEADD(week, -4, t.QueryRun)
) t0
GROUP BY t.QueryRun

Just keep in mind: any possibility of the dates not being exactly 4 weeks apart breaks this.