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 2012 Forums
 Transact-SQL (2012)
 Finding first and repeated values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Skorch
Constraint Violating Yak Guru

USA
300 Posts

Posted - 08/25/2014 :  13:01:51  Show Profile  Reply with Quote
Hey guys,

It's been a while since I've worn my developer hat, and this problem has got me stumped. I'm trying to find the first visit and repeat visit with the following data:

CREATE TABLE #Visits (OpportunityID int, ActivityID int, FirstVisit date, ScheduledEnd datetime, isFirstVisit bit, isRepeatVisit bit)


INSERT #Visits (OpportunityID, ActivityID, FirstVisit, ScheduledEnd)
SELECT 1, 1001, '2014-08-17', '2014-08-17 12:00:00.000' UNION ALL
SELECT 1, 1002, '2014-08-17', '2014-08-17 17:04:13.000' UNION ALL
SELECT 2, 1003, '2014-08-18', '2014-08-18 20:39:56.000' UNION ALL
SELECT 2, 1004, '2014-08-18', '2014-08-18 18:00:00.000' UNION ALL
SELECT 3, 1005, '2014-07-27', '2014-08-01 12:00:00.000' UNION ALL
SELECT 3, 1006, '2014-07-27', '2014-08-14 12:00:00.000' UNION ALL
SELECT 4, 1007, '2014-07-27', '2014-08-14 12:00:00.000' UNION ALL
SELECT 5, 1008, '2014-07-27', '2014-07-27 12:00:00.000' UNION ALL
SELECT 5, 1009, '2014-07-27', '2014-08-12 12:00:00.000' UNION ALL
SELECT 5, 1010, '2014-07-27', '2014-08-13 12:00:00.000'


Here are the expected results:

OpportunityID ActivityID FirstVisit ScheduledEnd isFirstVisit isRepeatVisit
1 1001 2014-08-17 2014-08-17 12:00:00.000 1 0
1 1002 2014-08-17 2014-08-17 17:04:13.000 0 1
2 1003 2014-08-18 2014-08-18 20:39:56.000 0 1
2 1004 2014-08-18 2014-08-18 18:00:00.000 1 0
3 1005 2014-07-27 2014-08-01 12:00:00.000 0 1
3 1006 2014-07-27 2014-08-14 12:00:00.000 0 1
4 1007 2014-07-27 2014-08-14 12:00:00.000 0 1
5 1008 2014-07-27 2014-07-27 12:00:00.000 1 0
5 1009 2014-07-27 2014-08-12 12:00:00.000 0 1
5 1010 2014-07-27 2014-08-13 12:00:00.000 0 1

Basically I want to take the earliest ScheduledEnd that falls on the same date as the FirstVisit and mark it as true in the isFirstVisit column. Otherwise the row should be marked as a repeat visit.

I'm thinking to use the ROW_NUMBER() function here but I'm not sure exactly how. Thanks in advance for the help - you guys rock!

Some days you're the dog, and some days you're the fire hydrant.

Edited by - Skorch on 08/25/2014 13:07:58

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 08/25/2014 :  14:43:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Something similar to this?
SELECT	*,
	CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit)
		WHEN ScheduledEnd THEN 1
		ELSE 0
	END AS IsFirstVisit,
	CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit)
		WHEN ScheduledEnd THEN 0
		ELSE 1
	END AS IsRepeatVisit
FROM	#Visits;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Skorch
Constraint Violating Yak Guru

USA
300 Posts

Posted - 08/25/2014 :  15:43:54  Show Profile  Reply with Quote
That's perfect. Thank you so much!

Some days you're the dog, and some days you're the fire hydrant.
Go to Top of Page

Skorch
Constraint Violating Yak Guru

USA
300 Posts

Posted - 08/25/2014 :  16:41:58  Show Profile  Reply with Quote
I think I may have jumped the gun a bit there. Upon doing some testing, some of the values are falsely identifying as first visits. I only want the first visit to count if the SheduledEnd falls on the same date as the FirstVisit. Please look at these last two test cases:

CREATE TABLE #Visits (OpportunityID int, ActivityID int, FirstVisit date, ScheduledEnd datetime, isFirstVisit bit, isRepeatVisit bit)


INSERT #Visits (OpportunityID, ActivityID, FirstVisit, ScheduledEnd)
SELECT 1, 1001, '2014-08-17', '2014-08-17 12:00:00.000' UNION ALL
SELECT 1, 1002, '2014-08-17', '2014-08-17 17:04:13.000' UNION ALL
SELECT 2, 1003, '2014-08-18', '2014-08-18 20:39:56.000' UNION ALL
SELECT 2, 1004, '2014-08-18', '2014-08-18 18:00:00.000' UNION ALL
SELECT 3, 1005, '2014-07-27', '2014-08-01 12:00:00.000' UNION ALL
SELECT 3, 1006, '2014-07-27', '2014-08-14 12:00:00.000' UNION ALL
SELECT 4, 1007, '2014-07-27', '2014-08-14 12:00:00.000' UNION ALL
SELECT 5, 1008, '2014-07-27', '2014-07-27 12:00:00.000' UNION ALL
SELECT 5, 1009, '2014-07-27', '2014-08-12 12:00:00.000' UNION ALL
SELECT 5, 1010, '2014-07-27', '2014-08-13 12:00:00.000' UNION ALL
SELECT 6, 1011, '2014-06-16', '2014-08-10 12:00:00.000' UNION ALL
SELECT 6, 1012, '2014-06-16', '2014-08-17 12:00:00.000'


The ActivityID of 1011 is being marked as a first visit with the code, when the FirstVisit happened two months prior.

Some days you're the dog, and some days you're the fire hydrant.
Go to Top of Page

Skorch
Constraint Violating Yak Guru

USA
300 Posts

Posted - 08/26/2014 :  11:44:02  Show Profile  Reply with Quote
I hope me posting that Peso's solution worked earlier doesn't cause this topic to be overlooked. Would love someone to help if possible..

Some days you're the dog, and some days you're the fire hydrant.
Go to Top of Page

Skorch
Constraint Violating Yak Guru

USA
300 Posts

Posted - 08/26/2014 :  12:34:58  Show Profile  Reply with Quote
Here is what I was able to come up with and I believe it now works:

SELECT
	OpportunityID,
	ActivityID,
	FirstVisit,
	ScheduledEnd,
	CASE
		WHEN CONVERT(DATE, ScheduledEnd) = FirstVisit 
		THEN
			CASE MIN(ScheduledEnd) OVER (PARTITION BY OpportunityID)
				WHEN ScheduledEnd 
				THEN 1 
				ELSE 0 
			END
		ELSE 0
	END AS isFirstVisit,
	CASE
		WHEN CONVERT(DATE, ScheduledEnd) = FirstVisit 
		THEN
			CASE MIN(ScheduledEnd) OVER (PARTITION BY OpportunityID) 
				WHEN ScheduledEnd
				THEN 0
				ELSE 1
			END
		ELSE 1
	END AS RepeatVisit
FROM #Visits
ORDER BY OpportunityID, ActivityID


Thanks again guys!

Some days you're the dog, and some days you're the fire hydrant.
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.06 seconds. Powered By: Snitz Forums 2000