SQL Query - Joining tables according to date

Ignition
1

I have two two SQL tables set up, one is for planned future events that contains a column ‘StartDate’ and the other is a record of completed events that has a ‘TimeStamp’ column.

Table 1 - SchedulerEvent
|StartDate|
2021-10-14 00:00:00.000
2021-10-07 00:00:00.000
2021-10-08 00:00:00.000
2021-10-04 00:00:00.000
2021-10-11 00:00:00.000
2021-10-21 00:00:00.000
2021-10-22 13:25:50.527
2021-10-28 00:00:00.000
2021-10-27 00:00:00.000
2021-09-27 00:00:00.000
2021-10-18 00:00:00.000
etc.

Table2 - ReportHistory
|TimeStamp|
2021-10-08 09:46:40.733
2021-10-08 09:50:14.010
2021-10-08 09:52:51.763
2021-10-08 09:54:26.293
2021-10-11 13:14:30.360
2021-10-11 13:15:48.340
2021-10-11 13:17:11.217
2021-10-11 14:27:53.780
2021-10-11 14:49:23.137
2021-10-18 09:37:04.553
etc.

I am attempting to retrieve how many events have been planned and completed for the upcoming month and the past 5 months. I’ve been attempting to join both tables so I have one table which displays Month, Planned, Completed.

The SQL querry I have is as follows: -
SELECT DISTINCT(MONTH(SE.StartDate)) Month, COUNT(MONTH(SE.StartDate)) Planned, COUNT(RH.TimeStamp) Completed

FROM SchedulerEvent SE

LEFT JOIN ReportHistory RH ON MONTH(SE.StartDate) = MONTH(RH.TimeStamp)

WHERE SE.StartDate BETWEEN DATEADD(MONTH, -5, GETDATE()) AND DATEADD(MONTH, 1, GETDATE()) AND SE.Label = ‘PM’

GROUP BY MONTH(StartDate)

The result I am trying to achieve is: -
|MONTH|PLANNED|COMPLETED|
|9 |2 |0 |
|10 |10 |10 |
|11 |6 |0 |

The result I am getting from this query is: -
|MONTH|PLANNED|COMPLETED|
|9 |2 |0 |
|10 |100 |100 |
|11 |6 |0 |

Does anyone have any idea where I’m going wrong with this query?

2

Using the sample data you posted. This was done in Postgres, but it should point you in the right direction.

The subquery count the events by date. You didn’t specify what db you’re using, so I tried to make is as generic as possible.

SELECT DISTINCT(to_char(d, 'YYYY-MM')) as month, 
       COUNT(to_char(d, 'YYYY-MM')) as planned, 
       SUM(count1) as completed
FROM 
             (SELECT se.start_date as d,
                     count(rh.timestamp) as count1
              FROM scheduler_event se
              LEFT JOIN report_history rh ON to_char(rh.timestamp, 'YYYY-MM-DD') = to_char(se.start_date, 'YYYY-MM-DD')
              -- PUT WHERE CLAUSE HERE
              GROUP BY se.start_date) subq
GROUP BY month
ORDER BY month

image

3

Thanks Jordan