# Bar chart question

**URL:** <https://forum.inductiveautomation.com/t/bar-chart-question/7748>\
**Category:** Ignition\
**Created:** [December 23, 2013, 1:52am UTC](https://forum.inductiveautomation.com/t/bar-chart-question/7748 "2013-12-23T01:52:41Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![cja7928](https://avatars.discourse-cdn.com/v4/letter/c/258eb7/32.png) [@cja7928](https://forum.inductiveautomation.com/u/cja7928)\
**Post date:** [December 23, 2013, 1:52am UTC](https://forum.inductiveautomation.com/t/bar-chart-question/7748/1 "2013-12-23T01:52:41Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Pat](https://avatars.discourse-cdn.com/v4/letter/p/8491ac/32.png) [@Pat](https://forum.inductiveautomation.com/u/Pat)\
**Post date:** [December 23, 2013, 4:01pm UTC](https://forum.inductiveautomation.com/t/bar-chart-question/7748/2 "2013-12-23T16:01:40Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![cja7928](https://avatars.discourse-cdn.com/v4/letter/c/258eb7/32.png) [@cja7928](https://forum.inductiveautomation.com/u/cja7928)\
**Post date:** [December 23, 2013, 6:46pm UTC](https://forum.inductiveautomation.com/t/bar-chart-question/7748/3 "2013-12-23T18:46:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [December 23, 2013, 7:55pm UTC](https://forum.inductiveautomation.com/t/bar-chart-question/7748/4 "2013-12-23T19:55:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![cja7928](https://avatars.discourse-cdn.com/v4/letter/c/258eb7/32.png) [@cja7928](https://forum.inductiveautomation.com/u/cja7928)\
**Post date:** [December 23, 2013, 8:42pm UTC](https://forum.inductiveautomation.com/t/bar-chart-question/7748/5 "2013-12-23T20:42:40Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![cja7928](https://avatars.discourse-cdn.com/v4/letter/c/258eb7/32.png) [@cja7928](https://forum.inductiveautomation.com/u/cja7928)\
**Post date:** [January 4, 2014, 12:20am UTC](https://forum.inductiveautomation.com/t/bar-chart-question/7748/6 "2014-01-04T00:20:20Z")

</div>

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]

---

<div class="post-metadata">

**Author:** ![diat150](https://avatars.discourse-cdn.com/v4/letter/d/a587f6/32.png) [@diat150](https://forum.inductiveautomation.com/u/diat150)\
**Post date:** [January 4, 2014, 1:15am UTC](https://forum.inductiveautomation.com/t/bar-chart-question/7748/7 "2014-01-04T01:15:44Z")

</div>

holy \*\*\*\* ![:thumb_left:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e26afb081ddde6df016df6ee49159cf56aaca688.gif ":thumb\_left:")

---

<div class="post-metadata">

**Author:** ![cja7928](https://avatars.discourse-cdn.com/v4/letter/c/258eb7/32.png) [@cja7928](https://forum.inductiveautomation.com/u/cja7928)\
**Post date:** [January 4, 2014, 11:15am UTC](https://forum.inductiveautomation.com/t/bar-chart-question/7748/8 "2014-01-04T11:15:46Z")

</div>

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… 😉