Finding first and repeated values

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 2012 Forums

Transact-SQL (2012)

Finding first and repeated values

Author

Topic

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2014-08-25 : 13:01:51

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:

[CODE]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' [/CODE]

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-25 : 14:43:54

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

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2014-08-25 : 15:43:54

That's perfect. Thank you so much!

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

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2014-08-25 : 16:41:58

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.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2014-08-26 : 11:44:02

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.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2014-08-26 : 12:34:58

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources