August 26, 2014 at 9:56 am
Hi guys, I'm trying to come up with a query for this 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' 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'
Here are the expected results:
OpportunityIDActivityIDFirstVisitScheduledEndisFirstVisitisRepeatVisit
110012014-08-172014-08-17 12:00:00.00010
110022014-08-172014-08-17 17:04:13.00001
210032014-08-182014-08-18 20:39:56.00001
210042014-08-182014-08-18 18:00:00.00010
310052014-07-272014-08-01 12:00:00.00001
310062014-07-272014-08-14 12:00:00.00001
410072014-07-272014-08-14 12:00:00.00001
510082014-07-272014-07-27 12:00:00.00010
510092014-07-272014-08-12 12:00:00.00001
510102014-07-272014-08-13 12:00:00.00001
610112014-06-162014-08-10 12:00:00.00001
610122014-06-162014-08-17 12:00:00.00001
Basically I'd like to mark the first Activity for each OpportunityID as a First Visit if its ScheduledEnd falls on the same day as the FirstVisit, and otherwise mark it as a Repeat Visit.
I have this so far, but it doesn't pick up on that the ScheduledEnd needs to be on the same day as the FirstVisit date to count as a first visit:
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
I really appreciate any help on this!
August 26, 2014 at 10:12 am
Something like this?
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER(PARTITION BY FirstVisit ORDER BY ScheduledEnd) rn
FROM #Visits
)
SELECT OpportunityID,
ActivityID,
FirstVisit,
ScheduledEnd,
CASE WHEN rn = 1 AND FirstVisit = CAST( ScheduledEnd AS date) THEN 1 ELSE 0 END,
CASE WHEN rn > 1 OR FirstVisit < CAST( ScheduledEnd AS date) THEN 1 ELSE 0 END
FROM CTE
ORDER BY ActivityID
August 26, 2014 at 10:33 am
Thank you so much - that works!
Meanwhile I was playing with my own code, and I think I got an alternate solution using nested cases:
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
Would one be a recommended approach over the other?
August 26, 2014 at 10:53 am
skorcher (8/26/2014)
Thank you so much - that works!...
Would one be a recommended approach over the other?
You're welcome.
My recommendation would be to test both approaches. If both return the same results and have similar performance, use the one you understand better.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply