May 22, 2008 at 6:53 pm
I have a status column in a table with about 20 different status.
This table also has a date column.
I want to get the totals for each status and date as separate fields.
The only way I know to do this is creating temporary tables for each status (containing the date, status, and count) and relating them all by the date in a final query and grouping by the date and status.
I'm looking for help here on how to do this in a more effective way, because all these temporary tables are slowing down my query alot.
Thanks
May 22, 2008 at 8:35 pm
It sounds like you want a crosstab - if so, lookup PIVOT in BOL. Generally though, this is much easier to do in whatever reporting tool you are using (e.g. SSRS, Crystal, etc...).
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 22, 2008 at 9:17 pm
I'm using SSRS, and I'm putting this in a matrix. I can do this too:
SELECT
DATE,
STATUS,
COUNT(1) [TOTAL]
FROM
TABLE
GROUP BY
DATE,STATUS
But then I don't get the control of each of the status total (to put them in different data fields in the matrix)
Thanks
May 23, 2008 at 11:12 am
Okay, so lookup PIVOT and see if that will give you what you need.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 23, 2008 at 3:24 pm
giontech (5/22/2008)
I'm using SSRS, and I'm putting this in a matrix.
I'm confused. A SSRS matrix can automatically generate subtotals for each row/column group. Why won't those subtotals meet your needs?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply