Home » SQL & PL/SQL » SQL & PL/SQL » How to loop through a Date Range ? (Oracle 11g, Windows 7)
How to loop through a Date Range ? [message #608065] Fri, 14 February 2014 21:17 Go to next message
tanthiamhuat
Messages: 2
Registered: February 2014
Junior Member
I have this query using LINQPAD:

SELECT TIME_TEST+TIME_STEP AS Productive_Time
FROM PFODS.PPL_TESTSYSTEMS_UTILISATION
WHERE (SYSTEMTYPE = '0005-064')
AND (TS_START >= to_date('13/01/2014', 'DD/MM/YYYY'))
AND (TS_END <= to_date('18/01/2014 00:1:59', 'DD/MM/YYYY HH24:MI:SS')) AND MONO != '9999999999'
Order by TS_START ASC

However, I need to extract out the SELECT SUM(TIME_TEST+TIME_STEP) as ProductiveTime on a daily basis, and thus I need to execute those below codes each time to extract out each day values. How do I combine those into 1 single query such that each day data can be displayed? Do I need to write a loop inside the WHERE condition?

SELECT SUM(TIME_TEST+TIME_STEP) as ProductiveTime
FROM PFODS.PPL_TESTSYSTEMS_UTILISATION
WHERE (SYSTEMTYPE = '0005-064')
AND(TS_START >= to_date('13/01/2014', 'DD/MM/YYYY'))
AND (TS_END <= to_date('14/01/2014 00:1:59', 'DD/MM/YYYY HH24:MI:SS')) AND MONO != '9999999999'
Order by TS_START ASC

SELECT SUM(TIME_TEST+TIME_STEP) as ProductiveTime
FROM PFODS.PPL_TESTSYSTEMS_UTILISATION
WHERE (SYSTEMTYPE = '0005-064')
AND (TS_START >= to_date('14/01/2014', 'DD/MM/YYYY'))
AND (TS_END <= to_date('15/01/2014 00:1:59', 'DD/MM/YYYY HH24:MI:SS')) AND MONO != '9999999999'
Order by TS_START ASC

SELECT SUM(TIME_TEST+TIME_STEP) as ProductiveTime
FROM PFODS.PPL_TESTSYSTEMS_UTILISATION
WHERE (SYSTEMTYPE = '0005-064')
AND (TS_START >= to_date('15/01/2014', 'DD/MM/YYYY'))
AND (TS_END <= to_date('16/01/2014 00:1:59', 'DD/MM/YYYY HH24:MI:SS')) AND MONO != '9999999999'
Order by TS_START ASC

SELECT SUM(TIME_TEST+TIME_STEP) as ProductiveTime
FROM PFODS.PPL_TESTSYSTEMS_UTILISATION
WHERE (SYSTEMTYPE = '0005-064')
AND (TS_START >= to_date('16/01/2014', 'DD/MM/YYYY'))
AND (TS_END <= to_date('17/01/2014 00:1:59', 'DD/MM/YYYY HH24:MI:SS')) AND MONO != '9999999999'
Order by TS_START ASC

SELECT SUM(TIME_TEST+TIME_STEP) as ProductiveTime
FROM PFODS.PPL_TESTSYSTEMS_UTILISATION
WHERE (SYSTEMTYPE = '0005-064')
AND (TS_START >= to_date('17/01/2014', 'DD/MM/YYYY'))
AND (TS_END <= to_date('18/01/2014 00:1:59', 'DD/MM/YYYY HH24:MI:SS')) AND MONO != '9999999999'
Order by TS_START ASC

In addition to display the SUM(TIME_TEST+TIME_STEP) for each day, I also need to display the date itself.
Take note here that TS_END < = TS_START + 24hours + 1mins + 59secs,
so can we have this in the SQL?
TS_END <= TS_START + 1 + (1/1440) + (59/86400)?
Would the SQL query recognize the TS_START variable?

How do we then loop through for a daily basis? I read something that we can use "Connect by Level",
see http://stackoverflow.com/questions/18182791/how-to-repeat-select-query-when-date-within-range
is this useful for my case? If yes, how should I modify the code?

take note my database is Oracle, so need to be careful.

thanks.

[Updated on: Fri, 14 February 2014 21:27]

Report message to a moderator

Re: How to loop through a Date Range ? [message #608066 is a reply to message #608065] Fri, 14 February 2014 21:54 Go to previous messageGo to next message
BlackSwan
Messages: 22712
Registered: January 2009
Senior Member
>Take note here that TS_END < = TS_START + 24hours + 1mins + 59secs,
why the 1 minutes 59 second overlap?

I don't understand what is the results you desire or expect.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: How to loop through a Date Range ? [message #608067 is a reply to message #608066] Fri, 14 February 2014 22:43 Go to previous message
tanthiamhuat
Messages: 2
Registered: February 2014
Junior Member
please see attached printscreen.
I need the TS_END < = TS_START + 24hours + 1mins + 59secs condition,
as I am extracting out the data based on a 24 hour daily basis.
However, we see that the last entry for each daily basis always ends at past midnight, e.g 12:01:11AM on the last record, so to be on the safe side, I add 1 min + 59 sec.

I hope I am clear in the explanation.

The result I desire is to have a single SQL script, which will display the sum of the ProductiveTime on a daily basis, i.e.

Date ProductiveTime Comment
========== ==============
13/01/2014 43577 -- Row1 to Row3
14/01/2014 46723 -- Row4 to Row9
15/01/2014 54296 -- Row10 to Row13
16/01/2014 53385 -- Row14 to Row17
17/01/2014 52368 -- Row18 to Row22
  • Attachment: output1.jpg
    (Size: 211.90KB, Downloaded 23 times)

[Updated on: Fri, 14 February 2014 22:47]

Report message to a moderator

Previous Topic: Calling PL/SQL function dynamically (with IN and OUT parameters
Next Topic: SELECT query gives error
Goto Forum:
  


Current Time: Wed Aug 27 22:41:18 CDT 2014

Total time taken to generate the page: 0.06450 seconds