SQL SUM ROWS and COLUMNS in CROSSTAB?

[Rock6431]

I am new to this crosstab stuff, but am slowly managing. I have a dynamic stored proceedure which creates the following query. I am wanting to sum the rows and columns of my ouput. Where would I add the sum code to the query below, and what would it look like. I will convert it in my dynamic SP but I don't know what it would look like in query form. I guess I work better at this in looking at it in reverse.

Thanks
Rock6431


Sample Output:

STATES PastDue Due Due1 Due2 Due3 Due4 Due5 Due6 Due7 TOTAL
CA 3 22 19 0 0 0 0 0 1 ?
KS 0 37 0 0 1 5 6 8 0 ?
------------------------------------------------------------
? ?? ?? ? ? ? ? ? ? ?




SELECT
state AS States,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) < 0 THEN 1
ELSE 0
END) AS PASTDUE,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) = 0 THEN 1
ELSE 0
END) AS DUE,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) =1 THEN 1
ELSE 0
END) AS DUE1,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) =2 THEN 1
ELSE 0
END) AS DUE2,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) =3 THEN 1
ELSE 0
END) AS DUE3,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) =4 THEN 1
ELSE 0
END) AS DUE4,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) =5 THEN 1
ELSE 0
END) AS DUE5,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) =6 THEN 1
ELSE 0
END) AS DUE6,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) >=7 THEN 1
ELSE 0
END) AS DUE7,

FROM IWworklist
WHERE (dbo.DATEPART(Tdate)) = &quot;01/09/2003&quot;
GROUP BY state
ORDER BY state

------------------------------------------------------------
SUM(????????????) AS Total_States -- ROWS??
SUM(????????????) AS DUE_TOTALS -- COLUMNS??

 

[sunila7]

hi,
try this

SELECT
state AS States,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) < 0 THEN 1
ELSE 0
END) AS PASTDUE,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) = 0 THEN 1
ELSE 0
END) AS DUE,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) =1 THEN 1
ELSE 0
END) AS DUE1,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) =2 THEN 1
ELSE 0
END) AS DUE2,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) =3 THEN 1
ELSE 0
END) AS DUE3,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) =4 THEN 1
ELSE 0
END) AS DUE4,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) =5 THEN 1
ELSE 0
END) AS DUE5,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) =6 THEN 1
ELSE 0
END) AS DUE6,
SUM(CASE
WHEN DateDiff(dd, Tdate, DueDate) >=7 THEN 1
ELSE 0
END) AS DUE7

FROM IWworklist
WHERE Tdate = &quot;01/09/2003&quot;
GROUP BY state
With Rollup

Sunil