• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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
0
Bastett
Asked:
Bastett
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
something like this:

SELECT ml.m, coalesce(d.c,0) as counter
FROM (
SELECT 1 as m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
) as ml
LEFT JOIN (
SELECT DATEPART(MONTH, DateField) m, COUNT (Key) c
FROM Table
GROUP BY DATEPART(MONTH, DateField)
) as d
on ml.m = d.m
0
 
BastettAuthor Commented:
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
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now