I have the following Table in my Microsoft SQL database

Monitors

And one more which is the Logger

In the Logger table, the PK's are monitor and time_stamp. where monitor is a foreign key to Monitor.monitor_id

Now I need to filter the unactive monitors which I do with the following statement:

SELECT * FROM Monitors
WHERE is_active = 1

I also have 3 queries to select the total count for example in the current day / alltime or in current hour on monitor = 1:

SELECT COALESCE(SUM(count),0) AS total_day
FROM Logger
WHERE monitor = '1'
AND DATEPART(DAY, GETUTCDATE()) = DATEPART(DAY, time_stamp)

I also have for current hour but I think you get the idea. My main question is, how would I combine these 4 statements (filter is_active, filter alltime count, filter daily count, filter hourly count) in order to get a table like this:

CodePudding user response：

It looks like you just need to join the tables and use conditional aggregation

SELECT
  monitor_name  = m.name,
  alltime       = ISNULL(SUM(count), 0),
  today         = ISNULL(SUM(CASE WHEN time_stamp >= CAST(CAST(GETUTCDATE() AS date) AS datetime)
                                   AND time_stamp < CAST(DATEADD(day, 1, CAST(GETUTCDATE() AS date)) AS datetime)
                                  THEN count END), 0),
  current_hour  = ISNULL(SUM(CASE WHEN time_stamp >= DATEADD(hour, DATEDIFF(hour, '20000101', GETUTCDATE()), '20000101')
                                   AND time_stamp <  DATEADD(hour, DATEDIFF(hour, '20000101', GETUTCDATE())   1, '20000101')
                                  THEN count END), 0)
FROM Monitors m
JOIN Logger l ON l.monitor = m.monitor_id
WHERE m.is_active = 1
GROUP BY
  m.name;

db<>fiddle