June 28, 2011 at 9:47 am
Hi All,
If anyone can help me with a bit of guidance on this SQL it would be much appreciated.
In a database I have a charges table with various types of charges for differing time periods, each charge is linked to a customer id. I need to produce the total charge for a customer for a unique time period.
Below is a sample customer with two types of charges over a few years. I have also included the results I am trying achieve.
create table #test(
CustomerId int,
ChargeType varchar(20),
StartDate datetime,
EndDate datetime,
ChargeAmount decimal(8,2)
)
insert into #test
select 1, 'T1', '2005-04-01', '2006-03-31', 10 union all
select 1, 'T2', '2005-10-11', '2006-08-31', 20 union all
select 1, 'T1', '2006-04-01', '2007-03-31', 11 union all
select 1, 'T2', '2007-01-01', '2007-05-31', 24 union all
select 1, 'T1', '2007-04-01', '2008-03-31', 12
select * from #test
print 'RESULT REQUIRED'
select 1 as 'Customer', '2005-04-01' as 'StartDate', '2005-10-10' as 'EndDate', 10 as 'TotalCharge' union all
select 1, '2005-10-11', '2006-03-31', 30 union all
select 1, '2006-04-01', '2006-08-31', 31 union all
select 1, '2006-09-01', '2006-12-31', 11 union all
select 1, '2007-01-01', '2007-03-31', 35 union all
select 1, '2007-04-01', '2007-05-31', 36 union all
select 1, '2007-06-01', '2008-03-31', 12
order by 'StartDate'
Many Thanks,
Paul.
June 28, 2011 at 10:39 am
How are you defining the date ranges? Are they from an input parameter, or a table, or just completely arbitrary?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 28, 2011 at 10:49 am
How about this?
; WITH UnpivotTable AS
(
SELECT CustomerId , Dates , Cols , ChargeAmount
, RN = ROW_NUMBER() OVER( ORDER BY Dates ASC)
FROM
(
SELECT
CustomerId
,ChargeType
,StartDate
,EndDate
,ChargeAmount
FROM #test
) SourceTable
UNPIVOT
( Dates FOR Cols IN (StartDate,EndDate)) UnpivotHandle
)
,CalculatedCharges AS
(
SELECT Table1.CustomerId ,
CASE WHEN Table1.Cols = 'EndDate' AND Table2.Cols = 'EndDate'
THEN DATEADD(DD , 1 , Table1.Dates)
ELSE Table1.Dates
END StartDate ,
CASE WHEN Table1.Cols = 'StartDate' AND Table2.Cols = 'StartDate'
THEN DATEADD(DD , -1 , Table2.Dates)
WHEN Table1.Cols = 'StartDate' AND Table2.Cols = 'EndDate'
THEN Table2.Dates
WHEN Table1.Cols = 'EndDate' AND Table2.Cols = 'EndDate'
THEN Table2.Dates
END EndDate,
CASE WHEN Table1.Cols = 'StartDate' AND Table2.Cols = 'StartDate'
THEN Table1.ChargeAmount
WHEN Table1.Cols = 'StartDate' AND Table2.Cols = 'EndDate'
THEN Table1.ChargeAmount + Table2.ChargeAmount
WHEN Table1.Cols = 'EndDate' AND Table2.Cols = 'EndDate'
THEN Table2.ChargeAmount
END ChargeAmount
FROM UnpivotTable Table1
LEFT JOIN UnpivotTable Table2
ON Table1.RN + 1 = Table2.RN
)
SELECT *
FROM CalculatedCharges
WHERE EndDate IS NOT NULL
One thing differs from the result i get and the result u have shown, this row:
1 2006-09-01 2006-12-31 11
I dont find those dates at all in the sample input data u provided; Can you please elaborate me on how u arrived at that particular row? Are there any business logic i am missing from your requirements? or, am i overseeing something?
HTH
{Edit: Edited the typo in StartDate and ChargeAmount calculation for EndDate-EndDate scenario}
June 28, 2011 at 10:51 am
The effective date range for each charge is held in the table. When I run the procedure to calculate the charges I envisage submitting start and end parameters that will show all charges in effect during the period, this will show charges even if the charge starts before the query start parameter or ends after the query end parameter, i.e. the charge is in effect during the parameter dates. Hope this makes sense.
Thanks,
Paul.
GSquared (6/28/2011)
How are you defining the date ranges? Are they from an input parameter, or a table, or just completely arbitrary?
June 28, 2011 at 3:56 pm
Hi, thanks for this. Pivot and UnPivot are an area of TSQL I have yet to fully appreciate the capabilities of and your code is much closer than I have got so far. One of the values in my ‘required results’ was incorrect 25 should be 35 (now edited in original post) this was correct in your query results.
The results from your script differ from what I need on a couple of results.
As you commented the row below is missing:
StartDate EndDate ChargeAmount
2006-09-01 2006-12-31 11
This is from the T1 charge in effect from 2006-04-01 to 2007-03-31 but for the period when no T2 type charge is levied.
The last row returned from your SQL is:
StartDate EndDate ChargeAmount
2007-05-30 2008-03-31 24.00
This row should be:
StartDate EndDate ChargeAmount
2007-06-01 2008-03-31 12
From the period when only the T1 charge is in effect from 2007-04-01 to 2008-03-31.
Thanks again for your reply I will now spend some time to understand the detail of how your query works. If you have any suggestions on how to resolve the above issues please let me know.
Paul.
ColdCoffee (6/28/2011)
How about this?
; WITH UnpivotTable AS(
SELECT CustomerId , Dates , Cols , ChargeAmount
, RN = ROW_NUMBER() OVER( ORDER BY Dates ASC)
FROM
(
SELECT
CustomerId
,ChargeType
,StartDate
,EndDate
,ChargeAmount
FROM #test
) SourceTable
UNPIVOT
( Dates FOR Cols IN (StartDate,EndDate)) UnpivotHandle
)
,CalculatedCharges AS
(
SELECT Table1.CustomerId ,
CASE WHEN Table1.Cols = 'EndDate' AND Table2.Cols = 'EndDate'
THEN DATEADD(DD , 1 , Table1.Dates)
ELSE Table1.Dates
END StartDate ,
CASE WHEN Table1.Cols = 'StartDate' AND Table2.Cols = 'StartDate'
THEN DATEADD(DD , -1 , Table2.Dates)
WHEN Table1.Cols = 'StartDate' AND Table2.Cols = 'EndDate'
THEN Table2.Dates
WHEN Table1.Cols = 'EndDate' AND Table2.Cols = 'EndDate'
THEN Table2.Dates
END EndDate,
CASE WHEN Table1.Cols = 'StartDate' AND Table2.Cols = 'StartDate'
THEN Table1.ChargeAmount
WHEN Table1.Cols = 'StartDate' AND Table2.Cols = 'EndDate'
THEN Table1.ChargeAmount + Table2.ChargeAmount
WHEN Table1.Cols = 'EndDate' AND Table2.Cols = 'EndDate'
THEN Table2.ChargeAmount
END ChargeAmount
FROM UnpivotTable Table1
LEFT JOIN UnpivotTable Table2
ON Table1.RN + 1 = Table2.RN
)
SELECT *
FROM CalculatedCharges
WHERE EndDate IS NOT NULL
One thing differs from the result i get and the result u have shown, this row:
1 2006-09-01 2006-12-31 11
I dont find those dates at all in the sample input data u provided; Can you please elaborate me on how u arrived at that particular row? Are there any business logic i am missing from your requirements? or, am i overseeing something?
HTH
{Edit: Edited the typo in StartDate and ChargeAmount calculation for EndDate-EndDate scenario}
June 28, 2011 at 4:09 pm
Paul-202125 (6/28/2011)
The last row returned from your SQL is:
StartDate EndDate ChargeAmount2007-05-30 2008-03-31 24.00
This row should be:
StartDate EndDate ChargeAmount2007-06-01 2008-03-31 12
From the period when only the T1 charge is in effect from 2007-04-01 to 2008-03-31.
This is already taken care in the new code 🙂 check out my new code in the old post :w00t:
as for the missed out row, i need to dig deep.. i dont have the bandwidth to do that now, i shall give it a shot when i reach home..
And, thanks for the feedback..:-)
June 28, 2011 at 4:23 pm
Thanks. The revised code does solve the last row issue. I'm off to get some shut eye :doze: it is now tomorrows problem!
ColdCoffee (6/28/2011)
Paul-202125 (6/28/2011)
This is already taken care in the new code 🙂 check out my new code in the old post :w00t:
as for the missed out row, i need to dig deep.. i dont have the bandwidth to do that now, i shall give it a shot when i reach home..
And, thanks for the feedback..:-)
June 29, 2011 at 10:49 am
With a great deal of inspiration from ColdCoffee the following works but I am concerned about the performance of the query with RBAR to calculate the charge for each given date range. Any suggestions on how to improve it will be gratefully received.
create table #test(
CustomerId int,
ChargeType varchar(20),
StartDate datetime,
EndDate datetime,
ChargeAmount decimal(8,2)
)
insert into #test
select 1, 'T1', '2005-04-01', '2006-03-31', 10 union all
select 1, 'T2', '2005-10-11', '2006-08-31', 20 union all
select 1, 'T1', '2006-04-01', '2007-03-31', 11 union all
select 1, 'T2', '2007-01-01', '2007-05-31', 24 union all
select 1, 'T1', '2007-04-01', '2008-03-31', 12
select * from #test
print 'RESULT REQUIRED'
select 1 as 'Customer', '2005-04-01' as 'StartDate', '2005-10-10' as 'EndDate', 10 as 'TotalCharge' union all
select 1, '2005-10-11', '2006-03-31', 30 union all
select 1, '2006-04-01', '2006-08-31', 31 union all
select 1, '2006-09-01', '2006-12-31', 11 union all
select 1, '2007-01-01', '2007-03-31', 35 union all
select 1, '2007-04-01', '2007-05-31', 36 union all
select 1, '2007-06-01', '2008-03-31', 12
order by 'StartDate'
print 'Proposed RBAR Solution'
; WITH UnpivotTable AS
(
SELECT CustomerId , Dates , Cols , ChargeAmount
, RN = ROW_NUMBER() OVER( ORDER BY Dates ASC)
FROM
(
SELECT
CustomerId
,ChargeType
,StartDate
,EndDate
,ChargeAmount
FROM #test
union
SELECT
CustomerId
,'T3'
,dateadd(day,1,EndDate)
,dateadd(day,-1,StartDate)
,0
FROM #test
) SourceTable
UNPIVOT
( Dates FOR Cols IN (StartDate,EndDate)) UnpivotHandle
)
,CalculatedCharges AS
(
SELECT Table1.CustomerId ,
CASE WHEN Table1.Cols = 'EndDate' AND Table2.Cols = 'EndDate'
THEN DATEADD(DD , 1 , Table1.Dates)
ELSE Table1.Dates
END StartDate ,
CASE WHEN Table1.Cols = 'StartDate' AND Table2.Cols = 'StartDate'
THEN DATEADD(DD , -1 , Table2.Dates)
WHEN Table1.Cols = 'StartDate' AND Table2.Cols = 'EndDate'
THEN Table2.Dates
WHEN Table1.Cols = 'EndDate' AND Table2.Cols = 'EndDate'
THEN Table2.Dates
END EndDate
FROM UnpivotTable Table1
LEFT JOIN UnpivotTable Table2
ON Table1.RN + 1 = Table2.RN
)
SELECT c.*, (select SUM(ChargeAmount) From #test t where c.StartDate >= t.StartDate and c.Enddate <= t.EndDate) AS 'NewAmount'
FROM CalculatedCharges c
WHERE EndDate IS NOT NULL and StartDate < EndDate
July 1, 2011 at 5:01 am
Simplify!
;WITH cteSource(CustomerID, StartDate, EndDate)
AS (
SELECT CustomerID,
MIN(theDate) AS StartDate,
MAX(theDate) AS EndDate
FROM (
SELECT t.CustomerID,
f.theDate,
DENSE_RANK() OVER (PARTITION BY t.CustomerID ORDER BY f.theDate) / 2 AS SeqID
FROM #Test AS t
CROSS APPLY (
VALUES (t.StartDate),
(DATEADD(DAY, -1, t.StartDate)),
(t.EndDate),
(DATEADD(DAY, 1, t.EndDate))
) AS f(theDate)
) AS d
GROUP BY CustomerID,
SeqID
HAVING COUNT(*) > 1
)
SELECT s.CustomerID,
s.StartDate,
s.EndDate,
f.ChargeAmount
FROM cteSource AS s
CROSS APPLY (
SELECT SUM(w.ChargeAmount)
FROM #Test AS w
WHERE w.CustomerID = s.CustomerID
AND w.StartDate <= s.EndDate
AND w.EndDate >= s.StartDate
) AS f(ChargeAmount)
ORDER BY s.CustomerID,
s.StartDate
N 56°04'39.16"
E 12°55'05.25"
July 1, 2011 at 5:44 am
Hi SwePeso,
Thanks for taking the time to look at this, your solution apppears way better than mine.
I am going to add CROSS APPLY to my study list along with with PIVOT brothers!
I will take some time to fully digest your code and try it with a larger dataset.
Thanks again, Paul.
SwePeso (7/1/2011)
Simplify!
;WITH cteSource(CustomerID, StartDate, EndDate)
AS (
SELECT CustomerID,
MIN(theDate) AS StartDate,
MAX(theDate) AS EndDate
FROM (
SELECT t.CustomerID,
f.theDate,
DENSE_RANK() OVER (PARTITION BY t.CustomerID ORDER BY f.theDate) / 2 AS SeqID
FROM #Test AS t
CROSS APPLY (
VALUES (t.StartDate),
(DATEADD(DAY, -1, t.StartDate)),
(t.EndDate),
(DATEADD(DAY, 1, t.EndDate))
) AS f(theDate)
) AS d
GROUP BY CustomerID,
SeqID
HAVING COUNT(*) > 1
)
SELECT s.CustomerID,
s.StartDate,
s.EndDate,
f.ChargeAmount
FROM cteSource AS s
CROSS APPLY (
SELECT SUM(w.ChargeAmount)
FROM #Test AS w
WHERE w.CustomerID = s.CustomerID
AND w.StartDate <= s.EndDate
AND w.EndDate >= s.StartDate
) AS f(ChargeAmount)
ORDER BY s.CustomerID,
s.StartDate
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply