#sql #sql-server #tsql
#sql #sql-сервер #tsql
Вопрос:
Мне нужно показывать уровни запасов за каждый день в представлении. У меня есть одна таблица, в которой перечислены текущий товар и количество, и другая таблица, в которой указаны все транзакции, которые произошли с этими товарами. Мне нужно создать запрос, в котором перечислены товар, дата и уровень запасов на эту дату для отчета о тенденциях. Здесь будут некоторые примеры данных в таблицах:
Товары:
ItemNumber QuantityOnHand
---------- --------------
B34233 25.0
B34234 10.0
ItemTransactions:
TransDate ItemNumber Quantity
----------- ---------- --------
1/1/2011 B34233 10.0
1/2/2011 B34234 -15.0
1/2/2011 B34233 -5.0
1/4/2011 B34234 -10.0
Вот результат, который я хочу получить от запроса:
Date ItemNumber Quantity
---- ---------- --------
12/31/2010 B34233 20.0
12/31/2010 B34234 35.0
1/1/2011 B34233 30.0
1/1/2011 B34234 35.0
1/2/2011 B34233 25.0
1/2/2011 B34234 20.0
1/3/2011 B34233 25.0
1/3/2011 B34234 20.0
1/4/2011 B34233 25.0
1/4/2011 B34234 10.0
Как бы я написал этот запрос? Я довольно хорошо разбираюсь в TSQL, но не могу придумать способ написания этого запроса.
Комментарии:
1. Как вы получаете выходные данные из предоставленных входных данных? Где находится
12/31/2010во входных данных?2. @Quassnoi: Я предполагаю, что в
Itemsтаблице есть «текущий баланс» на текущий момент времени. Затем он хочет получить «историю» с произвольного дня (start day) в прошлом, скажем, с01/01/2011по настоящее время (или другого произвольного дня).12/31/2010является предыдущим днем «начального дня».3. ypercude является правильным. В таблице товаров указано количество в наличии на текущую дату (количество, которое находится на складе прямо сейчас). Мне нужно перечислить по дням количество, имеющееся в наличии на этот день.
Ответ №1:
В SQL Server 2005 и выше:
WITH dates (itemNumber, quantity, currentDate, minDate) AS
(
SELECT itemNumber, CAST(quantityOnHand AS DECIMAL(20, 2)), it.*
FROM items i
CROSS APPLY
(
SELECT MAX(transDate) AS currentDate,
MIN(transDate) AS minDate
FROM itemTransactions it
) it
UNION ALL
SELECT d.itemNumber,
CAST
(
d.quantity -
COALESCE(
(
SELECT it.quantity
FROM itemTransactions it
WHERE it.transDate = d.currentDate
AND it.itemNumber = d.itemNumber
), 0) AS DECIMAL(20, 2)),
DATEADD(d, -1, currentDate),
minDate
FROM dates d
WHERE currentDate >= minDate
)
SELECT currentDate, itemNumber, quantity
FROM dates
ORDER BY
currentDate, itemNumber
Это предполагает, что у вас есть одна транзакция на элемент в день (это ограничение рекурсивного CTE ввода SQL Server ).
Если вы этого не сделаете, вам следует добавить другой, CTE который агрегировал бы транзакции по дням и элементам и использовал бы его вместо items :
WITH itGrouped (transDate, itemNumber, quantity) AS
(
SELECT transDate, itemNumber, SUM(quantity)
FROM itemTransactions
GROUP BY
transDate, itemNumber
),
dates (itemNumber, quantity, currentDate, minDate) AS
(
SELECT itemNumber, CAST(quantityOnHand AS DECIMAL(20, 2)), it.*
FROM items i
CROSS APPLY
(
SELECT MAX(transDate) AS currentDate,
MIN(transDate) AS minDate
FROM itGrouped it
) it
UNION ALL
SELECT d.itemNumber,
CAST
(
d.quantity -
COALESCE(
(
SELECT it.quantity
FROM itGrouped it
WHERE it.transDate = d.currentDate
AND it.itemNumber = d.itemNumber
), 0) AS DECIMAL(20, 2)),
DATEADD(d, -1, currentDate),
minDate
FROM dates d
WHERE currentDate >= minDate
)
SELECT currentDate, itemNumber, quantity
FROM dates
ORDER BY
currentDate, itemNumber
Комментарии:
1. У меня могло бы быть несколько записей в день для одного элемента. Должен ли я агрегировать транзакции по дням в инструкции UNION ALL -> COALESCE -> SELECT?
2. При запуске я получаю сообщение об ошибке: неправильный синтаксис рядом с ключевым словом ‘with’. Если этот оператор является обычным табличным выражением или предложением xmlnamespaces, предыдущий оператор должен заканчиваться точкой с запятой.
3. @Wili: добавь к нему точку с запятой:
; WITH ….4. точка с запятой должна стоять перед WITH . Запрос отлично работает в моих тестах. Я собираюсь попробовать это на своих текущих данных. У меня такое чувство, что это займет много времени, поскольку у меня около 10 000 частей и 2 миллиона транзакций.
Ответ №2:
Простая версия, использующая дополнительный выбор для получения количества в наличии в день.
Хорошее / быстрое решение, если у вас нет транзакций 100k и / или приличного sql-блока.
Извините за беспорядочный SQL (кодирование в обеденное время: P)
CREATE TABLE #transactions (ID INT, DTE DATETIME, PROD VARCHAR(25), QTY INT )
CREATE TABLE #products (ID VARCHAR(25))
CREATE TABLE #dates (DTE DATETIME)
-- create some dates - you would do this dynamically
INSERT INTO #dates values (convert(datetime, '01/01/2011', 103))
INSERT INTO #dates values (convert(datetime, '02/01/2011', 103))
INSERT INTO #dates values (convert(datetime, '03/01/2011', 103))
-- create some products - you would get these from where-ever they live
INSERT INTO #products values ('A')
INSERT INTO #products values ('B')
-- create some transactions - you would get these from where-ever they live
INSERT INTO #transactions values (1, convert(datetime, '01/01/2011', 103), 'A', 25)
INSERT INTO #transactions values (2, convert(datetime, '01/01/2011', 103), 'A', -5)
INSERT INTO #transactions values (3, convert(datetime, '02/01/2011', 103), 'A', 60)
INSERT INTO #transactions values (4, convert(datetime, '02/01/2011', 103), 'A', -15)
INSERT INTO #transactions values (5, convert(datetime, '03/01/2011', 103), 'A', 100)
INSERT INTO #transactions values (6, convert(datetime, '03/01/2011', 103), 'A', -20)
INSERT INTO #transactions values (7, convert(datetime, '01/01/2011', 103), 'B', 10)
INSERT INTO #transactions values (8, convert(datetime, '01/01/2011', 103), 'B', 5)
INSERT INTO #transactions values (9, convert(datetime, '02/01/2011', 103), 'B', -30)
INSERT INTO #transactions values (1, convert(datetime, '02/01/2011', 103), 'B', 50)
INSERT INTO #transactions values (11, convert(datetime, '03/01/2011', 103), 'B', 10)
INSERT INTO #transactions values (12, convert(datetime, '03/01/2011', 103), 'B', 200)
-- Join dates and transactions - Do a sub select from 'begining of time' to get qty on hand per day
SELECT CONVERT(VARCHAR(25), a.DTE, 103), b.id, (SELECT sum(qty) from #transactions c where b.id = c.prod and c.DTE <= a.DTE)
FROM #dates a, #products b
-- One benefit to this approach means you can genereate qty_on_hand per days were no transactions have occured (if you needed this)
DROP TABLE #transactions
DROP TABLE #products
DROP TABLE #dates
Ответ №3:
Смотрите, запрос прост, но каковы ваши требования к дате. Я даю простой запрос, который даст вам представление, и я использую временную таблицу:
Create Table #Transection
(
ID int Identity(1,1) not null,Item_Number varchar(20),Transection_Date datetime,Quantity_Available decimal(10,2)
)
insert into #Transection (Item_Number) (Select ItemNumber from Items)
Declare @Product varchar(20),@Next_Product Cursor, @Item varchar(20),@Quantity decimal(10,2)
set @Next_Product= CURSOR FOR Select Item_Number from #Transection open @Next_Product Fetch Next from @Next_Product into @Product
While @@Fetch_Status=0
Begin
set @Item=(Select ItemNumber from ItemTransection where ItemNumber= @Product)
if is not null
Begin
Set @Quantity=(Select top 1 Items.Quantityonhand -ItemsTrasection.Quant as Quantity from Items Join
ItemsTrasection on ItemsTrasection.ItemNumber=Items.ItemNumber where ItemsTrasection.ItemNumber=@Item order by ItemsTrasection.TransDate desc)
update #Transection set Transection_Date=(Select top 1 TransDate from ItemsTrasection where ItemNumber=@Item order by TransDate desc), Quantity=@Quantity
where Item_Number=@Item
End
Else
Begin
update #Transection set Transection_Date=(Select top 1 TransDate from Items where ItemNumber=@Item ), Quantity=(Select top 1 from Items where ItemNumber=@Item )
where Item_Number=@Item
End
End
Select * from #Transection
Комментарии:
1. Это я сделал с помощью Cursor, и это определенно будет работать для вас с небольшими изменениями.
2. Измените ItemNumber на ваше исходное имя столбца, ItemTransection — на ваше имя таблицы сечения, Quant — на ваше количество, а Quantityonhand — на столбец Quantity_On_Hand
3. В случае возникновения какой-либо проблемы вы можете связаться со мной по моей электронной почте gaurav_goel209@yahoo.co.in