Tricky query, get inventory count by date range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Tricky query, get inventory count by date range

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-03-08 : 18:50:15

Hi,

My tables:

Product
- productID
- name
- price

Inventory
- productID
- stockCount
- timestamp

So each day the Inventory table has a new row for each productID with its stock count.

How can I create a report for the total products sold from one day to another? Or from what a dateStart from a dateEnd (i.e. a range)

Example:

ProductID StockCount TimeStamp
1 10 2008/03/07
1 7 2008/03/08

So you can see that 3 products were sold in the last day.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-08 : 19:11:46

these kinds of running totals are best done on the client, really.
AFAIK all reporting tools have a running total function.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 19:21:24

[code]
Create Table #Product(
productID int not null,
[name] char(1) not null,
price numeric(15,2) not null)

Create Table #Inventory (
productID int not null,
stockCount int not null,
mydate datetime not null)

--prepare sample data
Insert Into #Inventory (ProductID, StockCount,myDate)
Select 1, 10, '2008/03/07' UNION ALL
Select 1, 7 , '2008/03/08' UNION ALL
Select 2, 10, '2008/03/07' UNION ALL
Select 2, 15, '2008/03/06' UNION ALL
Select 3, 20,'2008/3/5' UNION ALL
Select 2, 18, '2008/3/6'
Insert Into #Product (ProductID, [name],price)
Select 1, 'A',4.75 UNION ALL
Select 2, 'B',29.95 UNION ALL
Select 3, 'C', 19.95

;WITH myTable As
(
Select ROW_NUMBER() over(order by b.productID,myDate desc) as RowNum,
b.ProductID,
b.StockCount,
a.[Name],
a.[Price],
b.myDate
FROM #inventory b inner join #product a on b.ProductID = a.ProductID
)

Select y.ProductID,
y.[Name],
y.mydate as startday,
t.mydate as endday,
y.stockcount as startcount,
isnull(t.stockcount,y.stockcount) as endcount,
y.StockCount-isNull(t.StockCount,0) as NetChange,
(y.StockCount-isNull(t.StockCount,0)* y.Price) as dollarsreduced,
IsNull(t.StockCount,y.StockCount) * isNull(t.Price,y.Price) as EndDayInventoryValue
FROM myTable y inner join mytable t
on y.RowNum = (t.RowNum+1)
and y.ProductID = t.ProductID

----to add a dateparameter?

Declare @startday datetime
SET @startday = '20080307'

;WITH myTable As
(
Select ROW_NUMBER() over(order by b.productID,myDate desc) as RowNum,
b.ProductID,
b.StockCount,
a.[Name],
a.[Price],
b.myDate
FROM #inventory b inner join #product a on b.ProductID = a.ProductID
)

Select y.ProductID,
y.[Name],
y.mydate as startday,
t.mydate as endday,
y.stockcount as startcount,
isnull(t.stockcount,y.stockcount) as endcount,
y.StockCount-isNull(t.StockCount,0) as NetChange,
(y.StockCount-isNull(t.StockCount,0)* y.Price) as dollarsreduced,
IsNull(t.StockCount,y.StockCount) * isNull(t.Price,y.Price) as EndDayInventoryValue
FROM myTable y inner join mytable t
on y.RowNum = (t.RowNum+1)
and y.ProductID = t.ProductID
WHERE y.mydate = @startday

Drop Table #product
Drop Table #inventory
[/code]

Couple of options there that may help.

Poor planning on your part does not constitute an emergency on my part.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-08 : 22:29:26

Don't you have a sales transaction table to get the sales total from?

The change in inventory level from one day to the next can be due to many reasons besides sales, so how can you get sales numbers from it?

CODO ERGO SUM

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 22:47:15

I dont think he is looking for sales really, just the change in inventory from one day to the next.

Regardless, you are correct MVJ...if you add up sales from a Sales table, you should be able to derive decrease in inventory from a starting point.

this post is also from the OP
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98674

wonder what he has cookin.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources