Query: Count (amount Of (xDates Later Than GivenDate))

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