Need help with an advanced bit o' SQL

[shanemjo]

I have a table that has a date field, called DateOf, in. What i am trying to do is this.

I need a query to show me what records are 0 to 7 days old(1 week old), 8 to 14 days(2 weeks old), 15 to 21 days old(3 weeks), 22 to 28days(4 weeks), and anything over 28 days old.

So the result i am after will have 5 values for the age of a record from the date the query is being run..

Liek this

Age of record | No.
1 week | 2
2 weeks | 3
3 weeks | 6
1 month | 8
over 1 month | 16

I just dont know where to start with this one.

Many thanks in advance...

Shane Jones

 

[swampBoogie]

select count(*), Age
 from (select case 
when (current_date - dateOf) day(4) < 7 then
 '1 Week'
when (current_date - dateOf) day(4) < 14 then
 '2 Weeks'
when (current_date - dateOf) day(4) < 21 then
 '3 Weeks'
when (current_date - dateOf) day(4) < 28 then
 '1 Month'
else 'Over 1 month' end as age from t) as dt
group by age

As you don't say which DBMS you are using I gave a standard SQL answer. You probably have to change the logic for calculate the number of days between two dates.

 

[shanemjo]

sorry i am using a delphi frotend to select data from an Access mdb file

will this change the code much ar is it all generally the same

 

[swampBoogie]

Access has a function named datediff for doing date and time arithmetic. Instead of using case you can either use iif or switch.