MSSQL COALESCE function with multiple non null values

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 |

Answer

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.