Подсчитайте количество месяцев для каждой товарной группы
У меня есть таблица с ItemID, Qty и OrderDate
Я запрашиваю только результаты с датой заказа от следующего месяца (текущий месяц + 1) до 11 месяцев после этого.
Я пытаюсь написать запрос, который дает мне СУММУ QTY по ItemID и COUNT месяцев, которое существует для каждого ItemID.
Я могу зайти так далеко, что получил набор результатов, который выглядит так:
ITEMID Qty TMonth TYear
01.0002 392.00 1 2019
01.0002 392.00 2 2019
01.0002 392.00 3 2019
01.0002 784.00 4 2019
01.0002 784.00 5 2019
01.0002 392.00 6 2019
01.0002 784.00 7 2019
01.0002 392.00 8 2019
01.0002 392.00 9 2019
Я получаю это путем группировки по ITEMID, MONTH (OrderDate), YEAR (OrderDate), и он показывает общую сумму за каждый месяц / год. В этом примере всего 9 месяцев. тем не менее, вы увидите, что это повторяется для каждого элемента и связанного с ним месяца / года, который, если существует заказ, может быть от 1 до 11.
Мне нужен набор результатов, который выглядит так:
ITEMID Qty MnthCount
01.0002 4704.00 9
A0-B1002 3300.00 2
ZX-YT12 50.00 7
Я полагаю, что должна быть какая-то оконная функция или что-то, что я могу использовать. Есть ли способ сделать это, или мне нужно прибегнуть к подвыборкам или какому-то внешнему применению?
Что ж, это довольно просто, образцы данных ItemID, Qty и OrderDate будут выглядеть так: AX09-1 5 3/15/2018 Z7-HJk 4 3/01/2018 И так далее
Ответы 2
В будущем будет намного проще отвечать на такие вопросы, если вы включите в вопрос некоторые образцы данных (несколько десятков строк) вместе с ожидаемым результатом, основанным на этих образцах данных.
Есть много способов сделать это. Вот один вариант.
Поскольку вам нужна одна строка на каждый ItemID в результирующем наборе, мы должны сгруппировать по ItemID. Сумма Qty проста.
Чтобы отсчитать месяцы, сначала сделайте небольшую трансформацию.
DATEDIFF(month, '2001-01-01', OrderDate)
возвращает количество месяцев, прошедших с некоторой даты привязки. Вы можете выбрать любой другой 1-й день месяца вместо 2001-01-01. COUNT(DISTINCT) подсчитает все уникальные значения.
Последний вопрос:
SELECT
ITEMID
,SUM(Qty) AS TotalQty
,COUNT(DISTINCT DATEDIFF(month, '2001-01-01', OrderDate)) AS MonthCount
FROM
YourTable
WHERE
OrderDate >= @StartDate
AND OrderDate < @EndDate
GROUP BY
ITEMID
;
Вы бы хотели этого?
---drop table test
create table test
(ITEMID varchar(20),
Qty decimal(10,2),
TMonth int,
TYear int)
insert into test values
('01.0002',392.00,1,2019),
('01.0002',392.00,2,2019),
('01.0002',392.00,3,2019),
('01.0002',784.00,4,2019),
('01.0002',784.00,5,2019),
('01.0002',392.00,6,2019),
('01.0002',784.00,7,2019),
('01.0002',392.00,8,2019),
('01.0002',392.00,9,2019),
('A0-B1002',300.00,8,2019),
('A0-B1002',3000.00,9,2019),
('ZX-YT12',5.00,1,2019),
('ZX-YT12',5.00,2,2019),
('ZX-YT12',5.00,3,2019),
('ZX-YT12',5.00,4,2019),
('ZX-YT12',5.00,5,2019),
('ZX-YT12',5.00,6,2019),
('ZX-YT12',20.00,7,2019)
select ITEMID,sum(Qty) as Qty, count(TMonth) as MnthCount
from test group by ITEMID
/*
ITEMID Qty MnthCount
-------------------- --------------------------------------- -----------
01.0002 4704.00 9
A0-B1002 3300.00 2
ZX-YT12 50.00 7
*/
Как выглядят базовые данные? Не могли бы вы поделиться своим вопросом?