SQL to return From/To dates with gaps based on running balance
-
I have a temp table that has data like this:
I need to come up with t-SQL that will show the dates in/out for the lot like this:
Since the lot went empty on 6/12/15 (see the run bal column), I need to show 2 separate rows to allow for the gap in the date range when the lot had no qty. I've tried using MIN and MAX but I can't seem to figure out how to allow for the time gap. Any help would be greatly appreciated. I'm using SQL Server 2012.
Thanks.
-
You'll get more help if you post consumable data... like this:
CREATE TABLE LotData (Lot INT,
TranDate DATETIME,
Qty INT,
UOM CHAR(2),
RunBal INT );
GO
INSERT INTO LotData(Lot, TranDate, Qty, UOM, RunBal)
VALUES (114044, '5/27/2015 3:25 PM', 13, 'LB', 13)
, (114044, '6/12/2015 1:25 PM', -13, 'LB', 0)
, (114044, '10/20/2015 11:40 AM', 14, 'LB', 14)
, (114044, '10/30/2015 1:00 PM', 1, 'LB', 15)
, (114044, '11/13/2015 9:16 AM', -15, 'LB', 0);
Now people on here can run the create scripts and work on your problem... =)
-
Hope this helps
CREATE TABLE LotData (
Lot INT,
TranDate DATETIME,
Qty INT,
UOM CHAR(2),
RunBal INT );
GO
INSERT INTO LotData(Lot, TranDate, Qty, UOM, RunBal)
VALUES (114044, '5/27/2015 3:25 PM', 13, 'LB', 13)
, (114044, '6/12/2015 1:25 PM', -13, 'LB', 0)
, (114044, '10/20/2015 11:40 AM', 14, 'LB', 14)
, (114044, '10/30/2015 1:00 PM', 1, 'LB', 15)
, (114044, '11/13/2015 9:16 AM', -15, 'LB', 0);
Select *,ROW_NUMBER() over (partition by Lot order by TranDate) as rk into #temp from LotData
Select a.Lot,case when a.RunBal =0 then rk else rk1 end SCR_group,min(TranDate) as Min_date,max(TranDate) as max_date from #temp as a
outer apply (Select lot,MIN(rk) as rk1 from #temp as b where RunBal =0 and b.TranDate >a.TranDate and a.Lot =b.Lot
group by Lot) as b
group by a.Lot,case when a.RunBal =0 then rk else rk1 end
-
Thanks! This works.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply