Stock Date Calculation - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Stock Date Calculation

Author

Topic

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-03-29 : 05:28:29

create table #Product ( Product nvarchar(5),dOFp datetime, qut int)

insert into #Product
select 'P1','1/1/2011',100 union all
select 'P1','1/2/2011',200 union all
select 'P1','1/3/2011',50

create table #ProductStoc (Product nvarchar(5), stock int)
insert into #ProductStoc values ('P1',150)

I Need output
Product|Qty|Age
P1 |50 |28
P1 |100|56

here Qty=150(this is from #ProductStock)-50(this is from #Product/*latest date*/ every time how many stock is subtracted that value only stores ins Qty see we stored 50 so this much of stock is reduced from #Product)
until Stock is zero or empty this above calculation repeats

age column is difference between current date and dOFp(#ProductStoc)
today is 29/03/2011-1/03/2011 (which is 28 days)

this query is half some one solve this puzzle
---
select *,datediff(day,dOFp,getdate()) as Date,row_no=row_number() over (partition by product order by dOFp desc) into #t from #Product
select * from #t

Output I am getting
Product| Date | Stock | Diff_Date| row_no
P1 |2011-03-01 00:00:00.000 |50 |28 |1
P1 |2011-02-01 00:00:00.000 |200 |56 |2
P1 |2011-01-01 00:00:00.000 |100 |87 |3

but I need
Product| Date | |Stock |Diff_Date|row_no | Stock
P1 |2011-03-01 00:00:00.000 |50 |28 |1 | 50
P1 |2011-02-01 00:00:00.000 |200 |56 |2 | 100

Raghu' S

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-29 : 06:22:49

What version of SQL server are you using ?

KH
[spoiler]Time is always against us[/spoiler]

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-03-29 : 06:31:35

SQL server 2005(Developer)

"Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) "

Raghu' S

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-29 : 07:58:23

[code]
; with
StockCard as
(
select Product, dOFp, qut,
row_no = row_number() over (partition by Product order by dOFp desc)
from #Product
),
StockDate as
(
select p.Product, dOFp,
qty = case when p.stock <= c.qut then p.stock else c.qut end,
bal = p.stock - case when p.stock <= c.qut then p.stock else c.qut end,
row_no = c.row_no
from #ProductStoc p
inner join StockCard c on p.Product = c.Product
and c.row_no = 1
union all
select s.Product, c.dOFp,
qty = case when s.bal <= c.qut then s.bal else c.qut end,
bal = s.bal - case when s.bal <= c.qut then s.bal else c.qut end,
row_no = c.row_no
from StockDate s
inner join StockCard c on s.Product = c.Product
and s.row_no = c.row_no - 1

)
select Product, dOFp, Qty = qty, Age = datediff(day, dOFp, getdate())
from StockDate
where qty <> 0
order by Product, row_no
[/code]

KH
[spoiler]Time is always against us[/spoiler]

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-03-29 : 08:23:39

Thanks it working...........

Raghu' S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources