Return date range - SQL Server Forums

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)

Return date range

Author

Topic

esilva
Starting Member

4 Posts

Posted - 2011-01-13 : 15:32:37

hey all, hoping someone can help me with figuring out how to get what i need done! :)

I have a transactional table that lists various transactions happening for various customers. Below i will try and simplify the issue.


CustomerKey    TransactionDate     TransactionStatus
1              01/01/2010          Connected
1              01/02/2010          Disconnected
1              01/03/2010          Connected
2              01/01/2010          Connected
2              01/02/2010          Disconnected
2              01/03/2010          Connected          
2              01/04/2010          Connected
2              01/05/2010          Connected
2              01/06/2010          Disconnected

My problem is that i have to find customers that are connected for 3 or more succeeding days. And return the start date, and the end date for each series, understanding that a customer may have several series of 3 connection days. I'd like to avoid using a loop to cycle through each record for each customer as there are thousands of customers and millions of records. Doing it this way would take a long time for the script to complete. I'm open to pretty much any other ideas. Therefore using the above sample, the script would return customer 2 with a start date of Jan 3rd and end date of Jan 5th. Remember, customer 2 may have more than one series of 3.

Hope that makes sense...... HELP!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-13 : 16:25:22

[code]DECLARE @Sample TABLE
(
CustomerKey INT,
TransactionDate DATETIME,
TransactionStatus VARCHAR(12)
)

INSERT @Sample
SELECT 1, '20100101', 'Connected' UNION ALL
SELECT 1, '20100102', 'Disconnected' UNION ALL
SELECT 1, '20100103', 'Connected' UNION ALL
SELECT 2, '20100101', 'Connected' UNION ALL
SELECT 2, '20100102', 'Disconnected' UNION ALL
SELECT 2, '20100103', 'Connected' UNION ALL
SELECT 2, '20100104', 'Connected' UNION ALL
SELECT 2, '20100105', 'Connected' UNION ALL
SELECT 2, '20100106', 'Disconnected'

;WITH cteSource(CustomerKey, TransactionDate, TransactionStatus, GrpID)
AS (
SELECT CustomerKey,
TransactionDate,
TransactionStatus,
DATEDIFF(DAY, 0, TransactionDate) - ROW_NUMBER() OVER (PARTITION BY CustomerKey ORDER BY TransactionDate) AS GrpID
FROM @Sample
), cteYak(CustomerKey, TransactionDate, TransactionStatus, GrpID, SeqID)
AS (
SELECT CustomerKey,
TransactionDate,
TransactionStatus,
GrpID,
ROW_NUMBER() OVER (PARTITION BY CustomerKey, GrpID ORDER BY TransactionDate) -
ROW_NUMBER() OVER (PARTITION BY CustomerKey, GrpID ORDER BY TransactionStatus, TransactionDate) AS SeqID
FROM cteSource
)
SELECT CustomerKey,
TransactionDate,
TransactionStatus,
COUNT(*) OVER (PARTITION BY CustomerKey, GrpID, SeqID, TransactionStatus) AS Items
FROM cteYak
ORDER BY CustomerKey,
TransactionDate[/code]

N 56°04'39.26"
E 12°55'05.63"

esilva
Starting Member

4 Posts

Posted - 2011-01-13 : 17:31:10

Peso, thank you so much for the response. Was very happy to see your post, but when i tried working through the different scenario's in the data i see and applied them your script below does not work. I should have mentioned two more things. A customer may not have a transaction every day, AND the criteria is 3+ days. Therefore if i apply the following sample it does not work.


CustomerKey    TransactionDate     TransactionStatus
1              01/01/2010          Connected
1              01/02/2010          Disconnected
1              01/03/2010          Connected
2              01/01/2010          Connected
2              01/02/2010          Disconnected
2              01/03/2010          Connected          
2              01/05/2010          Connected
2              01/06/2010          Connected
2              01/07/2010          Disconnected

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-14 : 02:26:35

It does work! This is the result I get

CustomerKey  TransactionDate  TransactionStatus  Items
          1  20100101         Connected              1
          1  20100102         Disconnected           1
          1  20100103         Connected              1
          2  20100101         Connected              1
          2  20100102         Disconnected           1
          2  20100103         Connected              1
          2  20100105         Connected              2
          2  20100106         Connected              2
          2  20100107         Disconnected           1

What YOU have to do later, is to filter out the records you want, ie filter the Items column for values that are 3 or greater.

N 56°04'39.26"
E 12°55'05.63"

esilva
Starting Member

4 Posts

Posted - 2011-01-14 : 09:29:20

But in your results, it shows only value of two in the [items] field when for customer 2 it should a 4 in the items field (at the least) to show that the customer has been connected for 4 days (from Jan 1st through to Jan 5th).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-14 : 09:52:46

You stated succeding days, it means gaps are allowed?
If gaps are allowed, please remove the "DATEDIFF(DAY, 0, TransactionDate) - " part in the code.

N 56°04'39.26"
E 12°55'05.63"

esilva
Starting Member

4 Posts

Posted - 2011-01-14 : 18:04:51

Hi there, removing that part of the code gives me:


CustomerKey	TransactionDate	TransactionStatus	Items
1	        1/1/2010	Connected	        1
1	        1/2/2010	Disconnected	        1
1	        1/3/2010	Connected	        1
2	        1/1/2010	Connected	        1
2	        1/2/2010	Disconnected	        1
2	        1/3/2010	Connected	        1
2	        1/5/2010	Connected	        1
2	        1/6/2010	Connected	        1
2	        1/7/2010	Disconnected	        1

This is what it looks like now.


DECLARE	@Sample TABLE
	(
		CustomerKey INT,
		TransactionDate DATETIME,
		TransactionStatus VARCHAR(12)
	)

INSERT	@Sample
SELECT	1, '20100101', 'Connected' UNION ALL
SELECT	1, '20100102', 'Disconnected' UNION ALL
SELECT	1, '20100103', 'Connected' UNION ALL
SELECT	2, '20100101', 'Connected' UNION ALL
SELECT	2, '20100102', 'Disconnected' UNION ALL
SELECT	2, '20100103', 'Connected' UNION ALL
SELECT	2, '20100105', 'Connected' UNION ALL
SELECT	2, '20100106', 'Connected' UNION ALL
SELECT	2, '20100107', 'Disconnected'

;WITH cteSource(CustomerKey, TransactionDate, TransactionStatus, GrpID)
AS (
	SELECT	CustomerKey,
		TransactionDate,
		TransactionStatus,
		ROW_NUMBER() OVER (PARTITION BY CustomerKey ORDER BY TransactionDate) AS GrpID
	FROM	@Sample
), cteYak(CustomerKey, TransactionDate, TransactionStatus, GrpID, SeqID)
AS (
	SELECT	CustomerKey,
		TransactionDate,
		TransactionStatus,
		GrpID,
		ROW_NUMBER() OVER (PARTITION BY CustomerKey, GrpID ORDER BY TransactionDate) -
		ROW_NUMBER() OVER (PARTITION BY CustomerKey, GrpID ORDER BY TransactionStatus, TransactionDate) AS SeqID
	FROM	cteSource
)
SELECT		CustomerKey,
		TransactionDate,
		TransactionStatus,
		COUNT(*) OVER (PARTITION BY CustomerKey, GrpID, SeqID, TransactionStatus) AS Items
FROM		cteYak
ORDER BY	CustomerKey,
		TransactionDate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources