SQL Query Issues with date math

10 pts.
Tags:
Date calculation
Date format
SQL
SQL Query
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
ASKED: September 29, 2009  7:37 PM
UPDATED: September 29, 2009  10:35 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

By looking at your query, <b>I will assume</b> you are doing this on an Oracle database.

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:

<pre>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))</pre>

Also, filtering the results using the start_time only will not work.

You need a condition similar to this:

<pre>a.start_time <= ’30-MAY-09′ AND a.end_time >= ’24-MAY-09′ </pre>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following