Query: Count (amount of (xDates later than GivenDate))

[antifashionpimp]

Hello all,

I have the following data (Note: dates in dd-mm-yyyy format)

ID DEPT TARGET70 TARGET90 TARGET100
-- ---- -------- -------- ---------
12 ACCT 12/06/2005 31/07/2005 21/12/2005
14 MKT 30/08/2005 02/09/2005 11/04/2006
19 ACCT 13/04/2004 13/05/2005 22/08/2005
20 SAL 12/12/2005 20/12/2005 12/02/2006
21 SAL 21/03/2004 21/03/2005 21/09/2005

etc. etc.

It basically shows what the target dates (TARGETxx) are for certain stages of a deparment’s (DEPT) different projects (ID).

What I have to do next, is proving to be quite trivial to me. Can someone please help?

I must compare the target dates to a certain date (which is stored in a global variable, say gdatTheDate), and then return all the projects (ID) for which a target date is later than gdatTheDate. But it (the amount of later dates) has to be counted for each dept and presented as follows:

Say gdatTheDate = 05/08/2005
Then the following is returned

DEPT NUM70 NUM90 NUM100
---- ----- ----- ------
ACCT 0 0 2
MKT 1 1 1
SAL 1 1 2

I know how to use the global variable in a query, but how do I implement the query to do the counting and grouping etc.?

Any help would be appreciated. Thanks,

J

 

[Pat Hartman]

The problem is that your table is not normalized. If you can't permanently change the table, you can do it with a union query. Assuming that there really are only three types of target date, the union will contain three select queries.

Select Dept, "Target70" As TargetName, Target70 As TargetDate
From YourTable Where Target70 > [Some Date]
Union Select Dept, "Target90" As TargetName, Target90 As TargetDate
From YourTable Where Target90 > [Some Date]
Union Select Dept, "Target100" As TargetName, Target100 As TargetDate
From YourTable Where Target100 > [Some Date];

Then make a crosstab query that uses the union query as its source. Make sure to explicitly declare the parameter in the union query or the crosstab will have a problem with it.

The union query selects only the data you need and the crosstab query does the counting for you.