CASE statement inside COUNT

1

Getting Invalid column name 'acct_month' error in the following query. What I'm trying to do is subtract 351 from the count for the month of July but I'm getting stuck. Really need some help.

SELECT DATEPART(MONTH, sts.[Status Change Date]) acct_month,
COUNT(CASE
WHEN sts.acct_month = 7 then count(DISTINCT a.id) - 351
ELSE 0
END) 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])

2 
SELECT
    DATEPART(MONTH, sts.[Status Change Date]) acct_month,
    COUNT(   CASE
                 WHEN DATEPART(MONTH, sts.[Status Change Date]) = 7 THEN
                     COUNT(DISTINCT a.id) - 351
                 ELSE
                     0
             END
         ) asset_count
......

You cannot use an expression you define in the select statement in another part of the same select statement. Think of it as, all the expressions in the select statement being processed in parallel.

1 Like
3

Thanks @JamesK for the input!! I now have another question, how can I aggregate this to give me a yearly total for 2022. Right now it's broken out by month but I'd definitely like to display it as a total for this year.

SELECT DATEPART(MONTH, sts.[Status Change Date]) acct_month,
CASE
WHEN DATEPART(MONTH, sts.[Status Change Date]) = 7
THEN COUNT(DISTINCT a.id) - 351
ELSE
COUNT(distinct a.id) END AS 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])

4

You can use grouping sets as shown below:

.......
WHERE
    a.[status_name]                              = 'Accepted'
    AND DATEPART(YEAR, sts.[Status Change Date]) = YEAR(GETDATE())

GROUP BY
    GROUPING SETS ((YEAR(sts.[Status Change Date]),MONTH(sts.[Status Change Date])));

Be sure to enclose the set in parenthesis as shown above, or the grouping would not be what you are looking for. Also, add YEAR(sts.[Status Change Date]) to the select list, so you know what year you are looking at.

1 Like
6

I tried using that grouping set but I'm still getting the results as a monthly total instead of one total for the current year (see below)
acct_month | asset_count
1 | 2
2 | 40
3 | 28

The way I'd like to have it is like below:
Acct_year | asset_count
2022 | 70

7

hi

change 
     DATEPART(Month 
to 
     DATEPART(Year
1 Like