SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Compute Length of Service Over Multiple Records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LaurieCox
Posting Yak Master

USA
149 Posts

Posted - 12/05/2013 :  09:18:19  Show Profile  Reply with Quote
I have this table:

CREATE TABLE TestData(
	[ClientId] [int] NULL,
	[RevenueCode] [varchar](16) NULL,
	[ServiceDate] [date] NOT NULL,
	[ServiceEndDate] [date] NULL,
)
(test data at end of post)

I need to combine rows of data where the service is continuous for a given ClientId/RevenueCode and compute the length of stay (in days) for each continuous stay.

Example:

From this data:

Row  ClientId  RevenueCode  ServiceDate ServiceEndDate DaysDiffer
---- --------- ------------ ----------- -------------- -----------
1    59937     124          2013-04-09  2013-04-09     n/a
2    59937     124          2013-04-10  2013-04-10     1
3    59937     124          2013-04-11  2013-04-11     1
4    59937     124          2013-05-04  2013-05-04     23
5    59937     124          2013-05-05  2013-05-05     1
6    59937     912          2012-04-24  2012-04-25     n/a
7    59937     912          2012-04-25  2012-04-26     0
8    59937     912          2012-04-26  2012-04-27     0
9    59937     912          2012-04-27  2012-04-28     0

I should get this result:

Row  ClientId  RevenueCode  ServiceDate ServiceEndDate LengthOfService
---- --------- ------------ ----------- -------------- ---------------
1    59937     124          2013-04-09  2013-04-11     3
2    59937     124          2013-05-04  2013-05-05     2
3    59937     912          2012-04-24  2012-04-28     5

(Row and DaysDiffer columns added to help with explaining problem)

Rows 1 thru 5 (from input data) are all under the same revenue code (124) but there is a gap of 23 days between the ServiceEndDate of row 3 and the ServiceDate of row 4, These are two different stays (one that is 2 days long and one 3 days long). RevenueCode 912 has one stay which was 5 days long.

Notice that sometimes the ServiceDate will be the same as the ServiceEndDate (of the previous record) and sometimes it will be one day later (DaysDiffer) column. Both of these indicate a continuous stay. There needs to be at least 2 days between the ServiceEndDate of one row and the ServiceDate of the next row to be a break in service and thus be a separate stay.

My expected results from the test data below is:

Row  ClientId  RevenueCode  ServiceDate ServiceEndDate LengthOfService
---- --------- ------------ ----------- -------------- ---------------
1    8         124          2013-03-09  2013-03-13     5
2    364       100          2012-04-27  2012-05-15     19
3    364       100          2013-05-21  2013-05-31     11
4    5919      100          2012-03-05  2012-08-23     172
5    5919      100          2013-04-26  2013-07-01     67
6    24077     124          2013-02-22  2013-02-26     5
7    24077     124          2013-06-09  2013-06-12     4
8    24077     124          2013-07-27  2013-07-30     4
9    59937     124          2013-04-09  2013-04-11     3
10   59937     124          2013-05-04  2013-05-05     2
11   59937     912          2012-04-24  2012-04-28     5

Thanks

TestData:

CREATE TABLE #TestData(
	[ClientId] [int] NULL,
	[RevenueCode] [varchar](16) NULL,
	[ServiceDate] [date] NOT NULL,
	[ServiceEndDate] [date] NULL,
)

insert into #TestData
Select 8, 124, '03/09/2013', '03/13/2013' union all
Select 364, 100, '04/27/2012', '05/15/2012' union all
Select 364, 100, '05/21/2013', '05/31/2013' union all
Select 5919, 100, '03/05/2012', '04/01/2012' union all
Select 5919, 100, '04/01/2012', '05/01/2012' union all
Select 5919, 100, '05/01/2012', '06/01/2012' union all
Select 5919, 100, '06/01/2012', '07/01/2012' union all
Select 5919, 100, '07/01/2012', '08/01/2012' union all
Select 5919, 100, '08/01/2012', '08/23/2012' union all
Select 5919, 100, '04/26/2013', '05/01/2013' union all
Select 5919, 100, '05/01/2013', '06/01/2013' union all
Select 5919, 100, '06/01/2013', '07/01/2013' union all
Select 24077, 124, '02/22/2013', '02/22/2013' union all
Select 24077, 124, '02/23/2013', '02/23/2013' union all
Select 24077, 124, '02/23/2013', '02/24/2013' union all
Select 24077, 124, '02/24/2013', '02/25/2013' union all
Select 24077, 124, '02/26/2013', '02/26/2013' union all
Select 24077, 124, '06/09/2013', '06/09/2013' union all
Select 24077, 124, '06/10/2013', '06/10/2013' union all
Select 24077, 124, '06/11/2013', '06/11/2013' union all
Select 24077, 124, '06/12/2013', '06/12/2013' union all
Select 24077, 124, '07/27/2013', '07/27/2013' union all
Select 24077, 124, '07/28/2013', '07/28/2013' union all
Select 24077, 124, '07/29/2013', '07/29/2013' union all
Select 24077, 124, '07/30/2013', '07/30/2013' union all
Select 59937, 124, '04/09/2013', '04/09/2013' union all
Select 59937, 124, '04/10/2013', '04/10/2013' union all
Select 59937, 124, '04/11/2013', '04/11/2013' union all
Select 59937, 124, '05/04/2013', '05/04/2013' union all
Select 59937, 124, '05/05/2013', '05/05/2013' union all
Select 59937, 912, '04/24/2012', '04/25/2012' union all
Select 59937, 912, '04/25/2012', '04/26/2012' union all
Select 59937, 912, '04/26/2012', '04/27/2012' union all
Select 59937, 912, '04/27/2012', '04/28/2012'


Ifor
Aged Yak Warrior

626 Posts

Posted - 12/05/2013 :  12:25:37  Show Profile  Reply with Quote
You could use a calendar table (google this) and the row difference grouping technique.

I am just generating the calendar table on the fly here.


WITH Calendar
AS
(
	SELECT DATEADD(d, number, CAST('20120101' AS date)) AS cDate
	FROM master.dbo.spt_values
	WHERE [type] = 'P'
)
,DistinctDates
AS
(
	SELECT DISTINCT T.ClientId, T.RevenueCode, C.cDate AS ServiceDate
	FROM #TestData T
		JOIN Calendar C
			ON C.cDate BETWEEN T.ServiceDate and T.ServiceEndDate
)
,Grps
AS
(
	SELECT ClientId, RevenueCode, ServiceDate
		,DATEDIFF(d, '20120101', ServiceDate) 
			- ROW_NUMBER() OVER (PARTITION BY ClientId, RevenueCode ORDER BY ServiceDate) As Grp
	FROM DistinctDates
)
,Ranges
AS
(
	SELECT ClientId, RevenueCode, Grp
		,MIN(ServiceDate) AS ServiceDate
		,MAX(ServiceDate) AS ServiceEndDate
	FROM Grps
	GROUP BY ClientId, RevenueCode, Grp
)
SELECT ClientId, RevenueCode, ServiceDate, ServiceEndDate
	,DATEDIFF(d, ServiceDate, ServiceEndDate) + 1 AS LengthOfService
FROM Ranges;

Go to Top of Page

LaurieCox
Posting Yak Master

USA
149 Posts

Posted - 12/05/2013 :  13:03:16  Show Profile  Reply with Quote

Hi Ifor,

Thank you very much for your help.

We do have a date table and when I replaced your calendar table with it everything worked great (I will have to test against the actual data).

I am going to have to carefully walk thru your code so that I understand how it works. I have been reading up about the window functions (row_number, rank, etc) and had some idea that my solution lay somewhere with them but was unsure how to proceed. Can you point me to any articles about them that can help me wrap my head around it?

I assume you picked the 2012/01/01 because it was before the minimum ServiceDate in my test data.

Thanks again,

Laurie

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/05/2013 :  13:14:20  Show Profile  Reply with Quote


CREATE CLUSTERED INDEX IDX_Clust ON #TestData([ClientId],[RevenueCode],[ServiceDate],ServiceEndDate)

DECLARE @ClientId int,@RevenueCode varchar(100),@ServiceDate date,@ServiceEndDate date

SELECT TOP 1 @ClientId = [ClientId],@RevenueCode=[RevenueCode],@ServiceDate = [ServiceDate],@ServiceEndDate=ServiceEndDate
FROM #TestData

UPDATE #TestData
SET @ServiceDate=[ServiceDate] = CASE WHEN [ClientId]= @ClientId AND [RevenueCode] = @RevenueCode AND [ServiceDate] > @ServiceDate AND [ServiceDate] <=  DATEADD(dd,1,@ServiceEndDate)
THEN @ServiceDate ELSE [ServiceDate] END,
@ServiceEndDate=[ServiceEndDate] = CASE WHEN [ClientId]= @ClientId AND [RevenueCode] = @RevenueCode AND [ServiceEndDate] > @ServiceDate AND [ServiceEndDate] <=  @ServiceEndDate
THEN @ServiceEndDate ELSE [ServiceEndDate] END,
@ClientId = [ClientId],@RevenueCode=[RevenueCode]
OPTION (MAXDOP 1)

SELECT ClientID,RevenueCode,ServiceDate,MAX(ServiceEndDate) AS ServiceEndDate,DATEDIFF(dd,ServiceDate,MAX(ServiceEndDate)) + 1 AS LengthOfService
FROM #testData
GROUP BY ClientID,RevenueCode,ServiceDate


output
---------------------------------------------------------------
ClientID	RevenueCode	ServiceDate	ServiceEndDate	lengthOfService
8	124	2013-03-09	2013-03-13	5
364	100	2012-04-27	2012-05-15	19
364	100	2013-05-21	2013-05-31	11
5919	100	2012-03-05	2012-08-23	172
5919	100	2013-04-26	2013-07-01	67
24077	124	2013-02-22	2013-02-26	5
24077	124	2013-06-09	2013-06-12	4
24077	124	2013-07-27	2013-07-30	4
59937	124	2013-04-09	2013-04-11	3
59937	124	2013-05-04	2013-05-05	2
59937	912	2012-04-24	2012-04-28	5



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 12/05/2013 13:17:01
Go to Top of Page

LaurieCox
Posting Yak Master

USA
149 Posts

Posted - 12/05/2013 :  13:33:26  Show Profile  Reply with Quote
Hi visakh16

Wow, now I have two different solutions to play with.

I had found this thread where you had shown this technique for a very similar problem and was trying to figure out how to modify for my needs.

I need to understand both solutions so that I can use them and modify them for other problems.

What are the pros and cons between the two approaches?

Thanks,

Laurie
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/05/2013 :  13:53:42  Show Profile  Reply with Quote
quote:
Originally posted by LaurieCox

Hi visakh16

Wow, now I have two different solutions to play with.

I had found this thread where you had shown this technique for a very similar problem and was trying to figure out how to modify for my needs.

I need to understand both solutions so that I can use them and modify them for other problems.

What are the pros and cons between the two approaches?

Thanks,

Laurie


My method is called quirky updates. It relies upon presence of clustered on the table and also has to be run without any parallelism

more info here

http://visakhm.blogspot.in/2010/03/using-quirky-updates-to-develop-well.html


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Ifor
Aged Yak Warrior

626 Posts

Posted - 12/05/2013 :  13:58:11  Show Profile  Reply with Quote
>> I assume you picked the 2012/01/01 because it was before the minimum ServiceDate in my test data.
Correct

The trick with this technique is to choose two orders where the difference between them is the same when they should be in the same group.

The value of the grp number does not matter as long as it is different to the other groups.

Eyeball:

WITH Calendar
AS
(
	SELECT DATEADD(d, number, CAST('20120101' AS date)) AS cDate
	FROM master.dbo.spt_values
	WHERE [type] = 'P'
)
,DistinctDates
AS
(
	SELECT DISTINCT T.ClientId, T.RevenueCode, C.cDate AS ServiceDate
	FROM #TestData T
		JOIN Calendar C
			ON C.cDate BETWEEN T.ServiceDate and T.ServiceEndDate
)
SELECT ClientId, RevenueCode, ServiceDate
	,DATEDIFF(d, '20090722', ServiceDate) AS Number1
	,ROW_NUMBER() OVER (PARTITION BY ClientId, RevenueCode ORDER BY ServiceDate) AS Number2
	,DATEDIFF(d, '20090722', ServiceDate) 
		- ROW_NUMBER() OVER (PARTITION BY ClientId, RevenueCode ORDER BY ServiceDate) As Grp
FROM DistinctDates
ORDER BY ClientId, RevenueCode, ServiceDate;

Edited by - Ifor on 12/05/2013 13:59:55
Go to Top of Page

LaurieCox
Posting Yak Master

USA
149 Posts

Posted - 12/05/2013 :  14:37:00  Show Profile  Reply with Quote
Ifor, visakh16,

Thanks for your responses. I will be looking at both solutions and reading visakh16's reference).

I really want to learn. The trick is not to just take the solution given but to work with it, understand how it works and be able to apply that to other problems.

Again thanks very much for all the help,

Laurie
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/06/2013 :  04:59:07  Show Profile  Reply with Quote
quote:
Originally posted by LaurieCox

Ifor, visakh16,

Thanks for your responses. I will be looking at both solutions and reading visakh16's reference).

I really want to learn. The trick is not to just take the solution given but to work with it, understand how it works and be able to apply that to other problems.

Again thanks very much for all the help,

Laurie


Appreciate that attitude
You'll certainly make it big on sql one day if you have that attitude

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000