Adding new column based on SELECT from other table MSSQL

Question

I have the following Table in my Microsoft SQL database

Monitors

monitor_id	is_active	name
0	false	fence_monitor
1	true	bycicle_monitor
2	true	car_monitor

And one more which is the Logger

monitor	count	time_stamp
0	5	11/10/2021 15:10:00
1	5	11/20/2021 15:08:00
1	12	11/20/2021 15:11:00
1	8	11/10/2021 15:15:00

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:

monitor_name	alltime	today	current_hour
bycicle_monitor	25	8	8

Answer 1

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