Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Combine Concurrent Dates per product

Author  Topic 

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-05-19 : 04:47:18
Hello all.

Say I have the following table wnad data 

DECLARE @costBreakdown TABLE (
	[employeeID] INT
	, [startDate] DATETIME
	, [endDate] DATETIME
	, [productID] INT
	, [optionID] INT
	, [cost] MONEY
	)

INSERT @costBreakdown (
	[employeeID]
	, [startDate]
	, [endDate]
	, [productID]
	, [optionID]
	, [cost]
	)
      SELECT 1, '20080101', '20080120', 1, 1, 100
UNION SELECT 1, '20080121', '20080331', 1, 1, 100
UNION SELECT 1, '20080401', '20081231', 1, 2, 200
UNION SELECT 1, '20090101', '20090331', 1, 1, 100
UNION SELECT 2, '20080101', '20080412', 2, 5, 1000
UNION SELECT 2, '20080414', '20081231', 2, 5, 1000

SELECT
	*
FROM
	@costBreakdown
ORDER BY
	[employeeID]
	, [productId]
	, [startDate]
	, [endDate]



I need to combine the concurrent dates for the same product / option as long as there is no break between the [enddate] and [startdate] of the following entry.

So for that dataset I need to combine the first two rows for ([employeeId] = 1) into 1 row with the start Date of 20080101 and the end Date of 20080331.

Because there is a change of option for the next time chunk I don't want to combine it with the other [product] = 1, [option] = 1 entry.

For employee = 2 I don't want to combine the rows because the entries are not concurrent -- there is 1 day gap inbetween.

The cost is an annual value so does not need changed. Also products with the same options cannot have different costs so I don't have to worry about that.

My required result set is:

employeeID  startDate               endDate                 productID   optionID    cost
----------- ----------------------- ----------------------- ----------- ----------- ---------------------
1           2008-01-01 00:00:00.000 2008-03-31 00:00:00.000 1           1           100.00
1           2008-04-01 00:00:00.000 2008-12-31 00:00:00.000 1           2           200.00
1           2009-01-01 00:00:00.000 2009-03-31 00:00:00.000 1           1           100.00
2           2008-01-01 00:00:00.000 2008-04-12 00:00:00.000 2           5           1000.00
2           2008-04-14 00:00:00.000 2008-12-31 00:00:00.000 2           5           1000.00


Cheers.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-05-19 : 05:14:11
A cte will do. :-)


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-05-19 : 05:18:57
Cheers Peso! I had thought of that but am being a little slow this morning coming up with all the details (currently bogged down in some ROW_NUMBER() ideas)

I've got this:

SELECT
	*
FROM
	(
		SELECT
			ROW_NUMBER() OVER(ORDER BY [employeeID], [productId], [startDate], [endDate]) AS [row]
			, *
		FROM
			@costBreakdown
	)
	a
	JOIN
	(
		SELECT
			ROW_NUMBER() OVER(ORDER BY [employeeID], [productId], [startDate], [endDate]) AS [row]
			, *
		FROM
			@costBreakdown
	)
	b ON
		b.[row] = a.[row] + 1
		AND DATEDIFF(DAY, a.[endDate], b.[startDate]) = 1
		AND a.[productId] = b.[productID]
		AND a.[optionID] = b.[optionID]

to identify target rows.

As my real dataset can be huge I'm thinking best to copy all the rows that don't fit that criteria and then run a CTE on the ones that do?


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-05-19 : 05:20:07
Maybe I just need some coffee. :)


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-05-19 : 05:32:37
CTE anchor part:
Select earliest entry for all employees with columns employeeid, startdate, enddate, total start date and total end date

CTE recursive part:
Get next entry (from another cte with row_number() thingy).
If dates "match", change total end date. If no "match" change total start date and total end date.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-05-19 : 05:49:47
Cheers again Peso -- I'm going to go away and get something that works. I'll post it when I do for the inevitable "improvements"

:)




Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-05-19 : 06:36:13
Ok -- I have a solution!

I've changed the data slightly (just putting some more in it and adding a required primary key)

Here it is - I'm sure it can be improved!

DECLARE @costBreakdown TABLE (
	[ID] INT IDENTITY(1,1) PRIMARY KEY
	, [employeeID] INT
	, [startDate] DATETIME
	, [endDate] DATETIME
	, [productID] INT
	, [optionID] INT
	, [cost] MONEY
	)

INSERT @costBreakdown (
	[employeeID]
	, [startDate]
	, [endDate]
	, [productID]
	, [optionID]
	, [cost]
	)
      SELECT 1, '20080101', '20080120', 1, 1, 100
UNION SELECT 1, '20080121', '20080331', 1, 1, 100
UNION SELECT 1, '20080401', '20080630', 1, 1, 100
UNION SELECT 1, '20080701', '20081231', 1, 2, 200
UNION SELECT 1, '20090101', '20090331', 1, 1, 100
UNION SELECT 1, '20090401', '20091231', 1, 1, 100
UNION SELECT 2, '20080101', '20080412', 2, 5, 1000
UNION SELECT 2, '20080414', '20081231', 2, 5, 1000

SELECT
	[employeeID]
	, [productId]
	, [optionID]
	, [StartDate]
	, [EndDate]
	, [cost]
FROM
	@costBreakdown


-- CTE to combine concurrent dates
; WITH orders AS (
	SELECT
		ROW_NUMBER() OVER(ORDER BY [employeeID], [productId],  [startDate], [endDate]) AS [row]
		, [Id]
		, [employeeId]
		, [productId]
		, [optionId]
		, [startDate]
		, [endDate]
	FROM
		@costBreakdown
	)
, recur AS (
	-- Parent Decleration
	SELECT
		[ID]
		, [employeeID]
		, [productID]
		, [optionID]
		, [startDate]
		, [endDate]
		, [startDate] AS [baseStartDate]
		, [endDate] AS [baseEndDate]
		, 0 AS [level]
	FROM
		@costBreakdown
	WHERE
		[Id] NOT IN (
			SELECT
				b.[ID]
			FROM
				orders a
				JOIN orders b ON
					b.[row] = a.[row] + 1
					AND b.[startDate] = a.[endDate] + 1
					AND b.[optionId] = a.[optionId]
					AND b.[productId] = a.[productId]
		)
	-- Recursive Decleration
	UNION ALL SELECT
		r.[ID]
		, r.[employeeId]
		, r.[productId]
		, r.[optionId]
		, o.[startDate]
		, o.[endDate]
		, c.[startDate]
		, o.[endDate]
		, r.[level] + 1
	FROM
		
		orders o

		JOIN recur r ON
			r.[employeeId] = o.[employeeID] 
			AND r.[productId] = o.[productId]
			AND r.[optionId] = o.[optionID]
			AND DATEDIFF(DAY, r.[endDate], o.[startDate]) = 1

		JOIN @costBreakdown c ON c.[Id] = r.[Id]
	)
SELECT
	c.[employeeID]
	, c.[productId]
	, c.[optionID]
	, r.[baseStartDate]
	, r.[baseEndDate]
	, c.[cost]
FROM
	(
		SELECT
			ROW_NUMBER() OVER(PARTITION BY [Id] ORDER BY [level] DESC) AS [rowPos]
			, [ID] 
			, [baseStartDate]
			, [baseEndDate]
		FROM
			recur
	)
	r
	JOIN @costBreakdown c ON c.[Id] = r.[Id]
WHERE
	r.[rowPos] = 1



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2009-05-19 : 12:32:26
Here is a simple way, but I'm not 100% about the Partitioning for your real data (but it does work for the sample data):
SELECT
	employeeID,
	startDate,
	EndDate,
	productID,
	optionID,
	cost
FROM
(
	SELECT 
		A.employeeID,
		A.startDate,
		COALESCE(B.EndDate, A.EndDate) AS EndDate,
		A.productID,
		A.optionID,
		A.cost,
		ROW_NUMBER() OVER 
			(PARTITION BY 
				A.employeeID, 
				COALESCE(B.EndDate, A.EndDate), 
				A.productID, 
				A.optionID, 
				A.cost
			ORDER BY
				A.employeeID, 
				COALESCE(B.EndDate, A.EndDate), 
				A.productID, 
				A.optionID, 
				A.cost
			) AS RowNum
	FROM 
		@costBreakdown AS A
	LEFT OUTER JOIN
		@costBreakdown AS B
		ON A.employeeID = B.employeeID
		AND A.productID = B.productID
		AND A.optionID = B.optionID
		AND A.endDate = B.startDate - 1
) AS T
WHERE  
	RowNum = 1
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-05-20 : 04:54:42
Thanks for the input Lamps!

Your script doesn't work for the following set of data:

DECLARE @costBreakdown TABLE (
	[ID] INT IDENTITY(1,1) PRIMARY KEY
	, [employeeID] INT
	, [startDate] DATETIME
	, [endDate] DATETIME
	, [productID] INT
	, [optionID] INT
	, [cost] MONEY
	)

INSERT @costBreakdown (
	[employeeID]
	, [startDate]
	, [endDate]
	, [productID]
	, [optionID]
	, [cost]
	)
      SELECT 1, '20080101', '20080120', 1, 1, 100
UNION SELECT 1, '20080121', '20080331', 1, 1, 100
UNION SELECT 1, '20080401', '20080630', 1, 1, 100
UNION SELECT 1, '20080701', '20081231', 1, 2, 200
UNION SELECT 1, '20090101', '20090331', 1, 1, 100
UNION SELECT 1, '20090401', '20091231', 1, 1, 100
UNION SELECT 2, '20080101', '20080412', 2, 5, 1000
UNION SELECT 2, '20080414', '20081231', 2, 5, 1000


Your code outputs:

employeeID  startDate               EndDate                 productID   optionID    cost
----------- ----------------------- ----------------------- ----------- ----------- ---------------------
1           2008-01-01 00:00:00.000 2008-03-31 00:00:00.000 1           1           100.00
1           2008-01-21 00:00:00.000 2008-06-30 00:00:00.000 1           1           100.00
1           2008-07-01 00:00:00.000 2008-12-31 00:00:00.000 1           2           200.00
1           2009-01-01 00:00:00.000 2009-12-31 00:00:00.000 1           1           100.00
2           2008-01-01 00:00:00.000 2008-04-12 00:00:00.000 2           5           1000.00
2           2008-04-14 00:00:00.000 2008-12-31 00:00:00.000 2           5           1000.00

Which is a little mangled.

-- The end result should amalgamate the first 3 rows (of the initial data set) into 1 row. Your script doesn't do that. I need a way to combine practically any number of sequential rows into 1 (though I'd be very surprised if my real data has any more than 10 concurrent rows for any particular employee / product pairing).

I appreciate that it does work for the initial sample set I posted so my bad for posting incomplete data!

Looks faster than my recursive cte though....


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2009-05-20 : 12:15:36
Ahh well.. it was worth a shot... :)
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site