SELECT
CASE
WHEN (GROUPING(PLD_HRP_ID) = 1)
THEN 'END'
ELSE ISNULL(PLD_HRP_ID, 'UNKNOWN')
END AS PLD_HRP_ID,
CASE
WHEN (GROUPING(PLD_LPN_ID) = 1)
THEN 'END'
ELSE SUM(PLD_HOURS) -- if you use this it gives you an error... Error converting data type varchar to numeric.
--ELSE PLD_HOURS -- if you use this you have to put the PLD_HOURS in the GROUP BY
-- ELSE PLD_LPN_ID -- if you use this output will be id # and just the PLD_LPN_ID
-- ELSE PLD_LPN_ID
-- ELSE (SELECT SUM(CAST(PLD_HOURS AS DECIMAL(10,2))))
-- ELSE ISNULL(PLD_LPN_ID, 'UNKNOWN')
END --AS PLD_LPN_ID
FROM S59_PERLVDTL
WHERE PLD_DATE BETWEEN '07-01-2014' AND '06-30-2015'
GROUP BY PLD_HRP_ID, PLD_LPN_ID WITH ROLLUP
I don't see a question, sample data or expected result set. Not sure what you want help with given just a query.
Tara, sorry. I wasn't sure if I could get into this forum. I have tried others without any success.
I am trying to get the total hours per leave type per employee for a specified date range.
I have 'stabbed' at this in various ways. This code seems to be the closest. If you read the comments you will see the error I am getting. Error converting data type varchar to numeric.
Am I going about this the right way ?
I was thinking of doing a CTE for the employees and their leave types then joining that to another CTE where I will SUM the PLD_HOURS or hours per leave type.
Sigh.... I have worked on this longer than anything else.
Just read about the 'grouping' and 'grouping with group sets' or something like that.
thought I would try it.
I would appreciate any help I can get. I am self taught..... just so you know.
Based on what you are saying in the quoted text, your query should be something like this:
SELECT
EMPLOYEE_ID,
LEAVE_TYPE,
SUM(PLD_HOURS) -- assuming PLD_HOURS indicates the leave hours
FROM
YourTable
WHERE
PLD_DATE >= '07-01-2014' AND PLD_DATE < '07-01-2015'
GROUP BY
EMPLOYEE_ID,
LEAVE_TYPE;
The error message you are seeing is because one section of a case expression is returning something that is an INT (SUM(PLD_HOURS)) and another section is returning a string (THEN 'END'). All outputs of the CASE expression should be of the same data type, or should be something that could be converted to the same data type. In this case, SQL Server is trying to convert the string 'END' to an integer. The error message says it is unable to do that conversion.
Tara, wondering if you got my response.
here is the output I get when I do not have the SUM
0000007 DIVR
0000007 NLWP
0000007 NPER
0000007 NSIC
0000007 NVAC
0000007 OTHR
0000007 PRDV
0000007 SAFE
0000007 END
0000011 DIVR
0000011 FLWP
0000011 FPER
0000011 FSIC
0000011 OTHR
0000011 PRDV
0000011 SAFE
0000011 END
you noticed that I get the END like I want at the break of each ID# at the final I do get END END for both the ID# and Leave Type.
Why can't I do an aggregate within a case statement ? is it because of the 'grouping' ?
Please see this article on how to post sample data/code/question to get a fast and good answer: http://www.sqlservercentral.com/articles/Best+Practices/61537/