Making multiple SQL queries into one

General Discussion
1

I have multiple SQL Queries that I am trying to combine into one query for a report. They work just fine independently but when I try to combine into one query, I get faults and I do not know how to correct this as I am really green.
SELECT A.BM01_Infeed AS ‘Infeed’,
A.t_stamp AS ‘Start Time’, B.t_stamp ‘End Time’, TIMESTAMPDIFF(Second,A.t_stamp,B.t_stamp) AS ‘InfeedSeconds’
FROM didata.BM01_Activity A
INNER JOIN didata.BM01_Activity B ON B.BM01_Activity_ndx = (A.BM01_Activity_ndx + 1)
WHERE A.BM01_Infeed = 1 AND
A.t_stamp >= ‘{Root Container.CalendarStart.formattedDate}’ AND A.t_stamp <= ‘{Root Container.CalendarEnd.formattedDate}’
ORDER BY A.BM01_Activity_ndx ASC;

SELECT A.BM01_Run AS ‘Run’,
A.t_stamp AS ‘Start Time’, B.t_stamp ‘End Time’, TIMESTAMPDIFF(Second,A.t_stamp,B.t_stamp) AS ‘RunSeconds’
FROM didata.BM01_Activity A
INNER JOIN didata.BM01_Activity B ON B.BM01_Activity_ndx = (A.BM01_Activity_ndx + 1)
WHERE A.BM01_Run = 1 AND
A.t_stamp >= ‘{Root Container.CalendarStart.formattedDate}’ AND A.t_stamp <= ‘{Root Container.CalendarEnd.formattedDate}’
ORDER BY A.BM01_Activity_ndx ASC;

SELECT A.BM01_Down AS ‘Down’,
A.t_stamp AS ‘Start Time’, B.t_stamp ‘End Time’, TIMESTAMPDIFF(Second,A.t_stamp,B.t_stamp) AS ‘DownSeconds’
FROM didata.BM01_Activity A
INNER JOIN didata.BM01_Activity B ON B.BM01_Activity_ndx = (A.BM01_Activity_ndx + 1)
WHERE A.BM01_Down = 1 AND
A.t_stamp >= ‘{Root Container.CalendarStart.formattedDate}’ AND A.t_stamp <= ‘{Root Container.CalendarEnd.formattedDate}’
ORDER BY A.BM01_Activity_ndx ASC;
SELECT A.BM01_Discharge AS ‘Discharge’,
A.t_stamp AS ‘Start Time’, B.t_stamp ‘End Time’, TIMESTAMPDIFF(Second,A.t_stamp,B.t_stamp) AS ‘DischargeSeconds’
FROM didata.BM01_Activity A
INNER JOIN didata.BM01_Activity B ON B.BM01_Activity_ndx = (A.BM01_Activity_ndx + 1)
WHERE A.BM01_Discharge = 1 AND
A.t_stamp >= ‘{Root Container.CalendarStart.formattedDate}’ AND A.t_stamp <= ‘{Root Container.CalendarEnd.formattedDate}’
ORDER BY A.BM01_Activity_ndx ASC;

2

If I am reading this correctly, it looks like all your joins and where clauses are identical right? I would just put every column you need in the SELECT clause, leave the rest alone, and try that. What error if any are you getting when you try to do that?

3

I made a copy/paste error. I have corrected. Can you look over now and see if you think it is the same problem. I am REALLY new and trying to understand all this. No formal education, just reading and .learning

4

Ok I see 4 differnet where clauses now. Thats fine, you CAN have multiple SQL queries to provide your data to the report and it seems like that may be what you want. If you combine the WHERE clauses of each queries with AND, you are getting the intersection of all of them, only items that would be A.BM01_Infeed = 1 AND A.BM01_Run = 1 AND A.BM01_Down = 1 AND A.BM01_Discharge = 1, which is probably not what you need, and semantically it seems like Run and Down would cancel each other out to make your result set empty.

You could combine it with OR but your data would not be nicely organized. I would just make 4 separate SQL queries as your Data Sources if I was you.

If you need refreshing, I would highly recommend going to https://www.inductiveuniversity.com/ and going to the Reporting Module courses.

5

It looks like you are trying to make your own version of SQL LEAD or LAG to do uptime/downtime calculations. Consider using those directly (no joining required).

6

Thanks for the advice. I’ll give this all a try.
Refreshing is a kind word for what I need. One of those guys who jumped in the middle of the lake and trying to learn to swim without any prior knowledge.

7

Yes. Trying to show machine Uptime/Downtime/Standby-conditions. Figured making a report first then will try making a graph… (that’ll take me forever). Never heard of SQL LEAD or LAG so looks like some more reading for me there also. Appreciate the insight.

1 Like
8

Offhand it looks like UNION or UNION ALL between statements is all you need.

9

Thanks martinel3746. I just ended up using the 4 separate statements as suggested by bkarabinchak. although a lot more cumbersome, it works and that’s really good for a newbie like myself. I did make notes of your fix and will try it if/when I have to make another similar report.
Thanks all!