Inventory No Of Days Calculation 1

[Pampers]

Hi everyone,
I've made a little inventory database. I want to calculate how long (in days) a certain number of articles where in stock. The setup of my table is like:

tblStockMutations
------------------
StockMutID
MutationDate
ArticleCode
SoldNumber
ReceivedNumber

wich in a dataview looks like:

MutDate  Arti  Sold Recei    
-------  ----  ---- -----
10jan06  1000   5
12jan06  1000         8
15jan06  1000   1
.....

Now I want to calculate for how long the article was in stock untill next mutation (and say today is the 15th of january 2006). So then it would look like:

MutDate  Arti  Sold Recei  Days    
-------  ----  ---- -----  ----
10jan06  1000   5           2 
12jan06  1000         8     3
15jan06  1000   1           1
.....

How can you calculate this column?




Pampers
Keeping it simple can be complicated

 

[Remou]

Do you mean something like:

[tt]SELECT a.MutDate,
(Select top 1 b.[mutdate]
from table1 b
where b.arti=a.arti and b.mutdate>a.mutdate)
AS NextDate, [NextDate]-[MutDate] AS Diff, a.Arti, a.Sold, a.Recei
FROM Table1 AS a;[/tt]

 

[Pampers]

Hi Remou,
tnx for the reply. I hope it will work. I will try it out, and let you know...


Pampers
Keeping it simple can be complicated

 

[Pampers]

Hi Remou,
Works great! Thanx a lot.





Pampers
Keeping it simple can be complicated