SQLTeam.com | Weblogs | Forums

Add or Subtract from Count for any given month range

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 acceptedbut is inaccurate. The query is below.

SELECT DATEPART(YEAR, sts.[Status Change Date]) acct_year,

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(YEAR, sts.[Status Change Date])