I need help in putting the query below into a subquery, then adding the next few lines of code separately but I am unsure of how I should do it. Everything I've tried so far is giving me an error.
Main Query
DROP TABLE IF EXISTS #temp_assets;
SELECT [Asset Id],
[Percent Playbook Complete]
INTO #temp_assets
FROM reporting.vw_collibra_assets;
CREATE NONCLUSTERED INDEX idx_temp ON #temp_assets ([Asset Id]);
SELECT DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1)) sc_date,
CASE
WHEN DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1)) = '2022-05-01'
THEN count(DISTINCT a.id) + 73
WHEN DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1)) = '2022-06-01'
THEN count(DISTINCT a.id) -9
WHEN DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1)) = '2022-07-01'
THEN count(DISTINCT a.id) - 351
ELSE count(DISTINCT a.id)
END AS accepted_ct,
sum(CASE
WHEN t.[Percent Playbook Complete] = 1
THEN 1.0
ELSE 0.0
END) adherence_ct,
sum(sum(CASE
WHEN t.[Percent Playbook Complete] = 1
THEN 1.0
ELSE 0.0
END)) OVER (
ORDER BY DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1))
) RunningCompliance,
CASE
WHEN sum(sum(CASE
WHEN t.[Percent Playbook Complete] = 1
THEN 1.0
ELSE 0.0
END)) OVER (
ORDER BY DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1))
) = 0
OR sum(count(DISTINCT a.id)) OVER (
ORDER BY DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1))
) = 0
THEN 0
ELSE sum(sum(CASE
WHEN t.[Percent Playbook Complete] = 1
THEN 1.0
ELSE 0.0
END)) OVER (
ORDER BY DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1))
) / sum(count(DISTINCT a.id)) OVER (
ORDER BY DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1))
) * 1.0
END RunningPercent
FROM API_CLBRA_JSON.assets a
LEFT JOIN #temp_assets t
ON a.id = t.[Asset Id]
LEFT JOIN (
SELECT [Asset Id] asset_id,
[New Name] [status],
max([Activity Date]) [Status Change Date]
FROM reporting.vw_collibra_activities
WHERE [Activity Type] = 'Update'
AND Field = 'Status'
AND [New Name] = 'Accepted'
GROUP BY [Asset Id],
[New Name]
) sts
ON a.[id] = sts.asset_id
WHERE a.[status_name] = 'Accepted'
AND sts.[Status Change Date] >= DATEADD(year, - 1, GETDATE())
GROUP BY DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1))
ORDER BY DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1))
Then After the subquery add this:
sum(count(DISTINCT a.id)) OVER (
ORDER BY DATEADD(DAY, 1, EOMONTH(sts.[Status Change Date], - 1)))
RunningAccepted,