June 28, 2012 at 3:25 pm
I'm sure this is probably elementary, but for some reason I can't wrap my head around it.
I have a stored procedure that has two params, @begin_date and @end_date. For each day between the two params I need to pull the date in question, we can call this Reporting_Date, and hit an inventory table and pull the most recent date that is less than each day between the two and it's corresponding volume
Given Inventory_Table:
Close_Date, Volume
6/1/2012, 14
6/2/2012, 13
6/4/2012, 10
6/6/2012, 14
6/8/2012, 13
6/9/2012, 10
Given params:
@begin_date = 6/2/2012
@end_date = 6/11/2012
What I need to return is:
Reporting_Date, Close_Date, Volume
6/2/2012, 6/1/2012, 14
6/3/2012, 6/2/2012, 13
6/4/2012, 6/2/2012, 13
6/5/2012, 6/4/2012, 10
6/6/2012, 6/4/2012, 10
6/7/2012, 6/6/2012, 14
6/8/2012, 6/6/2012, 14
6/9/2012, 6/8/2012, 13
6/10/2012, 6/9/2012, 10
6/11/2012, 6/9/2012, 10
This seems so simple, but it is beating me up pretty badly.
June 28, 2012 at 4:40 pm
There may be other ways to this, but based on what you provided this does work.
create table #TestData (
CloseDate date,
Volume int
);
go
insert into #TestData (CloseDate, Volume)
values ('20120601',14),
('20120602',13),
('20120604',10),
('20120606',14),
('20120608',13),
('20120609',10);
go
declare @StartDate date = '20120602';
declare @EndDate date = '20120611';
with e1(n) as (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1), -- 10 rows
e2(n) as (select a.n from e1 a cross join e1 b), -- 100 rows
e4(n) as (select a.n from e2 a cross join e2 b), -- 10,000 rows
tally(n) as (select 0 union all select row_number() over (order by (select null)) from e4)
select top (datediff(dd,@StartDate,@EndDate) + 1)
dateadd(dd,t.n,@StartDate) ReportingDate,
dt.CloseDate,
dt.Volume
from
tally t
cross apply (select top 1
CloseDate, Volume
from
#TestData
where
CloseDate < dateadd(dd,t.n,@StartDate)
order by
CloseDate desc) dt;
go
drop table #TestData;
go
June 29, 2012 at 9:37 am
Wow, this works like a charm, althouugh I need to read up some on tallys because I have no idea what it is doing from just looking at it.
I need to add a few more things from the inventory table, like TankNumber and Product. I also need to ignore closing dates if the volume is zero. If I just insert them into the query it pulls only the latest close date and the corresponding TankNumber and Product, while I need to get it for each TankNumber.
June 29, 2012 at 9:58 am
bigdickmagee (6/29/2012)
Wow, this works like a charm, althouugh I need to read up some on tallys because I have no idea what it is doing from just looking at it.I need to add a few more things from the inventory table, like TankNumber and Product. I also need to ignore closing dates if the volume is zero. If I just insert them into the query it pulls only the latest close date and the corresponding TankNumber and Product, while I need to get it for each TankNumber.
Two things. One, look at the fourth link down in my signature block for more on Tally Tables. Two, you will get better answers if you provide the full story of what you need instead of piecemeal stuff. Any problems with modifying the code, come back here and post.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply