I need help in adding a CASE statement that will take the count for a particular month and then either add or subtract from the total for that month, then help with aggregating the date back to a year level. Query is below:
SELECT DATEPART(MONTH, sts.[Status Change Date]) acct_month,
COUNT(DISTINCT a.id) asset_count
FROM API_CLBRA_JSON.assets a
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 DATEPART(year, sts.[Status Change Date]) = Year(getdate())
group by DATEPART(MONTH, sts.[Status Change Date])
I don't fully follow what you're asking. Some sample data, just a few rows, would be very helpful, with expected results.
Btw, your WHERE clause for year is very inefficient; it should instead be like this:
WHERE sts.[Status Change Date] >= DATEADD(YEAR, DATEDIFF(YEAR, 0, CAST(GETDATE() AS date)), 0) AND sts.[Status Change Date] < DATEADD(YEAR, DATEDIFF(YEAR, 0, CAST(GETDATE() AS date)) + 1, 0)
1 Like
Thanks for the tip. In a nutshell I have a query that's counting ID's that have been "accepted" in any given month for this year. The issue is that due to a break in the system some of the numbers were inflated for certain months and now I have to either add or subtract from the total from that month but I am unsure how to express that in my query. I'm sure there's some hardcoding that needs to happen but not sure here to go with it. For example in the query below the total asset count is 770, but I need to back out 335 from the month of July in which 415 were accepted but is inaccurate.