Bar chart question

cja7928 · December 23, 2013, 1:52am

I have set up a stacked bar chart with 7 columns and 6 items or rows for each column. I have used the cell update function and tied each row/column to 42 different SQL query tags. So basically I have 6 different SQL queries that are repeated 7 different times for each day of the week. Is there a better way to do this than having 42 sql tags tied to the cell update? is there a way to do a group of SQL queries through the chart itself rather than having all the tags? Please let me know if what I have done is the proper/clean way of going about this.

Thank you.

Chris.

Pat · December 23, 2013, 4:01pm

Is there a better way to do this? Probably. Ideally you’d have the chart data bound to one SQL query. If you could give some information about what you are displaying and how your database table is set up we could give you a hand with the query.

cja7928 · December 23, 2013, 6:46pm

I have attached screen shots of the table cell update and the database table I set up, as well as what the chart looks like. here is one example of the sql queries im running. basically they are all similar queries just changing the day, shift and event type (running, idle, ect)

SELECT NVL(SUM(EVENT_DURATION * 24),0) FROM (SELECT (DATE_DT - LEAD(DATE_DT) OVER ( ORDER BY DATE_DT DESC)) AS EVENT_DURATION, LEAD(EVENTTYPE, 1,0) OVER ( ORDER BY DATE_DT DESC) AS EVENTTYPE, LEAD(SHIFT, 1,0) OVER ( ORDER BY DATE_DT DESC) AS SHIFT FROM (SELECT DATE_DT, SHIFT, ASSET_ID, EVENTTYPE FROM TEST_CHRIS WHERE DATE_DT BETWEEN (SELECT MIN(DATE_DT) FROM TEST_CHRIS WHERE ASSET_ID = {[.]../../../../Equipment/Equipment_ID} AND SHIFT = 1 AND TRUNC(DATE_DT) = TRUNC(SYSDATE)-2) AND (SELECT MIN(DATE_DT) FROM TEST_CHRIS WHERE ASSET_ID = {[.]../../../../Equipment/Equipment_ID} AND SHIFT = 1 AND TRUNC(DATE_DT) = TRUNC(SYSDATE)-1) AND ASSET_ID = {[.]../../../../Equipment/Equipment_ID} ORDER BY DATE_DT)) WHERE EVENTTYPE = 1 AND SHIFT = 1

[attachment=2]cell update.jpg[/attachment]
[attachment=1]table.jpg[/attachment]
[attachment=0]chart.jpg[/attachment]

The query tags and chart (cell update) work as expected but Im just not sure how to do the same thing in a single sql query within the chart that will populate all the rows and columns of the bar chart as shown. Thank you for the help I really appreciate it.

Chris

JordanCClark · December 23, 2013, 7:55pm

I’m not Oracle-ish by any means, and I don’t know what your event types are, so take what I have with a pound of salt…

I use something similar to grab energy production from our windmill. Another 32 years and it’s all ours!

SELECT DATE_FORMAT(DATE_DT,'%W %m-%d') as datestamp, SUM(IF(EVENTTYPE=1,(EVENT_DURATION *24),0)) AS RunGrave, SUM(IF(EVENTTYPE=2,(EVENT_DURATION *24),0)) AS RunSwing, SUM(IF(EVENTTYPE=3,(EVENT_DURATION *24),0)) AS Indirect FROM TEST_CHRIS WHERE DATE_DT <= NOW() AND DATE_DT<=DATE_ADD(NOW(), interval -7 day) AND SHIFT=1 GROUP BY datestamp, ORDER BY DATE_DT

EDIT: It looks a bit confusing to group by datestamp, then order by date_dt, but the reason for that is if you order by datestamp, it would be in alphabetical order: Friday-Monday-Saturday-Sunday… ordering by date_dt puts it back into date order.

cja7928 · December 23, 2013, 8:42pm

thank you for the example. Im going to start testing using that example. I’ll let you know how I do in a day or two. Thanks again.

cja7928 · January 4, 2014, 12:20am

OK I got it figured out with a single query. its a bit long but ill post part of it for reference.

[code]SELECT MIN(DATE_TIME) AS DATE_TIME,
NVL(SUM(DAYS_EVENT_DURATION * 24),0) AS RUN_DAYS,
NVL(SUM(SWING_EVENT_DURATION * 24),0) AS RUN_SWING,
NVL(SUM(GRAVE_EVENT_DURATION * 24),0) AS RUN_GRAVE,
NVL(SUM(INDIRECT_EVENT_DURATION * 24),0) AS INDIRECT,
NVL(SUM(PM_EVENT_DURATION * 24),0) AS PM,
NVL(SUM(REPAIR_WAIT_EVENT_DURATION * 24),0) AS REPAIR_WAIT,
NVL(SUM(REPAIR_ARRIVED_EVENT_DURATION * 24),0) AS REPAIR_ARRIVED,
NVL(SUM(IDLE_EVENT_DURATION * 24),0) AS IDLE

FROM ------------------------------------------------RUN DAYS

(SELECT DATE_TIME,
DAYS_EVENT_DURATION,
SWING_EVENT_DURATION,
GRAVE_EVENT_DURATION,
INDIRECT_EVENT_DURATION,
PM_EVENT_DURATION,
REPAIR_WAIT_EVENT_DURATION,
REPAIR_ARRIVED_EVENT_DURATION,
IDLE_EVENT_DURATION
FROM
(SELECT (DATE_DT - LEAD(DATE_DT) OVER (
ORDER BY DATE_DT DESC)) AS DAYS_EVENT_DURATION,
NULL AS SWING_EVENT_DURATION,
NULL AS GRAVE_EVENT_DURATION,
NULL AS INDIRECT_EVENT_DURATION,
NULL AS PM_EVENT_DURATION,
NULL AS REPAIR_WAIT_EVENT_DURATION,
NULL AS REPAIR_ARRIVED_EVENT_DURATION,
NULL AS IDLE_EVENT_DURATION,
LEAD(EVENTTYPE, 1,0) OVER (
ORDER BY DATE_DT DESC) AS EVENTTYPE,
LEAD(SHIFT, 1,0) OVER (
ORDER BY DATE_DT DESC) AS SHIFT,
TRUNC(DATE_DT) AS DATE_TIME
FROM
(SELECT DATE_DT,
SHIFT,
ASSET_ID,
EVENTTYPE
FROM TEST_CHRIS
WHERE DATE_DT BETWEEN to_date(to_char(sysdate-0, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’) AND to_date(to_char(sysdate+1, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND ASSET_ID = 1916
ORDER BY DATE_DT))
WHERE SHIFT = 1
AND EVENTTYPE = 1
UNION ALL ----------------------------------------------------RUN SWING
SELECT DATE_TIME,
DAYS_EVENT_DURATION,
SWING_EVENT_DURATION,
GRAVE_EVENT_DURATION,
INDIRECT_EVENT_DURATION,
PM_EVENT_DURATION,
REPAIR_WAIT_EVENT_DURATION,
REPAIR_ARRIVED_EVENT_DURATION,
IDLE_EVENT_DURATION
FROM
(SELECT NULL AS DAYS_EVENT_DURATION,
(DATE_DT - LEAD(DATE_DT) OVER (
ORDER BY DATE_DT DESC)) AS SWING_EVENT_DURATION,
NULL AS GRAVE_EVENT_DURATION,
NULL AS INDIRECT_EVENT_DURATION,
NULL AS PM_EVENT_DURATION,
NULL AS REPAIR_WAIT_EVENT_DURATION,
NULL AS REPAIR_ARRIVED_EVENT_DURATION,
NULL AS IDLE_EVENT_DURATION,
LEAD(EVENTTYPE, 1,0) OVER (
ORDER BY DATE_DT DESC) AS EVENTTYPE,
LEAD(SHIFT, 1,0) OVER (
ORDER BY DATE_DT DESC) AS SHIFT,
TRUNC(DATE_DT) AS DATE_TIME
FROM
(SELECT DATE_DT,
SHIFT,
ASSET_ID,
EVENTTYPE
FROM TEST_CHRIS
WHERE DATE_DT BETWEEN to_date(to_char(sysdate-0, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’) AND to_date(to_char(sysdate+1, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND ASSET_ID = 1916
ORDER BY DATE_DT))
WHERE SHIFT = 2
AND EVENTTYPE = 1
UNION ALL ----------------------------------------------------RUN GRAVE
SELECT DATE_TIME,
DAYS_EVENT_DURATION,
SWING_EVENT_DURATION,
GRAVE_EVENT_DURATION,
INDIRECT_EVENT_DURATION,
PM_EVENT_DURATION,
REPAIR_WAIT_EVENT_DURATION,
REPAIR_ARRIVED_EVENT_DURATION,
IDLE_EVENT_DURATION
FROM
(SELECT NULL AS DAYS_EVENT_DURATION,
NULL AS SWING_EVENT_DURATION,
(DATE_DT - LEAD(DATE_DT) OVER (
ORDER BY DATE_DT DESC)) AS GRAVE_EVENT_DURATION,
NULL AS INDIRECT_EVENT_DURATION,
NULL AS PM_EVENT_DURATION,
NULL AS REPAIR_WAIT_EVENT_DURATION,
NULL AS REPAIR_ARRIVED_EVENT_DURATION,
NULL AS IDLE_EVENT_DURATION,
LEAD(EVENTTYPE, 1,0) OVER (
ORDER BY DATE_DT DESC) AS EVENTTYPE,
LEAD(SHIFT, 1,0) OVER (
ORDER BY DATE_DT DESC) AS SHIFT,
TRUNC(DATE_DT) AS DATE_TIME
FROM
(SELECT DATE_DT,
SHIFT,
ASSET_ID,
EVENTTYPE
FROM TEST_CHRIS
WHERE DATE_DT BETWEEN to_date(to_char(sysdate-0, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’) AND to_date(to_char(sysdate+1, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND ASSET_ID = 1916
ORDER BY DATE_DT))
WHERE SHIFT = 3
AND EVENTTYPE = 1
UNION ALL ----------------------------------------------------IDLE
SELECT DATE_TIME,
DAYS_EVENT_DURATION,
SWING_EVENT_DURATION,
GRAVE_EVENT_DURATION,
INDIRECT_EVENT_DURATION,
PM_EVENT_DURATION,
REPAIR_WAIT_EVENT_DURATION,
REPAIR_ARRIVED_EVENT_DURATION,
IDLE_EVENT_DURATION
FROM
(SELECT NULL AS DAYS_EVENT_DURATION,
NULL AS SWING_EVENT_DURATION,
NULL AS GRAVE_EVENT_DURATION,
(DATE_DT - LEAD(DATE_DT) OVER (
ORDER BY DATE_DT DESC)) AS INDIRECT_EVENT_DURATION,
NULL AS PM_EVENT_DURATION,
NULL AS REPAIR_WAIT_EVENT_DURATION,
NULL AS REPAIR_ARRIVED_EVENT_DURATION,
NULL AS IDLE_EVENT_DURATION,
LEAD(EVENTTYPE, 1,0) OVER (
ORDER BY DATE_DT DESC) AS EVENTTYPE,
LEAD(SHIFT, 1,0) OVER (
ORDER BY DATE_DT DESC) AS SHIFT,
TRUNC(DATE_DT) AS DATE_TIME
FROM
(SELECT DATE_DT,
SHIFT,
ASSET_ID,
EVENTTYPE
FROM TEST_CHRIS
WHERE DATE_DT BETWEEN to_date(to_char(sysdate-0, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’) AND to_date(to_char(sysdate+1, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND ASSET_ID = 1916
ORDER BY DATE_DT))
WHERE EVENTTYPE = 2
UNION ALL ----------------------------------------------------PM
SELECT DATE_TIME,
DAYS_EVENT_DURATION,
SWING_EVENT_DURATION,
GRAVE_EVENT_DURATION,
INDIRECT_EVENT_DURATION,
PM_EVENT_DURATION,
REPAIR_WAIT_EVENT_DURATION,
REPAIR_ARRIVED_EVENT_DURATION,
IDLE_EVENT_DURATION
FROM
(SELECT NULL AS DAYS_EVENT_DURATION,
NULL AS SWING_EVENT_DURATION,
NULL AS GRAVE_EVENT_DURATION,
NULL AS INDIRECT_EVENT_DURATION,
(DATE_DT - LEAD(DATE_DT) OVER (
ORDER BY DATE_DT DESC)) AS PM_EVENT_DURATION,
NULL AS REPAIR_WAIT_EVENT_DURATION,
NULL AS REPAIR_ARRIVED_EVENT_DURATION,
NULL AS IDLE_EVENT_DURATION,
LEAD(EVENTTYPE, 1,0) OVER (
ORDER BY DATE_DT DESC) AS EVENTTYPE,
LEAD(SHIFT, 1,0) OVER (
ORDER BY DATE_DT DESC) AS SHIFT,
TRUNC(DATE_DT) AS DATE_TIME
FROM
(SELECT DATE_DT,
SHIFT,
ASSET_ID,
EVENTTYPE
FROM TEST_CHRIS
WHERE DATE_DT BETWEEN to_date(to_char(sysdate-0, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’) AND to_date(to_char(sysdate+1, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND ASSET_ID = 1916
ORDER BY DATE_DT))
WHERE EVENTTYPE = 7
UNION ALL ----------------------------------------------------REPAIR WAIT
SELECT DATE_TIME,
DAYS_EVENT_DURATION,
SWING_EVENT_DURATION,
GRAVE_EVENT_DURATION,
INDIRECT_EVENT_DURATION,
PM_EVENT_DURATION,
REPAIR_WAIT_EVENT_DURATION,
REPAIR_ARRIVED_EVENT_DURATION,
IDLE_EVENT_DURATION
FROM
(SELECT NULL AS DAYS_EVENT_DURATION,
NULL AS SWING_EVENT_DURATION,
NULL AS GRAVE_EVENT_DURATION,
NULL AS INDIRECT_EVENT_DURATION,
NULL AS PM_EVENT_DURATION,
(DATE_DT - LEAD(DATE_DT) OVER (
ORDER BY DATE_DT DESC)) AS REPAIR_WAIT_EVENT_DURATION,
NULL AS REPAIR_ARRIVED_EVENT_DURATION,
NULL AS IDLE_EVENT_DURATION,
LEAD(EVENTTYPE, 1,0) OVER (
ORDER BY DATE_DT DESC) AS EVENTTYPE,
LEAD(SHIFT, 1,0) OVER (
ORDER BY DATE_DT DESC) AS SHIFT,
TRUNC(DATE_DT) AS DATE_TIME
FROM
(SELECT DATE_DT,
SHIFT,
ASSET_ID,
EVENTTYPE
FROM TEST_CHRIS
WHERE DATE_DT BETWEEN to_date(to_char(sysdate-0, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’) AND to_date(to_char(sysdate+1, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND ASSET_ID = 1916
ORDER BY DATE_DT))
WHERE EVENTTYPE = 8
UNION ALL ----------------------------------------------------REPAIR ARRIVED
SELECT DATE_TIME,
DAYS_EVENT_DURATION,
SWING_EVENT_DURATION,
GRAVE_EVENT_DURATION,
INDIRECT_EVENT_DURATION,
PM_EVENT_DURATION,
REPAIR_WAIT_EVENT_DURATION,
REPAIR_ARRIVED_EVENT_DURATION,
IDLE_EVENT_DURATION
FROM
(SELECT NULL AS DAYS_EVENT_DURATION,
NULL AS SWING_EVENT_DURATION,
NULL AS GRAVE_EVENT_DURATION,
NULL AS INDIRECT_EVENT_DURATION,
NULL AS PM_EVENT_DURATION,
NULL AS REPAIR_WAIT_EVENT_DURATION,
(DATE_DT - LEAD(DATE_DT) OVER (
ORDER BY DATE_DT DESC)) AS REPAIR_ARRIVED_EVENT_DURATION,
NULL AS IDLE_EVENT_DURATION,
LEAD(EVENTTYPE, 1,0) OVER (
ORDER BY DATE_DT DESC) AS EVENTTYPE,
LEAD(SHIFT, 1,0) OVER (
ORDER BY DATE_DT DESC) AS SHIFT,
TRUNC(DATE_DT) AS DATE_TIME
FROM
(SELECT DATE_DT,
SHIFT,
ASSET_ID,
EVENTTYPE
FROM TEST_CHRIS
WHERE DATE_DT BETWEEN to_date(to_char(sysdate-0, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’) AND to_date(to_char(sysdate+1, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND ASSET_ID = 1916
ORDER BY DATE_DT))
WHERE EVENTTYPE = 9

UNION ALL ----------------------------------------------------IDLE
SELECT DATE_TIME,
DAYS_EVENT_DURATION,
SWING_EVENT_DURATION,
GRAVE_EVENT_DURATION,
INDIRECT_EVENT_DURATION,
PM_EVENT_DURATION,
REPAIR_WAIT_EVENT_DURATION,
REPAIR_ARRIVED_EVENT_DURATION,
IDLE_EVENT_DURATION
FROM
(SELECT NULL AS DAYS_EVENT_DURATION,
NULL AS SWING_EVENT_DURATION,
NULL AS GRAVE_EVENT_DURATION,
NULL AS INDIRECT_EVENT_DURATION,
NULL AS PM_EVENT_DURATION,
NULL AS REPAIR_WAIT_EVENT_DURATION,
NULL AS REPAIR_ARRIVED_EVENT_DURATION,
(DATE_DT - LEAD(DATE_DT) OVER (
ORDER BY DATE_DT DESC)) AS IDLE_EVENT_DURATION,
LEAD(EVENTTYPE, 1,0) OVER (
ORDER BY DATE_DT DESC) AS EVENTTYPE,
LEAD(SHIFT, 1,0) OVER (
ORDER BY DATE_DT DESC) AS SHIFT,
TRUNC(DATE_DT) AS DATE_TIME
FROM
(SELECT DATE_DT,
SHIFT,
ASSET_ID,
EVENTTYPE
FROM TEST_CHRIS
WHERE DATE_DT BETWEEN to_date(to_char(sysdate-0, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’) AND to_date(to_char(sysdate+1, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND ASSET_ID = 1916
ORDER BY DATE_DT))
WHERE EVENTTYPE = 0)

UNION -------------PREVIOUIS DAY 1

SELECT MIN(DATE_TIME) AS DATE_TIME,
NVL(SUM(DAYS_EVENT_DURATION * 24),0) AS RUN_DAYS,
NVL(SUM(SWING_EVENT_DURATION * 24),0) AS RUN_SWING,
NVL(SUM(GRAVE_EVENT_DURATION * 24),0) AS RUN_GRAVE,
NVL(SUM(INDIRECT_EVENT_DURATION * 24),0) AS INDIRECT,
NVL(SUM(PM_EVENT_DURATION * 24),0) AS PM,
NVL(SUM(REPAIR_WAIT_EVENT_DURATION * 24),0) AS REPAIR_WAIT,
NVL(SUM(REPAIR_ARRIVED_EVENT_DURATION * 24),0) AS REPAIR_ARRIVED,
24-(NVL(SUM(DAYS_EVENT_DURATION * 24),0)+NVL(SUM(SWING_EVENT_DURATION * 24),0)+NVL(SUM(GRAVE_EVENT_DURATION * 24),0)+NVL(SUM(INDIRECT_EVENT_DURATION * 24),0)+ NVL(SUM(PM_EVENT_DURATION * 24),0)+NVL(SUM(REPAIR_WAIT_EVENT_DURATION * 24),0)+ NVL(SUM(REPAIR_ARRIVED_EVENT_DURATION * 24),0)) AS IDLE
FROM ------------------------------------------------RUN DAYS

(SELECT DATE_TIME,
DAYS_EVENT_DURATION,
SWING_EVENT_DURATION,
GRAVE_EVENT_DURATION,
INDIRECT_EVENT_DURATION,
PM_EVENT_DURATION,
REPAIR_WAIT_EVENT_DURATION,
REPAIR_ARRIVED_EVENT_DURATION
FROM
(SELECT (DATE_DT - LEAD(DATE_DT) OVER (
ORDER BY DATE_DT DESC)) AS DAYS_EVENT_DURATION,
NULL AS SWING_EVENT_DURATION,
NULL AS GRAVE_EVENT_DURATION,
NULL AS INDIRECT_EVENT_DURATION,
NULL AS PM_EVENT_DURATION,
NULL AS REPAIR_WAIT_EVENT_DURATION,
NULL AS REPAIR_ARRIVED_EVENT_DURATION,
LEAD(EVENTTYPE, 1,0) OVER (
ORDER BY DATE_DT DESC) AS EVENTTYPE,
LEAD(SHIFT, 1,0) OVER (
ORDER BY DATE_DT DESC) AS SHIFT,
TRUNC(DATE_DT) AS DATE_TIME
FROM
(SELECT DATE_DT,
SHIFT,
ASSET_ID,
EVENTTYPE
FROM TEST_CHRIS
WHERE DATE_DT BETWEEN to_date(to_char(sysdate-1, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’) AND to_date(to_char(sysdate-0, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND ASSET_ID = 1916
ORDER BY DATE_DT))
WHERE SHIFT = 1
AND EVENTTYPE = 1
UNION ALL ----------------------------------------------------RUN SWING
SELECT DATE_TIME,
DAYS_EVENT_DURATION,
SWING_EVENT_DURATION,
GRAVE_EVENT_DURATION,
INDIRECT_EVENT_DURATION,
PM_EVENT_DURATION,
REPAIR_WAIT_EVENT_DURATION,
REPAIR_ARRIVED_EVENT_DURATION
FROM
(SELECT NULL AS DAYS_EVENT_DURATION,
(DATE_DT - LEAD(DATE_DT) OVER (
ORDER BY DATE_DT DESC)) AS SWING_EVENT_DURATION,
NULL AS GRAVE_EVENT_DURATION,
NULL AS INDIRECT_EVENT_DURATION,
NULL AS PM_EVENT_DURATION,
NULL AS REPAIR_WAIT_EVENT_DURATION,
NULL AS REPAIR_ARRIVED_EVENT_DURATION,
LEAD(EVENTTYPE, 1,0) OVER (
ORDER BY DATE_DT DESC) AS EVENTTYPE,
LEAD(SHIFT, 1,0) OVER (
ORDER BY DATE_DT DESC) AS SHIFT,
TRUNC(DATE_DT) AS DATE_TIME
FROM
(SELECT DATE_DT,
SHIFT,
ASSET_ID,
EVENTTYPE
FROM TEST_CHRIS
WHERE DATE_DT BETWEEN to_date(to_char(sysdate-1, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’) AND to_date(to_char(sysdate-0, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND ASSET_ID = 1916
ORDER BY DATE_DT))
WHERE SHIFT = 2
AND EVENTTYPE = 1
UNION ALL ----------------------------------------------------RUN GRAVE
SELECT DATE_TIME,
DAYS_EVENT_DURATION,
SWING_EVENT_DURATION,
GRAVE_EVENT_DURATION,
INDIRECT_EVENT_DURATION,
PM_EVENT_DURATION,
REPAIR_WAIT_EVENT_DURATION,
REPAIR_ARRIVED_EVENT_DURATION
FROM
(SELECT NULL AS DAYS_EVENT_DURATION,
NULL AS SWING_EVENT_DURATION,
(DATE_DT - LEAD(DATE_DT) OVER (
ORDER BY DATE_DT DESC)) AS GRAVE_EVENT_DURATION,
NULL AS INDIRECT_EVENT_DURATION,
NULL AS PM_EVENT_DURATION,
NULL AS REPAIR_WAIT_EVENT_DURATION,
NULL AS REPAIR_ARRIVED_EVENT_DURATION,
LEAD(EVENTTYPE, 1,0) OVER (
ORDER BY DATE_DT DESC) AS EVENTTYPE,
LEAD(SHIFT, 1,0) OVER (
ORDER BY DATE_DT DESC) AS SHIFT,
TRUNC(DATE_DT) AS DATE_TIME
FROM
(SELECT DATE_DT,
SHIFT,
ASSET_ID,
EVENTTYPE
FROM TEST_CHRIS
WHERE DATE_DT BETWEEN to_date(to_char(sysdate-1, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’) AND to_date(to_char(sysdate-0, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND ASSET_ID = 1916
ORDER BY DATE_DT))
WHERE SHIFT = 3
AND EVENTTYPE = 1
UNION ALL ----------------------------------------------------IDLE
SELECT DATE_TIME,
DAYS_EVENT_DURATION,
SWING_EVENT_DURATION,
GRAVE_EVENT_DURATION,
INDIRECT_EVENT_DURATION,
PM_EVENT_DURATION,
REPAIR_WAIT_EVENT_DURATION,
REPAIR_ARRIVED_EVENT_DURATION
FROM
(SELECT NULL AS DAYS_EVENT_DURATION,
NULL AS SWING_EVENT_DURATION,
NULL AS GRAVE_EVENT_DURATION,
(DATE_DT - LEAD(DATE_DT) OVER (
ORDER BY DATE_DT DESC)) AS INDIRECT_EVENT_DURATION,
NULL AS PM_EVENT_DURATION,
NULL AS REPAIR_WAIT_EVENT_DURATION,
NULL AS REPAIR_ARRIVED_EVENT_DURATION,
LEAD(EVENTTYPE, 1,0) OVER (
ORDER BY DATE_DT DESC) AS EVENTTYPE,
LEAD(SHIFT, 1,0) OVER (
ORDER BY DATE_DT DESC) AS SHIFT,
TRUNC(DATE_DT) AS DATE_TIME
FROM
(SELECT DATE_DT,
SHIFT,
ASSET_ID,
EVENTTYPE
FROM TEST_CHRIS
WHERE DATE_DT BETWEEN to_date(to_char(sysdate-1, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’) AND to_date(to_char(sysdate-0, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND ASSET_ID = 1916
ORDER BY DATE_DT))
WHERE EVENTTYPE = 2
UNION ALL ----------------------------------------------------PM
SELECT DATE_TIME,
DAYS_EVENT_DURATION,
SWING_EVENT_DURATION,
GRAVE_EVENT_DURATION,
INDIRECT_EVENT_DURATION,
PM_EVENT_DURATION,
REPAIR_WAIT_EVENT_DURATION,
REPAIR_ARRIVED_EVENT_DURATION
FROM
(SELECT NULL AS DAYS_EVENT_DURATION,
NULL AS SWING_EVENT_DURATION,
NULL AS GRAVE_EVENT_DURATION,
NULL AS INDIRECT_EVENT_DURATION,
(DATE_DT - LEAD(DATE_DT) OVER (
ORDER BY DATE_DT DESC)) AS PM_EVENT_DURATION,
NULL AS REPAIR_WAIT_EVENT_DURATION,
NULL AS REPAIR_ARRIVED_EVENT_DURATION,
LEAD(EVENTTYPE, 1,0) OVER (
ORDER BY DATE_DT DESC) AS EVENTTYPE,
LEAD(SHIFT, 1,0) OVER (
ORDER BY DATE_DT DESC) AS SHIFT,
TRUNC(DATE_DT) AS DATE_TIME
FROM
(SELECT DATE_DT,
SHIFT,
ASSET_ID,
EVENTTYPE
FROM TEST_CHRIS
WHERE DATE_DT BETWEEN to_date(to_char(sysdate-1, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’) AND to_date(to_char(sysdate-0, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND ASSET_ID = 1916
ORDER BY DATE_DT))
WHERE EVENTTYPE = 7
UNION ALL ----------------------------------------------------REPAIR WAIT
SELECT DATE_TIME,
DAYS_EVENT_DURATION,
SWING_EVENT_DURATION,
GRAVE_EVENT_DURATION,
INDIRECT_EVENT_DURATION,
PM_EVENT_DURATION,
REPAIR_WAIT_EVENT_DURATION,
REPAIR_ARRIVED_EVENT_DURATION
FROM
(SELECT NULL AS DAYS_EVENT_DURATION,
NULL AS SWING_EVENT_DURATION,
NULL AS GRAVE_EVENT_DURATION,
NULL AS INDIRECT_EVENT_DURATION,
NULL AS PM_EVENT_DURATION,
(DATE_DT - LEAD(DATE_DT) OVER (
ORDER BY DATE_DT DESC)) AS REPAIR_WAIT_EVENT_DURATION,
NULL AS REPAIR_ARRIVED_EVENT_DURATION,
LEAD(EVENTTYPE, 1,0) OVER (
ORDER BY DATE_DT DESC) AS EVENTTYPE,
LEAD(SHIFT, 1,0) OVER (
ORDER BY DATE_DT DESC) AS SHIFT,
TRUNC(DATE_DT) AS DATE_TIME
FROM
(SELECT DATE_DT,
SHIFT,
ASSET_ID,
EVENTTYPE
FROM TEST_CHRIS
WHERE DATE_DT BETWEEN to_date(to_char(sysdate-1, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’) AND to_date(to_char(sysdate-0, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND ASSET_ID = 1916
ORDER BY DATE_DT))
WHERE EVENTTYPE = 8
UNION ALL ----------------------------------------------------REPAIR ARRIVED
SELECT DATE_TIME,
DAYS_EVENT_DURATION,
SWING_EVENT_DURATION,
GRAVE_EVENT_DURATION,
INDIRECT_EVENT_DURATION,
PM_EVENT_DURATION,
REPAIR_WAIT_EVENT_DURATION,
REPAIR_ARRIVED_EVENT_DURATION
FROM
(SELECT NULL AS DAYS_EVENT_DURATION,
NULL AS SWING_EVENT_DURATION,
NULL AS GRAVE_EVENT_DURATION,
NULL AS INDIRECT_EVENT_DURATION,
NULL AS PM_EVENT_DURATION,
NULL AS REPAIR_WAIT_EVENT_DURATION,
(DATE_DT - LEAD(DATE_DT) OVER (
ORDER BY DATE_DT DESC)) AS REPAIR_ARRIVED_EVENT_DURATION,
LEAD(EVENTTYPE, 1,0) OVER (
ORDER BY DATE_DT DESC) AS EVENTTYPE,
LEAD(SHIFT, 1,0) OVER (
ORDER BY DATE_DT DESC) AS SHIFT,
TRUNC(DATE_DT) AS DATE_TIME
FROM
(SELECT DATE_DT,
SHIFT,
ASSET_ID,
EVENTTYPE
FROM TEST_CHRIS
WHERE DATE_DT BETWEEN to_date(to_char(sysdate-1, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’) AND to_date(to_char(sysdate-0, ‘YYYY-MM-DD’) || ‘05:30:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND ASSET_ID = 1916
ORDER BY DATE_DT))
WHERE EVENTTYPE = 9)

UNION -----------PREVIOUS DAY 2
[/code]

diat150 · January 4, 2014, 1:15am

holy ****

cja7928 · January 4, 2014, 11:15am

yea its a bit long… thats just for 2 days. its just a lot of repetition. similar query 7 times over for each condition its looking for then repeated another 7 times over for each day. it works though. much nicer than having 42 different tags tied into each template…