SUM(CASE) with a nested CASE!
-
Can someone tell me if this is possible? With the query below I need to perform 1 case and compare it to another case but I get an eror of "Server: Msg 130, Level 15, State 1, Line 10
Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
SELECT UserID, SubjectDescript,SUM(CASE WHEN
DATEDIFF(DAY, ClosedDateTime, OpenedDateTime) <= (SELECT CASE WHEN DATEPART(dw, OpenedDateTime) >= 4 THEN 11 ELSE 9 END
FROM vwComplaintDetails
WHERE ClosedDateTime >= @StartDate AND ClosedDateTime <= @EndDate)
THEN 1
ELSE 0 END) AS ClosedSevenDaysTot
FROM vwComplaintDetails
WHERE ClosedDateTime >= @StartDate AND ClosedDateTime <= @EndDate)
GROUP BY UserID, SubjectDescript -
Does this provide the results you are looking for?
SELECT a.UserID, a.SubjectDescript,
ClosedSevenDaysTot = (SELECT COUNT(b.userID) FROM vwComplaintDetails b WHERE b.userID = a.UserID AND DATEDIFF(DAY, b.ClosedDateTime, b.OpenedDateTime) = 4 THEN 11 ELSE 9 END)
AND b.ClosedDateTime >= @StartDate AND b.ClosedDateTime = @StartDate AND a.ClosedDateTime <= @EndDate
GROUP BY a.UserID, a.SubjectDescript
-
The original post I have given is wrong. I apologise for that. But there must be some way of doing what I'm trying to do!
The query I'm trying to get to work is below and I'm trying to SUM 1 CASE with a DATEDIFF and the 2nd CASE with a DATEPART. But I keep getting an error of,
"Server: Msg 130, Level 15, State 1, Line 8
Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
SELECT UserID, ComplaintType, COUNT(CategoryDescript) AS ClosedTot,
SUM(CASE WHEN
DATEDIFF(DAY, ClosedDateTime, OpenedDateTime) = 4 THEN 11 ELSE 9 END)
THEN 1
ELSE 0 END) AS ClosedSevenDaysTot
WHERE ClosedDateTime >= @StartDate AND ClosedDateTime <= @EndDate
GROUP BY UserID, ComplaintType
-
create Table #table (UserID int, ComplaintType varchar(10), CategoryDescript varchar(10), ClosedDateTime datetime, OpenedDateTime datetime)
declare @StartDate datetime, @EndDate datetime
SELECT
UserID,
ComplaintType,
COUNT(CategoryDescript) AS ClosedTot,
SUM(ClosedSevenDaysTot) AS ClosedSevenDaysTot
FROM
(
SELECT
UserID,
ComplaintType,
CategoryDescript,
CASE
WHEN DATEDIFF(DAY, ClosedDateTime, OpenedDateTime)
<=
CASE
WHEN DATEPART(dw, OpenedDateTime) >= 4 THEN 11
ELSE 9
END
THEN 1
ELSE 0
END AS ClosedSevenDaysTot
FROM #table
WHERE ClosedDateTime >= @StartDate AND ClosedDateTime <= @EndDate
) tmp
GROUP BY UserID, ComplaintType
drop table #table
HTH,
Leon Bakkers
Regards,
Leon Bakkers -
Or simply drop the "SELECT" in<= (select CASE WHEN DATEPART
Change it to
<=(CASE WHEN DATEPART
Regards,
Leon Bakkers
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply