Link to home
Start Free TrialLog in
Avatar of Bastett
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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bastett
Bastett

ASKER

Great thanks for that!

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