0

I currently have a table logging inventory numbers for my entire facility every 30 minutes. I need to be able to show fluctuations for part numbers based upon the difference between their last recorded value. So for example for part "MAA-0001" if it had 100 at 11am and 150 at 1130 am I need it to show "+50" and then if it went to 130 at 1200 I would need it to show for that row "-20". I just want the fluctuations based upon it's last recorded logged time instead of a rolling cumulative amount.

Code to set up table for values:

create table #temptablei (partnum nvarchar(50), loc nvarchar(50), logtime 
smalldatetime, amt float)

insert into #temptablei (partnum,loc,logtime,amt)
    VALUES
    ('maa-0001','whs1','2018-11-29 11:00:00',288),
    ('maa-0001','whs1','2018-11-29 11:30:00',253),
    ('maa-0001','whs1','2018-11-29 12:00:00',294),
    ('maa-0001','whs1','2018-11-29 12:30:00',181),
    ('maa-0001','whs1','2018-11-29 13:00:00',200),
    ('maa-0001','whs1','2018-11-29 13:30:00',218)

select * from #temptablei

I've tried using both LAG and LEAD commands from other examples, but it just appears to be giving a rolling cumulative amount. I need something that will just show the latest recorded value and the difference between it and the last recorded value by part number. Any help would be greatly appreciated!

Dale K
  • 25,246
  • 15
  • 42
  • 71
Dave Holt
  • 111
  • 7

1 Answers1

2

I would expect the answer to be:

select t.*,
       (amt - lag(amt) over (partition by partnum, loc order by logtime)) as diff
from #temptablei t;

Here is a db<>fiddle.

To show the last two values, you would simply use top (2):

select top (2) t.*,
       (amt - lag(amt) over (partition by partnum, loc order by logtime)) as diff
from #temptablei t
order by logtime desc;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786