Query Help

[V00D00]

Any help with this will be greatly appreciated. The table I have has a field called 'TYPE' and 'DOL' (DOL = Date of Load).

I need to produce a query that will count the number of 'TYPE' and group them by 'DOL' for a given week.

For example:

TYPE     1 WEEK OLD   2 WEEKS OLD   3 WEEKS OLD

00           53            42           12
01           43            23           12
02           12            41           43
03           0             0            1

Two problems I have run in to so far are:

1. I need to be returned a 0 count for TYPES if applicable.

2. I need the dataset to be returned in the exactly the same order each time.

If I can come up with a query that will satisfy these two problems, then I can use PERL to grab the data and produce the report in the desired output.

 

[gmmastros]

You show sample output data. Can you also show some sample input data? It will help us to help you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

SELECT Type,
       SUM(CASE WHEN DateDiff(dd, DOL, GetDate()) BETWEEN 1  AND 7 THEN 1 ELSE 0 END) AS 1_WEEK_OLD,
       SUM(CASE WHEN DateDiff(dd, DOL, GetDate()) BETWEEN 8  AND 14 THEN 1 ELSE 0 END) AS 2_WEEKS_OLD,
       SUM(CASE WHEN DateDiff(dd, DOL, GetDate()) BETWEEN 9 AND 21 THEN 1 ELSE 0 END) AS 3_WEEK_OLD
FROM MyTable
GROUP BY Type

not tested



Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[V00D00]

Sample Input Data.

TABLE NAME: LEADS

TYPE     DOL
01       09/01/2006
03       09/04/2006
04       09/14/2006
05       09/08/2006
02       09/11/2006
03       09/01/2006
05       09/04/2006
01       09/14/2006
00       09/08/2006
02       09/11/2006
04       08/25/2006
05       09/03/2006

 

[V00D00]

I should add this will be a report run each sunday night and the "WEEKS OLD" will reflect how old from the day the report is run.

 

[bborissov]

Chanemy first statment to be:

SUM(CASE WHEN DateDiff(dd, DOL, GetDate()) BETWEEN 0  AND 7 THEN 1 ELSE 0 END) AS 1_WEEK_OLD,

If you want to include current day in that report.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP