#sql #sql-server
Вопрос:
Допустим, у меня есть таблица Orders , и в каждом заказе есть столбец CreatedDate и DeliveredDate столбец.
Как я могу выбрать для каждой даты в диапазоне количество, включающее все записи, в которых дата находится между CreatedDate и DeliveredDate ? Допустим, получение результатов за период с 2021-10-04 по 2021-10-09
Если Orders таблица выглядит так
ID CreatedOn DeliveredOn 1 2021-10-04 2021-10-04 2 2021-10-06 2021-10-07 3 2021-10-06 2021-10-08 4 2021-10-07 2021-10-08 5 2021-10-08 2021-10-09 Результат должен выглядеть так
Date ActiveCount 2021-10-04 1 2021-10-05 0 2021-10-06 2 2021-10-07 3 2021-10-08 3 2021-10-09 1 Таким образом, заказ 1 применяется только к подсчету 2021-10-04, но заказ 3 применяется к подсчету 2021-10-06, 2021-10-07 и 2021-10-08, потому что в этот день он доставляется.
Я не очень хорошо разбираюсь в SQL, и мне трудно понять, как это сделать. Если бы кто-нибудь мог указать мне правильное направление, я был бы очень признателен!
Комментарии:
1. Откуда берется диапазон дат в ваших результатах? У вас есть таблица дат, или вы получаете ее из пользовательского ввода или параметров хранимой процедуры? Определено в другой таблице, к которой вы присоединяетесь? Жестко прописано в запросе?
2. @squillman Это для хранимой процедуры, и диапазон дат будет определяться параметрами. Прямо сейчас у меня есть процедура, которая объединяет таблицу дат, чтобы убедиться, что я получаю количество дат, с которыми не связаны какие-либо записи. Процедура, которую я сейчас выполняю, группируется по одной дате, так что каждая запись засчитывается в один день диапазона. Мне нужно, чтобы каждая запись потенциально учитывалась более чем на один день в зависимости от двух дат в таблице.
Ответ №1:
Предполагая, что вы создадите таблицу дат в своей процедуре на основе значений входных параметров, вам просто нужно a LEFT JOIN и a COUNT с GROUP BY :
DECLARE @Dates TABLE ([Date] DATE); DECLARE @Orders TABLE (ID INT, CreatedOn DATE, DeliveredOn DATE); INSERT @Dates ([Date]) VALUES ('2021-10-04'),('2021-10-05'),('2021-10-06'), ('2021-10-07'),('2021-10-08'),('2021-10-09'); INSERT @Orders (ID, CreatedOn, DeliveredOn) VALUES (1, '2021-10-04', '2021-10-04'), (2, '2021-10-06', '2021-10-07'), (3, '2021-10-06', '2021-10-08'), (4, '2021-10-07', '2021-10-08'), (5, '2021-10-08', '2021-10-09'); SELECT d.[Date], COUNT(ID) AS ActiveCount FROM @Dates d LEFT JOIN @Orders o ON o.CreatedOn lt;= d.[Date] AND o.DeliveredOn gt;= d.[Date] GROUP BY d.[Date]; Здесь важен порядок таблиц. Вы хотите увидеть все даты в диапазоне, поэтому начинаете с этой таблицы. Он LEFT JOIN вернет все записи из этой таблицы и внесет записи из таблицы Заказов, где условие СОЕДИНЕНИЯ совпадает. Вы получите отдельную запись для каждого матча, поэтому используйте COUNT с GROUP BY , чтобы получить общее количество по дням.
Комментарии:
1. Спасибо. Это именно то, что мне было нужно.
Ответ №2:
Если у вас есть таблица, содержащая все даты, что-то в этом роде должно сработать:
SELECT d.[date] as [Date], COUNT(*) as ActiveCount FROM dates d WHERE d.[date] gt;= @startDate AND d.[date] lt;= @endDate LEFT JOIN orders o ON o.CreatedDate lt;= d.[date] and o.DeliveredDate gt;= d.[date] GROUP BY d.[date] ORDER BY [Date]