I am totally going around in circles with this and wanted to ask for a fresh pair of eyes on this.
This query works fine in that it identifies where the week beginning date in the Main table matches the Date Received in the Sub table. However, this is not completely correct because I am looking for a 'snapshot' as at 01/04/2024, 08/04/2024 etc so I also need the Dates Received before the week beginning, not just on that exact date, so I can show where an application was ongoing as at the week beginning. I know the issue is with the table join at the very bottom but I just cant figure it out so any advice would be hugely appreciated
SELECT DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, dt)) % 7, CONVERT(date, dt, 103)) AS Week
INTO [#Main]
FROM RQIA_Custom_Calendar_View
WHERE (CONVERT(date, dt, 103) >= DATEADD(year, DATEDIFF(month, 90, CURRENT_TIMESTAMP) / 12, 90)) OPTION (maxrecursion 0);
SELECT FilteredAccount.rqia_servicetypeidname AS ServiceType, FilteredAccount.rqia_subtypeidname AS ServiceSubtype, FilteredAccount.name AS RQIAService, 'Variation Application' AS ApplicationType,
Filteredrqia_variation.rqia_name AS ApplicationID, DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, Filteredrqia_variation.rqia_applicationreceiveddate)) % 7, CONVERT(date,
Filteredrqia_variation.rqia_applicationreceiveddate, 103)) AS WB, CONVERT(date, Filteredrqia_variation.rqia_applicationreceiveddate, 103) AS DateReceived, CONVERT(date,
Filteredrqia_variation.rqia_dateofoutcome, 103) AS OutcomeDate
INTO #Sub
FROM FilteredAccount AS FilteredAccount INNER JOIN
Filteredrqia_variation ON FilteredAccount.name = Filteredrqia_variation.rqia_serviceidname
WHERE (Filteredrqia_variation.statuscodename NOT LIKE '%Withdrawn') AND (Filteredrqia_variation.statuscodename NOT LIKE '%Refused') AND (Filteredrqia_variation.statuscodename NOT LIKE '%Returned')
GROUP BY FilteredAccount.rqia_directoratename, FilteredAccount.rqia_servicetypeidname, FilteredAccount.rqia_subtypeidname, FilteredAccount.name, Filteredrqia_variation.rqia_name, DATEADD(dd,
0 - (@@DATEFIRST + 5 + DATEPART(dw, Filteredrqia_variation.rqia_applicationreceiveddate)) % 7, CONVERT(date, Filteredrqia_variation.rqia_applicationreceiveddate, 103)), CONVERT(date,
Filteredrqia_variation.rqia_applicationreceiveddate, 103), CONVERT(date, Filteredrqia_variation.rqia_dateofoutcome, 103)
/* Final Query combining all temp tables*/ SELECT Main.Week, Sub.ApplicationID, Sub.DateReceived, Sub.OutcomeDate, CASE WHEN Sub.DateReceived <= Main.Week AND (Sub.OutcomeDate IS NULL OR
Sub.OutcomeDate > Main.Week) THEN 'Yes' ELSE 'No' END AS Ongoing
FROM #Main AS Main LEFT OUTER JOIN
#Sub AS Sub ON Sub.WB = Main.Week
GROUP BY Main.Week, Sub.ApplicationType, Sub.ApplicationID, Sub.DateReceived, Sub.OutcomeDate
ORDER BY Ongoing DESC, Main.Week /* Clean up temporary tables*/ DROP TABLE #Main; DROP TABLE #Sub
was so easy - should have known - just a change in the table join!
ON Sub.DateReceived <= Main.Week AND (Sub.OutcomeDate IS NULL OR
Sub.OutcomeDate > Main.Week)