July 5, 2012 at 8:57 am
Hi,
I am having a problem with rollup. First, this is mssql...
the problem is I try to display a total at the end using rollup, but I get the last statusname as the label, this case being the word 'received', I'd like to display 'Total' or blank..
Thanks In Advance
Joe
SELECT
MAX(STATUSNAME ) 'Status',
COUNT(wo.WORKORDERID) 'Total Count',
COUNT(CASE WHEN DATEDIFF(day, DATEADD(s, wo.CREATEDTIME/1000, '01-01-1970 00:00:00'), getDate()) <= 30 THEN 1 ELSE NULL END) '0 - 30 Days',
COUNT(CASE when DATEDIFF(day, DATEADD(s, wo.CREATEDTIME/1000, '01-01-1970 00:00:00'), getDate()) <= 60 and DATEDIFF(day, DATEADD(s, wo.CREATEDTIME/1000, '01-01-1970 00:00:00'), getDate()) > 30 THEN 1 ELSE NULL END) '31 - 60 Days',
COUNT(CASE when DATEDIFF(day, DATEADD(s, wo.CREATEDTIME/1000, '01-01-1970 00:00:00'), getDate()) <= 90 and DATEDIFF(day, DATEADD(s, wo.CREATEDTIME/1000, '01-01-1970 00:00:00'), getDate()) > 60 THEN 1 ELSE NULL END) '61 - 90 Days',
COUNT(CASE when DATEDIFF(day, DATEADD(s, wo.CREATEDTIME/1000, '01-01-1970 00:00:00'), getDate()) > 90 THEN 1 ELSE NULL END) 'More than 90 Days' FROM WORKORDER wo
INNER JOIN WORKORDERSTATES on WORKORDERSTATES.WORKORDERID = wo.WORKORDERID
LEFT JOIN STATUSDEFINITION on STATUSDEFINITION.STATUSID = WORKORDERSTATES.STATUSID
LEFT JOIN AAAUSER ON AAAUser.USER_ID= WORKORDERSTATES.OWNERID
WHERE WORKORDERSTATES.STATUSID IN ( Select STATUSID from StatusDefinition where ISPENDING=1)
and FIRST_NAME in('joe','jane')
GROUP BY STATUSNAME
with rollup
July 5, 2012 at 10:44 am
It would help us to help you if you could post some DDL and sample data in the form of INSERT scripts. Then post an expected result and a copy of the result you're really getting.
July 5, 2012 at 10:55 am
Hope this helps with my ??
Sorry and thanks
Output looks like this
StatusTotal Count0 - 30 Days31 - 60 Days61 - 90 DaysMore than 90 Days
Onhold46 4 8 925
Open270 135 39 2175
Ordered62 30 12 614
Received4 0 1 21
Received382 169 60 38115
I dont want the status to be received on the total line I want Received to be 'Total' or blank
July 5, 2012 at 11:39 am
Please post CREATE TABLE statements for your tables in the query. Please post INSERT statements with multiple rows of sample data for us to run the query against.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply