2

I need to show inventory levels for each day in a view. I have one table that lists the current item and quantity and another table that has all of the transactions that happened to those items. I need to create a query that lists the item, date and inventory level for that date for a trend report. Here would be some sample data in the tables:

Items:

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

Here is the outcome I want from the query:

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

How would I write this query? I have pretty good knowledge of TSQL but cannot come up with a way to write this query.

Improve this question
3
  • 1
    How do you get the output from the input provided? Where is 12/31/2010 in the input? Commented Apr 28, 2011 at 11:39
  • @Quassnoi: I guess the Items table has the "running balance" at current time. He then wants the "history" from an arbitrary day (start day) in the past, from say 01/01/2011 up to now (or another arbitrary day). 12/31/2010 is the previous day of the "start day". Commented Apr 28, 2011 at 11:49
  • ypercude is correct. Items table has the quantity on hand at the current date (quantity that is in the warehouse right now). I need to list by day the quantity on hand at that day. Commented Apr 28, 2011 at 11:53

3 Answers 3

Reset to default
1

In SQL Server 2005 and above:

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

This assumes you have one transaction per item per day (this is a limitation of recursive CTE in SQL Server).

If you don't, you should add another CTE which would aggregate the transactions by day and item and use it instead of 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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I could have multiple records per day for one item. Should I aggregate the transactions by day in the UNION ALL -> COALESCE -> SELECT statement?
I am getting an error when I run it: Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
@Wili: prepend it with a semicolon: ; WITH ….
a semicolon needs to be in front of WITH. The query works awesome on my tests. I'm gonna try it on my live data. I have a feeling it's going to take a lot of time as I have about 10,000 parts and 2 million transactions.
0

A simple version using sub-select for getting quantity on hand per day.

A good / quick solution if you dont have 100k's transactions and/or a decent sql box.

Excuse the messy SQL (lunchtime coding :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
Improve this answer

Comments

0

See, the query is simple, but what is your requirement with date. I am giving a simple query which will give you the idea, and I am using a temporary table:

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
Improve this answer

3 Comments

This I have made using Cursor and it will definitely work for you with minor changes.
Change ItemNumber to your Original Column Name, ItemTransection to your Transection Table Name,Quant to your Quanity and Quantityonhand to Quantity_On_Hand Column
In case of any problem you can contact me at my email [email protected]

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.