SQL view from unrelated tables

Question

I have a database with the following tables :

PositionMessages

DeviceID | MsgDate | MeasureDate | MsgType
---------+---------+-------------+--------
    1000 | 15/10/20|    12/10/20 |  1
    1000 | 15/10/20|    15/10/20 |  1
    1000 | 16/10/20|    16/10/20 |  2
    1000 | 17/10/20|    12/10/20 |  3
    1001 | 15/10/20|    12/10/20 |  1
    1001 | 15/10/20|    15/10/20 |  1
    1001 | 15/10/20|    15/10/20 |  3
    1002 | 16/10/20|    12/10/20 |  2

TemperatureMessages

DeviceID | MsgDate | MeasureDate | MsgType
---------+---------+-------------+--------
    1000 | 17/10/20|    12/10/20 |  4
    1000 | 17/10/20|    17/10/20 |  4
    1000 | 18/10/20|    17/10/20 |  5
    1000 | 14/10/20|    14/10/20 |  4
    1001 | 15/10/20|    15/10/20 |  4
    1001 | 16/10/20|    15/10/20 |  5
    1001 | 18/10/20|    16/10/20 |  5
    1002 | 18/10/20|    18/10/20 |  5

I have about 4 similar tables in total. Each device can send types of messages that will go in one of these 4 tables and populate them.

What I am trying to achieve is to get a SQL view (in order to supply a Power BI report) with a table that would look like :

DeviceID | MsgType | Delayed | Quantity
---------+---------+---------+--------
    1000 |    1    |  Yes    |   250
    1000 |    1    |  No     |   14
    1000 |    2    |  Yes    |   421
    1000 |    2    |  No     |   51
    1000 |    3    |  Yes    |   320
    1000 |    3    |  No     |   84
    1000 |    4    |  Yes    |   112
    1000 |    4    |  No     |   54
    1001 |    1    |  Yes    |   244
    1001 |    1    |  No     |   36

In this table, I would like to get, for each device, the count of messages for each type, and I also want to differentiate the amount of messages that were sent live from the ones that were sent after some delay (no matter what the delay is, as long as it is different than zero).

So far, I have been able to create such a table only based on one original table, with this request:

SELECT DeviceID, MsgType, DATEDIFF(minute, MeasureDate, MsgDate) AS [Delay], COUNT(*) as Quantity FROM PositionMessages
    WHERE MeasureDate = MsgDate
    GROUP BY DeviceID, MsgType, DATEDIFF(minute, MeasureDate, MsgDate)
    ORDER BY DeviceID ASC

(that would return only "live messages", whose broadcast dates is equal to the measure date).

This gives me the table :

DeviceID | MsgType | Delayed | Quantity
---------+---------+---------+--------
    1000 |    1    |  No     |   14
    1000 |    2    |  No     |   51
    1000 |    3    |  No     |   84
    1001 |    1    |  No     |   36
    1001 |    2    |  No     |   28

I have another request for delayed messages, as if I group them by time difference they do not gather properly :

SELECT DeviceID, MsgType, 1 AS [Delay], COUNT(*) as Total FROM PositionMessages
    WHERE MeasureDate <> MsgDate
    GROUP BY DeviceID, MsgType
    ORDER BY DeviceID ASC

This lead to the table :

DeviceID | MsgType | Delayed | Quantity
---------+---------+---------+--------
    1000 |    1    |  Yes    |   250
    1000 |    2    |  Yes    |   421
    1000 |    3    |  Yes    |   320
    1001 |    1    |  Yes    |   112
    1001 |    2    |  Yes    |   244

Would you know if this is a correct way to proceed, and how I can put the results together in a single table (along with results from the other tables)?

When I try to include UNION ALL or different JOIN types, I get the error "Incorrect syntax near the keyword 'UNION'". I am also unsure about how to include this in a view, that would be called daily through a SQL agent.

Thanks a lot.

Answer 1

This seems like union all and aggregation:

select DeviceID, MsgType,
       (case when MsgDate = MeasureDate then 'No' else 'Yes' end) as delayed,
       count(*)
from ((select DeviceID, MsgDate, MeasureDate, MsgType
       from PositionMessages
      ) union all
      (select DeviceID, MsgDate, MeasureDate, MsgType
       from LocationMessages
      )
     ) m
group by DeviceID,
         (case when MsgDate = MeasureDate then 'No' else 'Yes' end),
         MsgType;

  

Answer 2

To combine data from multiple tables, you are correct that you need to UNION them.

To UNION them, they need the exact same column layout and column types between them all. Putting it in a view works wonderfully because then you can query it easily, and do your WHERE and what-have-you.

Whether this is the "correct" way to proceed is too broad of a question for this board. Only you know your business requirement. If you want help with your specific queries, we're happy to help -- but you have to tell us what it should look like, vs. what it currently looks like. I have no idea how to help you with your second query because I don't know how you want it to look like. Are you trying to gather the total delay by DeviceID?

SELECT DeviceID, MsgType, DATEDIFF(minute, MeasureDate, MsgDate) AS [Delay], COUNT(*) as Quantity 
FROM A
WHERE MeasureDate = MsgDate
GROUP BY DeviceID, MsgType, DATEDIFF(minute, MeasureDate, MsgDate) 
/* Note: your MeasureDate = MsgDate, so doing DATEDIFF is really superfluous unless there's a time component we don't see in your sample */

UNION ALL 

SELECT DeviceID, MsgType, SUM(DATEDIFF(minute, MeasureDate, MsgDate)) AS [Delay], COUNT(1) as Total 
FROM A
WHERE MeasureDate <> MsgDate
GROUP BY DeviceID, MsgType


ORDER BY DeviceID ASC

Demo here to play with. Please feel free to make your own example, and come back to share it vs your expected resultset so we can be more helpful.