I am trying to get data (T_Stamp, Destination, Weight, Line) from two tables using variable time intervals and a destination selection. This is going into an Ignition SCADA. My SQL code below works for most cases except when there are entries into both tables with the same timestamp. In those cases it shows only the data from table A. This is giving me results with the incorrect destination. I understand that the COALESCE function is returning the first Non-null value, but I don't know how else to write this logic.
SELECT COALESCE(a.t_stamp, b.t_Stamp) T_Stamp, COALESCE(a.Destination, b.Destination) Destination, COALESCE(a.A_Weight, b.B_Weight) Weight, CASE WHEN a.Ham_Line_A_Counts_ndx > 0 THEN 'A' ELSE 'B' END AS Line
FROM Ham_Line_A_Counts as a FULL OUTER JOIN
b_Counts AS b
ON a.t_Stamp=b.t_Stamp
WHERE (a.t_Stamp Between '{Root Container.Popup Calendar.date}' AND '{Root Container.Popup Calendar 1.date}' AND a.Destination = {Root Container.Dropdown 1.selectedValue}) OR (b.t_Stamp Between '{Root Container.Popup Calendar.date}' AND '{Root Container.Popup Calendar 1.date}' AND b.Destination = {Root Container.Dropdown 1.selectedValue})
ORDER BY T_Stamp DESC
Expected results:
t_stamp | Destination | Weight | Line |
---|---|---|---|
10:05:01 | 1 | 30.01 | A |
10:05:05 | 1 | 25.11 | B |
10:05:07 | 1 | 26.32 | B |
Actual Results:
t_stamp | Destination | Weight | Line |
---|---|---|---|
10:05:01 | 1 | 30.01 | A |
10:05:05 | 1 | 25.11 | B |
10:05:07 | 2 | 25.46 | A |
Sample Data Table A: | t_stamp | Destination | A_Weight | | -------- | ----------- | -------- | | 10:05:01 | 1 | 30.01 | | 10:05:07 | 2 | 32.32 |
Table B: | t_stamp | Destination | B_Weight | | -------- | ----------- | -------- | | 10:05:03 | 1 | 24.01 | | 10:05:07 | 1 | 26.46 |
I suspect what you want is something like the following, which uses a UNION
instead of a JOIN
.
SELECT a.t_stamp T_Stamp, a.Destination Destination, a.A_Weight Weight, 'A' Line
FROM Ham_Line_A_Counts a
WHERE a.t_Stamp Between '{Root Container.Popup Calendar.date}' AND '{Root Container.Popup Calendar 1.date}' AND a.Destination = {Root Container.Dropdown 1.selectedValue}
UNION ALL
SELECT b.t_stamp T_Stamp, b.Destination Destination, b.B_Weight Weight, 'B' Line
FROM b_Counts b
WHERE b.t_Stamp Between '{Root Container.Popup Calendar.date}' AND '{Root Container.Popup Calendar 1.date}' AND b.Destination = {Root Container.Dropdown 1.selectedValue})
ORDER BY T_Stamp DESC
This gets the results from a
with the correct destination and timestamps, gets the results from b
with the correct destination and timestamps, then sorts them all together by timestamp. So if a timestamp is in a
and b
, both rows are returned one after the other. I used UNION ALL
rather than just UNION
since the hardcoded 'A'/'B' in the Line
column means there won't be duplicates and the UNION ALL
could be more efficient.