# SQL Query - Joining tables according to date

**URL:** <https://forum.inductiveautomation.com/t/sql-query-joining-tables-according-to-date/52543>\
**Category:** Ignition\
**Tags:** database\
**Created:** [October 31, 2021, 7:37pm UTC](https://forum.inductiveautomation.com/t/sql-query-joining-tables-according-to-date/52543 "2021-10-31T19:37:04Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Hefin\_Price](https://avatars.discourse-cdn.com/v4/letter/h/a6a055/32.png) [@Hefin\_Price](https://forum.inductiveautomation.com/u/Hefin_Price)\
**Post date:** [October 31, 2021, 7:37pm UTC](https://forum.inductiveautomation.com/t/sql-query-joining-tables-according-to-date/52543/1 "2021-10-31T19:37:04Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [November 1, 2021, 4:51pm UTC](https://forum.inductiveautomation.com/t/sql-query-joining-tables-according-to-date/52543/2 "2021-11-01T16:51:03Z")

</div>

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.

```sql
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/0/9090720c640b7b1c0af978bf82321d3542103ce0.png)

---

<div class="post-metadata">

**Author:** ![Hefin\_Price](https://avatars.discourse-cdn.com/v4/letter/h/a6a055/32.png) [@Hefin\_Price](https://forum.inductiveautomation.com/u/Hefin_Price)\
**Post date:** [November 1, 2021, 6:37pm UTC](https://forum.inductiveautomation.com/t/sql-query-joining-tables-according-to-date/52543/3 "2021-11-01T18:37:25Z")

</div>

Thanks Jordan