Home » SQL & PL/SQL » SQL & PL/SQL » working with dates
working with dates [message #236060] Tue, 08 May 2007 22:38 Go to next message
yog_23
Messages: 79
Registered: March 2007
Member
I have several queries that find the ages of the difference between days. Just wondering if this can be done in one query ?

/**difference more than 7 days**/
select
count(*)
from
Table1
where
status = 'C' and
trunc(sysdate-create_dt) > 7;

/**difference more than 14 days**/
select
count(*)
from
Table1
where
status = 'C' and
trunc(sysdate-create_dt) > 14;

/**difference more than 30 days**/
select
count(*)
from
Table1
where
status = 'C' and
trunc(sysdate-create_dt) > 30;

/**difference more than 120 days**/
select
count(*)
from
Table1
where
status = 'C' and
trunc(sysdate-create_dt) > 120;

/**Similarly counts for other status **/

select
count(*)
from
Table1
where
status = 'E' and
trunc(sysdate-create_dt) > 7;
-- trunc(sysdate-create_dt) > 14;
-- trunc(sysdate-create_dt) > 30;
-- trunc(sysdate-create_dt) > 120;

Thank you.

Re: working with dates [message #236085 is a reply to message #236060] Wed, 09 May 2007 00:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Remove the date-restriction from the where-clause and move it to the select part.
Something like this (note: untested code)
select sum(case when create_dt < sysdate - 7 then 1
                else 0
           end) as seven_days
,      sum(case when create_dt < sysdate - 14 then 1
                else 0
           end) as fourteen_days
,      sum(case when create_dt < sysdate - 30 then 1
                else 0
           end) as thirty_days
,      sum(case when create_dt < sysdate - 120 then 1
                else 0
           end) as onehundredandtwenty_days
from   table1
where  status = 'C';
Re: working with dates [message #236105 is a reply to message #236085] Wed, 09 May 2007 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To refer to another topic, I prefer writting it:
select count(case when create_dt < sysdate - 7 then 1 end) as seven_days,
       count(case when create_dt < sysdate - 14 then 1 end) as fourteen_days,
       count(case when create_dt < sysdate - 30 then 1 end) as thirty_days,
       count(case when create_dt < sysdate - 30 then 1 end) as onehundredandtwenty_days
from table1
where status = 'C';


Smile

Regards
Michel
Re: working with dates [message #236289 is a reply to message #236105] Wed, 09 May 2007 08:43 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
thank you very much.

Its counting twice..

e.g.

If the difference is 16 days, it is counted in both the 7 days as well as 14 days count. Is there a way to do range between days.

e.g. If there is total of 100 records, 10 were 1 day old, 20 were 14 days old etc.., 70 were 30 days old etc

thank you

Re: working with dates [message #236290 is a reply to message #236289] Wed, 09 May 2007 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course, use between instead of < in the case clause.

Regards
Michel
Re: working with dates [message #236298 is a reply to message #236060] Wed, 09 May 2007 09:06 Go to previous message
yog_23
Messages: 79
Registered: March 2007
Member
got it..thank you Smile
Previous Topic: Help with tough query
Next Topic: needed order by in concat_all() function
Goto Forum:
  


Current Time: Sat Dec 03 10:06:40 CST 2016

Total time taken to generate the page: 0.11221 seconds