April 4, 2016 at 4:06 pm
[font="Courier New"]Suppose I have the data set below:
contract lineitem effectiveDate termDate rate
1234 1ITEM110/12/20054/4/2016 $100.00
1234 1ITEM15/1/20154/4/2016 $125.00
1234 1ITEM210/12/20054/4/2016 $200.00
1234 1ITEM25/1/20154/4/2016 $225.00
1234 2ITEM310/12/20054/4/2016 $300.00
1234 2ITEM35/1/20154/4/2016 $325.00
1234 3ITEM410/12/20054/4/2016 $400.00
1234 3ITEM45/1/20154/4/2016 $425.00
Given the start date of 1/1/2015 and the end date of 12/31/2015, I need to get a count of how many days each rate was active during the period. For example:
ITEM1, Line 1: $100 was effective 10/12/2005 through 4/30/2015. Since we're only interested in 1/1/2015-12/31/2015, this is actually only active 1/1/2015-4/30/2015.
$125 was the effective rate beginning 5/1 through 4/40216. Since we're only interested in 1/1/2015-12/31/2015, this is actually only active 5/1/2015-12/31/2015.
This item was active at $100 for 120 days and at $125 for 245 during the period being examined. I would like these values added as a column for each row.
How can I calculate this as part of a TSQL statement? I've built a separate application that handles this outside of the database layer, but there is a new requirement that a legacy reporting tool needs to consume this directly from SQL, so I need to find a way for SQL to prepare this data.
Any thoughts would be appreciated.[/font]
April 4, 2016 at 5:15 pm
swansonm (4/4/2016)
[font="Courier New"]Suppose I have the data set below:
contract lineitem effectiveDate termDate rate
1234 1ITEM110/12/20054/4/2016 $100.00
1234 1ITEM15/1/20154/4/2016 $125.00
1234 1ITEM210/12/20054/4/2016 $200.00
1234 1ITEM25/1/20154/4/2016 $225.00
1234 2ITEM310/12/20054/4/2016 $300.00
1234 2ITEM35/1/20154/4/2016 $325.00
1234 3ITEM410/12/20054/4/2016 $400.00
1234 3ITEM45/1/20154/4/2016 $425.00
Given the start date of 1/1/2015 and the end date of 12/31/2015, I need to get a count of how many days each rate was active during the period. For example:
ITEM1, Line 1: $100 was effective 10/12/2005 through 4/30/2015. Since we're only interested in 1/1/2015-12/31/2015, this is actually only active 1/1/2015-4/30/2015.
$125 was the effective rate beginning 5/1 through 4/40216. Since we're only interested in 1/1/2015-12/31/2015, this is actually only active 5/1/2015-12/31/2015.
This item was active at $100 for 120 days and at $125 for 245 during the period being examined. I would like these values added as a column for each row.
How can I calculate this as part of a TSQL statement? I've built a separate application that handles this outside of the database layer, but there is a new requirement that a legacy reporting tool needs to consume this directly from SQL, so I need to find a way for SQL to prepare this data.
Any thoughts would be appreciated.[/font]
Based on your explanation, I think that your termDate for Line1, Item1 at $100 is quoted wrongly in the sample data.
You should be able to use something like this:
use Samples;
go
declare @StartOn date = '20150101';
declare @EndOn date = '20160101';
if object_id('tempdb..#EffDates', 'U') is not null
drop table #EffDates;
create table #EffDates
(
EffDatesId int identity(1, 1)
primary key
,StartDate date
,EndDate date
);
insert #EffDates
(StartDate, EndDate)
values ('20051012', '20150430'),
('20150501', '20160404')
selected.EffDatesId
,ed.StartDate
,ed.EndDate
,dates.EffectiveStart
,dates.EffectiveEnd
,duration = iif(datediff(day, dates.EffectiveStart, dates.EffectiveEnd) > 0, datediff(day, dates.EffectiveStart, dates.EffectiveEnd), 0)
from #EffDates ed
cross apply (select EffectiveStart = iif(ed.StartDate < @StartOn, @StartOn, ed.StartDate), EffectiveEnd = iif(ed.EndDate > @EndOn, @EndOn, ed.EndDate)) dates
April 5, 2016 at 7:38 am
This would work if the termDate is the date of the term of that line/effective rate, but not since the termDate is actually the term date of the line (notice they all have the term date of 4/4/2016), correct?
Unfortunately, the data structure is that the effective date is when the new rate kicks in, but the term date is for that actual line/item. I essentially have to look at what is the most recent rate in effect during my time period, apply that up until the beginning, and then move on to the next most recent rate. The term date does not reflect the termination of the rate - just of the line item.
Make sense?
April 5, 2016 at 8:40 am
swansonm (4/5/2016)
This would work if the termDate is the date of the term of that line/effective rate, but not since the termDate is actually the term date of the line (notice they all have the term date of 4/4/2016), correct?Unfortunately, the data structure is that the effective date is when the new rate kicks in, but the term date is for that actual line/item.
Make sense?
First, Phil's solution is super-excellent, get's you very close and will perform great provided that the right index is in place.
I essentially have to look at what is the most recent rate in effect during my time period, apply that up until the beginning, and then move on to the next most recent rate. The term date does not reflect the termination of the rate - just of the line item.
If I understand what you're saying, you can simply create another subquery that gets the MAX(EffectiveDate) then use CROSS APPLY to get pass that value to your OUTER query the same way Phil did with the "dates" values. I could be wrong, It may be helpful to post a second query demonstrates what the resultset should look like.
-- Itzik Ben-Gan 2001
April 5, 2016 at 9:39 am
swansonm (4/5/2016)
This would work if the termDate is the date of the term of that line/effective rate, but not since the termDate is actually the term date of the line (notice they all have the term date of 4/4/2016), correct?Unfortunately, the data structure is that the effective date is when the new rate kicks in, but the term date is for that actual line/item. I essentially have to look at what is the most recent rate in effect during my time period, apply that up until the beginning, and then move on to the next most recent rate. The term date does not reflect the termination of the rate - just of the line item.
Make sense?
This results in a less tidy solution, I'm afraid. To derive the termination date for the line's rate, I used the LEAD() function. This meant that I had to discard the CROSS APPLY and go for an in-line solution.
The Duration calculation is essentially the same, but complicated by the need to plug in the long LEAD() construction.
DECLARE @StartOn DATE = '20150101';
DECLARE @EndOn DATE = '20160101';
IF OBJECT_ID('tempdb..#EffDates','U') IS NOT NULL
DROP TABLE #EffDates;
CREATE TABLE #EffDates
(
EffDatesId INT IDENTITY(1,1)
PRIMARY KEY
,Line INT
,Item VARCHAR(20)
,EffectiveDate DATE
,TermDate DATE
);
INSERT #EffDates
(Line,Item,EffectiveDate,TermDate)
VALUES (1,'ITEM1','20051012','20160404'),
(1,'ITEM1','20150501','20160404'),
(1,'ITEM2','20051012','20160404'),
(1,'ITEM2','20150501','20160404');
SELECT ed.EffDatesId
, ed.Line
, ed.Item
, ed.EffectiveDate
, EffectiveStart = IIF(ed.EffectiveDate < @StartOn,@StartOn,ed.EffectiveDate)
, EffectiveEnd = IIF(LEAD(ed.EffectiveDate,1,ed.TermDate) OVER (PARTITION BY ed.Line,ed.Item ORDER BY ed.EffectiveDate) > @EndOn,@EndOn,LEAD(ed.EffectiveDate,
1,ed.TermDate) OVER (PARTITION BY ed.Line,
ed.Item ORDER BY ed.EffectiveDate))
, ed.TermDate
, duration = IIF(DATEDIFF(DAY,IIF(ed.EffectiveDate < @StartOn,@StartOn,ed.EffectiveDate),
IIF(LEAD(ed.EffectiveDate,1,ed.TermDate) OVER (PARTITION BY ed.Line,ed.Item ORDER BY ed.EffectiveDate) > @EndOn,@EndOn,LEAD(ed.EffectiveDate,
1,ed.TermDate) OVER (PARTITION BY ed.Line,
ed.Item ORDER BY ed.EffectiveDate))) > 0,DATEDIFF(DAY,
IIF(ed.EffectiveDate < @StartOn,@StartOn,ed.EffectiveDate),
IIF(LEAD(ed.EffectiveDate,
1,ed.TermDate) OVER (PARTITION BY ed.Line,
ed.Item ORDER BY ed.EffectiveDate) > @EndOn,@EndOn,LEAD(ed.EffectiveDate,
1,ed.TermDate) OVER (PARTITION BY ed.Line,
ed.Item ORDER BY ed.EffectiveDate))),0)
FROM #EffDates ed;
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply