Help Creating a Query

[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.

 

[feherke]

Hi

[b]select[/b]
[green][i]typefield[/i][/green],sum(case when week=1 then 1 else 0 end),sum(case when week=2 then 1 else 0 end),sum(case when week=3 then 1 else 0 end),sum(case when week=4 then 1 else 0 end)

[b]from[/b] (
  [b]select[/b]
  [green][i]typefield[/i][/green],date_part([i]'day'[/i],now()-[green][i]datefield[/i][/green])/7 [b]as[/b] week
  [b]from[/b] [green][i]thetable[/i][/green]
) foo

[b]group by[/b] [green][i]typefield[/i][/green];

Feherke.

http://rootshell.be/~feherke/