How to loop through a query in LINQPAD?

tanthiamhuat

I have this query in 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.

thanks.

kimbonics

select cast(TS_START as DATE) as THE_DATE_ITSELF, {rest of your query}
...
GROUP BY cast(TS_START as DATE)

Assumption is that you don't care when TS_END is...

tanthiamhuat

Do you mean this? My WHERE condition needs the TS_START and TS_End such that the data extracted is within 1 day duration of 24 hour+1min+59sec. Can we equate the TS_End <= TS_START.AddHours(24).AddMinutes(1).AddSeconds(59)?
i.e TS_End <= TS_START+1+(1/1440)+(59/86400)?

But how does it loop through to the next day, as TS_START is hardcoded as 13/01/2014?

select cast(TS_START as DATE) as THE_DATE_ITSELF, 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 <= TS_START+1+(1/1440)+(59/86400))
AND MONO != '9999999999'
GROUP BY cast(TS_START as DATE)

tanthiamhuat

http://stackoverflow.com/questions/18182791/how-to-repeat-select-query-when-date-within-range

above link may be useful....Connect by Level
how should I modify my SQL then?
take note my database is Oracle, so need to be careful.

tanthiamhuat

http://stackoverflow.com/questions/18182791/how-to-repeat-select-query-when-date-within-range

above link may be useful....Connect by Level
how should I modify my SQL then?
take note my database is Oracle, so need to be careful.

tanthiamhuat

see my original output as from
http://i61.tinypic.com/j94mye.jpg

I need the output to be like this:
Date ProductiveTime Comment
======== ========== -- Sum of those rows
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