SQL Code to filter based on dates for each unique record-CodePudding

I have a table that looks something like this, lets call it TABLE_1:

| Customer_ID | Date_Start | Date_End   |
| c1          | 2017-09-24 | 2017-03-24 |
| c2          | 2017-03-28 | 2017-09-28 |
| c3          | 2018-12-22 | 2019-06-22 |
| c4          | 2017-04-26 | 2017-10-26 |

There is another table called TABLE_2 which contains records of all customer IDs for multiple dates. Is there a way in which I can get row using SQL query from TABLE_2 such that I can count how many times each customer ID occurs in the TABLE_2 within the ranges of Date_Start and Date_End based on the above TABLE_1?

Here there is a filter on each customer ID.

CodePudding user response：

This might do it, if I understand the question:

SELECT T1.Customer_ID, T1.Date_Start, T1.Date_End, COUNT(*)
FROM Table1 AS T1
LEFT JOIN Table2 AS T2 
   on T1.Customer_ID=T2.Customer_ID 
      and T2.cust_date BETWEEN T1.Date_Start and T2.Date_Start

CodePudding user response：

select distinct Table1.customer_ID count(Table_2.customer_ID)

from TABLE_1 left join TABLE_2 on TABLE_1_customer_ID = TABLE_2_customer_ID

below will be helpful

How to count occurrences of a column value efficiently in SQL?

CodePudding user response：

maybe a simple left join will help

SELECT A.Customer_ID, COUNT(B.Customer_ID ) as count_of_records
FROM TABLE_1 A LEFT JOIN
TABLE_2 B 
 ON A.Customer_ID =B.Customer_ID 
WHERE B.datecol BETWEEN A.Date_Start AND A.Date_End
GROUP BY A.Customer_ID