Блог

Главная - Вопросы по программированию - Транзакции соединения SQL в диапазоне дат

Транзакции соединения SQL в диапазоне дат

#sql #sql-server #tsql #aggregate

Вопрос:

В SQL Server 2014 я работаю с двумя таблицами, an EMPLOYEE и SALES таблицей:

 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

Мне нужно написать запрос, который выведет все строки/столбцы из EMPLOYEE таблицы со столбцом, показывающим их (агрегированные) продажи, но ТОЛЬКО продажи, которые были запущены в течение 30 дней с даты найма.

Этот запрос работает, но будет включать ВСЕ продажи, завершенные до настоящего времени:

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

В этой другой попытке HIRE_DATE не распознается в подзапросе.

 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

Как я могу переписать этот запрос, чтобы во второй таблице были указаны совокупные продажи, ТОЛЬКО если продажа состоялась через 30 дней после даты найма?

Желаемый результат:

 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

Комментарии:

1. Пожалуйста, покажите свои желаемые результаты, и если вы хотите, чтобы людям было легко отвечать, предоставьте свои примеры данных в формате DDL DML.

Ответ №1:

 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

Не могли бы вы, пожалуйста, попробовать, подходит ли вам вышеперечисленное

Комментарии:

1. Ух ты! Это полностью работает! Спасибо!

Метки: