Q:
SQL Query Issues with date math
Table with partial sample data would appear as follows:
EQUIPNOÂ ASSEMBLYÂ START_TIMEÂ END_TIME 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 24-MAY-09Â Â 25-MAY-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 25-MAY-09Â Â 25-MAY-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â 25-MAY-09Â Â 26-MAY-09 304Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 25-MAY-09Â Â 04-JUN-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 26-MAY-09Â Â 26-MAY-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 26-MAY-09Â Â 27-MAY-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 27-MAY-09Â Â 28-MAY-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 28-MAY-09Â Â 28-MAY-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 28-MAY-09Â Â 29-MAY-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 29-MAY-09Â Â 29-MAY-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 29-MAY-09Â Â 30-MAY-09
Query as follows:
with a as ( select a.equipno, sum(a.end_time - a.start_time) as days_down from equipdowntimes a where a.assembly = 0 and a.equipno <= '502' and a.start_time between '24-MAY-09' and '30-MAY-09' group by a.equipno order by 1)
select a.equipno, a.days_down, 100-(100*((24*(a.days_down))/168)) as percent_up from a group by a.equipno, a.days_down order by percent_up
This query gets the following sample results:
EQUIPNOÂ DAYS_DOWNÂ Â Â PERCENT_UP 304Â Â Â Â Â Â Â 9.461805556Â Â Â -35.16865079 150Â Â Â Â Â Â Â Â 4.260416667Â Â Â 39.13690476
The query returns more days than what was asked for as shown in the example above. Additionally, if the query is written where the start_time is after the actual initial start_time began, i.e., (start_time >= ’26-MAY-09’), then zero results are returned even though there really is downtime to report. This is also true if the end_time is set within a day of the initial start time. So there are instances of downtimes that are not captured ith the above query. How do I resolve this issue?
Software/Hardware used:
Oracle SQL Developer 1.5.5
EQUIPNOÂ ASSEMBLYÂ START_TIMEÂ END_TIME 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 24-MAY-09Â Â 25-MAY-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 25-MAY-09Â Â 25-MAY-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â 25-MAY-09Â Â 26-MAY-09 304Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 25-MAY-09Â Â 04-JUN-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 26-MAY-09Â Â 26-MAY-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 26-MAY-09Â Â 27-MAY-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 27-MAY-09Â Â 28-MAY-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 28-MAY-09Â Â 28-MAY-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 28-MAY-09Â Â 29-MAY-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 29-MAY-09Â Â 29-MAY-09 150Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 29-MAY-09Â Â 30-MAY-09
Query as follows:
with a as ( select a.equipno, sum(a.end_time - a.start_time) as days_down from equipdowntimes a where a.assembly = 0 and a.equipno <= '502' and a.start_time between '24-MAY-09' and '30-MAY-09' group by a.equipno order by 1)
select a.equipno, a.days_down, 100-(100*((24*(a.days_down))/168)) as percent_up from a group by a.equipno, a.days_down order by percent_up
This query gets the following sample results:
EQUIPNOÂ DAYS_DOWNÂ Â Â PERCENT_UP 304Â Â Â Â Â Â Â 9.461805556Â Â Â -35.16865079 150Â Â Â Â Â Â Â Â 4.260416667Â Â Â 39.13690476
The query returns more days than what was asked for as shown in the example above. Additionally, if the query is written where the start_time is after the actual initial start_time began, i.e., (start_time >= ’26-MAY-09’), then zero results are returned even though there really is downtime to report. This is also true if the end_time is set within a day of the initial start time. So there are instances of downtimes that are not captured ith the above query. How do I resolve this issue?
Software/Hardware used:
Oracle SQL Developer 1.5.5
ASKED:
Sep 29 2009 7:37 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
You are getting more days because you are calculating the total downtime for each record, even when some records comprise dates outside your desired range.
You will have to check the end time for the records, and when the end_time is greater than the last day you want to include in the report, you should calculate the downtime based on that day, and not on the end_time column. And something similar needs to be done with the start times.
A combination of the DECODE and SIGN functions could help.
The downtime calculation should be something like this:
sum(decode(sign(a.end_time-to_date('30-MAY-09')),1,'30-MAY-2009',a.end_time) - decode(sign(a.start_time-to_date('24-MAY-09')),-1,'24-MAY-2009',a.start_time))Also, filtering the results using the start_time only will not work.
You need a condition similar to this:
a.start_time <= '30-MAY-09' AND a.end_time >= '24-MAY-09'
