Home » SQL & PL/SQL » SQL & PL/SQL » Sql to loop through date (oracle9.2.0.3)
Sql to loop through date [message #414228] Tue, 21 July 2009 02:06 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
 select count(empid) from emp where emp_join_dte between &dt1 and &dt2
  
  
  
  
  
    date diff                count(*)   
  12-07-2009 - 13-07-2009    5
  13-07-2009 - 14-07-2009    6
  14-07-2009 - 15-07-2009    7 
  15-07-2009 - 16-07-2009    9
  16-07-2009 - 17-07-2009   11
  17-09-2009 - 18-07-2009   99
  19-07-2009 - 19-07-2009   88


Can it be done using a sql without writing pl/sql.
I want to get the count for the last week (sunday-saturday)
how many count is for one day difference for the past week
Thanks
Re: Sql to loop through date [message #414229 is a reply to message #414228] Tue, 21 July 2009 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You know... test case and so on.

Regards
Michel
Re: Sql to loop through date [message #414234 is a reply to message #414228] Tue, 21 July 2009 02:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you guarantee that there will be data available for every date in that date range?

If so, you can just do:
select to_char(trunc(emp_join_dte),'dd-mm-yyyy')||' - '|| to_char(trunc(emp_join_dte)+1,'dd-mm-yyyy')
      ,count(*)
from   emp
where  trunc(emp_join_dte) between to_date('&dt1','dd-mm-yyyy') and  to_date('&dt2','dd-mm-yyyy')
group by trunc(emp_join_dte);


If you can't, then you need to use a row generator technique, like
SELECT trunc(to_Date('&dt1','dd-mm-yyyy'))+level-1 from dual connect by level <= 7
to generate the dates, and then outer join your query to the row generator.

[EDITED by LF: fixed [code] tags]

[Updated on: Tue, 21 July 2009 02:54] by Moderator

Report message to a moderator

Re: Sql to loop through date [message #414254 is a reply to message #414234] Tue, 21 July 2009 04:01 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks.....
Previous Topic: query
Next Topic: Performance Tuning
Goto Forum:
  


Current Time: Wed Dec 07 18:12:17 CST 2016

Total time taken to generate the page: 0.24688 seconds