Determine Days In Period For Each Row in TSQL

Transact-SQL
1

Suppose I have the data set below:

contract    line item	       effectiveDate     termDate	 rate 
1234	       1	 ITEM1	10/12/2005	4/4/2016	 $100.00 
1234	       1	 ITEM1	5/1/2015	        4/4/2016	 $125.00 
1234	       1	 ITEM2	10/12/2005	4/4/2016	 $200.00 
1234	       1	 ITEM2	5/1/2015	        4/4/2016	 $225.00 
1234	       2	 ITEM3	10/12/2005	4/4/2016	 $300.00 
1234	       2	 ITEM3	5/1/2015	        4/4/2016	 $325.00 
1234	       3	 ITEM4	10/12/2005	4/4/2016	 $400.00 
1234	       3	 ITEM4	5/1/2015	        4/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.

2

declare @startdate date='20150101', @enddate date='20151231' select contract, [line item], item, rate, datediff(day, CASE WHEN effectiveDate>@startdate THEN effectiveDate ELSE @startdate END, CASE WHEN termDate>@enddate THEN @enddate ELSE termDate) AS activeDays FROM myTable

3

@robert_volk, you missed an END on your last CASE :smiley:

4

Lack of sleep, thanks for catching it. :slight_smile:

5

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.

Make sense?

6

Can you explain it a little bit better?... in the data sample the the termDate is the same for all the items 4/4/2016, but in the explanation that you give after the data sample the termDate change. For example you say that the ITEM1, Line,$100 the termDate is 4/30/2015