Bastett
asked on
Grouping aggregate data but showing counts of 0 where no records match the criteria
I'm using Microsoft SQL Server 2000 and Crystal Reports built into Visual Studio 2005.
I need to prepare a report with statistics for certain categories (eg: months and time classification).
Detailed Example:
I need to show the number of database entries for each month. There is a datetime field in the table. If I
SELECT COUNT (Key)
FROM Table
GROUP BY DATEPART(MONTH, DateField)
then I get this information, except if there are no entries for a month. In this case nothing shows up, whereas I need to see a 0 against this month.
As a short term solution I created a Months table, containing nothing but the numbers 1-12 and used an outer join to create the information. But now I require a similar solution for time classifications (basically a classification of the time between two different datetime fields. eg if the difference between the two fields is less than 2 hours this is classified as "Excellent", if it's between 2 and 4 hours this is "Good" and later than 4 hours is "Slow").
I really don't want to be creating temporary tables left, right and center just to be able to join to create the data sets I need.
My knowledge of Crystal Reports is quite limited (basically what I've learnt in the last few days).
Is it possible with either a T-SQL query or with a Crystal Report formula field (or other crystal report stuff) to group the aggregate data like this but showing 0 values where no data exists for the group?
Report view Example:
Crystal Report Cross-Tab Style showing counts of records matching the criteria
Month Excellent Good Slow
1 5 0 0
2 6 3 3
3 0 9 3
...
12 5 5 0
Tablular
Month Classification Count
1 "Excellent" 5
1 "Good" 0
1 "Slow" 0
2 "Excellent" 6
2 "Good" 3
2 "Slow" 3
3 "Excellent" 0
3 "Good" 9
3 "Slow" 3
...
12 "Excellent" 5
12 "Good" 5
12 "Slow" 0
Thanks
I need to prepare a report with statistics for certain categories (eg: months and time classification).
Detailed Example:
I need to show the number of database entries for each month. There is a datetime field in the table. If I
SELECT COUNT (Key)
FROM Table
GROUP BY DATEPART(MONTH, DateField)
then I get this information, except if there are no entries for a month. In this case nothing shows up, whereas I need to see a 0 against this month.
As a short term solution I created a Months table, containing nothing but the numbers 1-12 and used an outer join to create the information. But now I require a similar solution for time classifications (basically a classification of the time between two different datetime fields. eg if the difference between the two fields is less than 2 hours this is classified as "Excellent", if it's between 2 and 4 hours this is "Good" and later than 4 hours is "Slow").
I really don't want to be creating temporary tables left, right and center just to be able to join to create the data sets I need.
My knowledge of Crystal Reports is quite limited (basically what I've learnt in the last few days).
Is it possible with either a T-SQL query or with a Crystal Report formula field (or other crystal report stuff) to group the aggregate data like this but showing 0 values where no data exists for the group?
Report view Example:
Crystal Report Cross-Tab Style showing counts of records matching the criteria
Month Excellent Good Slow
1 5 0 0
2 6 3 3
3 0 9 3
...
12 5 5 0
Tablular
Month Classification Count
1 "Excellent" 5
1 "Good" 0
1 "Slow" 0
2 "Excellent" 6
2 "Good" 3
2 "Slow" 3
3 "Excellent" 0
3 "Good" 9
3 "Slow" 3
...
12 "Excellent" 5
12 "Good" 5
12 "Slow" 0
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My next trick will be using a CASE statement with a huge subquery and non-scalar comparisons.
I don't want to use CASE WHEN (subquery expression) THEN <answer> WHEN (subquery expression again) THEN <other answer>... END
because I really don't want to execute the subquery that many times. But I guess I'll open another expers-exchange issue for that one :D
Thanks for your help