SQL Joining transactions on Date Range-CodePudding

In SQL Server 2014, I'm working with two tables, an EMPLOYEE and a SALES table:

EMPID EMPNAME    HIRE_DATE
---------------------------
1234  JOHN SMITH 2021-05-01
1235  JANE DOE   2021-08-05
1236  JANE SMITH 2021-07-31

EMPID SALE_DATE  PRODUCT
-------------------------------------
1234  2021-05-05 VPN
1234  2021-05-10 VPN Basic
1234  2021-07-15 Cloud Storage Bronze
1234  2021-07-05 Cloud Storage Gold
1235  2021-10-01 Antivirus

I need to write a query that will produce all rows/columns from the EMPLOYEE table, with a column showing their (aggregated) sales, but ONLY sales that were triggered within 30 days of the hire date.

This query works, but will pull in ALL sales completed until present:

SELECT EMP.*, SALES_30_DAYS
FROM EMP 
LEFT JOIN 
    (SELECT EMPID, COUNT(*) 
     FROM SALES_30_DAYS 
     GROUP BY EMPID) ON EMP.EMPID = SALES.EMPID

In this other attempt, HIRE_DATE is not recognized in the sub-query.

SELECT EMP.*, SALES_30_DAYS 
FROM EMP 
LEFT JOIN 
    (SELECT EMPID, COUNT(*) SALES_30_DAYS 
     FROM SALES 
     WHERE DATEDIFF(DD, HIRE_DATE, SALE_DATE) < 30 
     GROUP BY EMPID) ON EMP.EMPID=  SALES.EMPID

How can I re-write this query, so that the second table will provide the aggregated sales ONLY if the sale took place up to 30 days after the hire date?

Desired outcome:

EMPID EMPNAME    HIRE_DATE  SALES_30_DAYS
-----------------------------------------
1234  JOHN SMITH 2021-05-01 2
1235  JANE DOE   2021-08-05 1
1236  JANE SMITH 2021-07-31 NULL

CodePudding user response：

WITH EMPLOYEES(EMPID, EMPNAME, HIRE_DATE)AS
(
   SELECT 1234,  'JOHN SMITH', '2021-05-01' UNION ALL
   SELECT 1235,  'JANE DOE'  , '2021-08-05' UNION ALL
   SELECT 1236,  'JANE SMITH' ,'2021-07-31'  

),
SALES(EMPID, SALE_DATE,  PRODUCT) AS
(

 SELECT 1234,  '2021-05-05' ,'VPN' UNION ALL
 SELECT 1234 , '2021-05-10' ,'VPN Basic' UNION ALL
 SELECT 1234 , '2021-07-15' ,'Cloud Storage Bronze' UNION ALL
 SELECT 1234 , '2021-07-05' ,'Cloud Storage Gold' UNION ALL
 SELECT 1235 , '2021-10-01', 'Antivirus' 
)
SELECT E.EMPID,E.EMPNAME,E.HIRE_DATE,SALE_QUERY.CNTT
FROM EMPLOYEES E
OUTER APPLY
(
  SELECT COUNT(*)CNTT
    FROM SALES AS S WHERE E.EMPID=S.EMPID AND
      S.SALE_DATE BETWEEN E.HIRE_DATE AND DATEADD(DD,30,E.HIRE_DATE)
)SALE_QUERY

Could you please try if the above is suitable for you