August 21, 2013 at 2:44 pm
Hi,
I'm trying to create a unique id, it's easier to show than explain i think...
So I need the result to show the UNIQUE_ID as shown below...
I thought one of the rank functions might work, but I'm not sure how to use a CASE Statement within it, or even if a CASE is allowed???
ACTIVITY_ID is the key... also, pat_id1 is patients, so I can have many unique PAT_ID1 (it doesn't matter to me when doing the unique_id if the count starts over at each new pat_id1 is continues the count)
Appreciate any ideas!
Thanks,
John
PAT_ID1 COMPLETION DATE ACTIVITY ACTIVITY_ID LEAD_ACTIVITY_ID UNIQUE_ID
10910 1/7/2011 Referral Date 1 NULL 1
10910 1/7/2011 Billing Start Date 3 4 1
10910 4/29/2011 Billing End Date 4 3 1
10910 5/25/2011 Billing Start Date 3 4 2
10910 6/30/2011 Billing End Date 4 3 2
10910 7/25/2011 Billing Start Date 3 4 3
10910 8/26/2011 Billing End Date 4 3 3
10910 10/17/2011 Billing Start Date 3 4 4
10910 10/31/2011 Billing End Date 4 3 4
10910 11/9/2011 Billing Start Date 3 4 5
10910 11/24/2011 Billing End Date 4 NULL 5
CREATE TABLE #TEMP_RANK
(
PAT_ID1 INT
, COMPLETION_DATE DATETIME
, ACTIVITY VARCHAR(100)
, ACTIVITY_ID INT
, LEAD_ACTIVITY_ID INT
)
INSERT INTO #TEMP_RANK (PAT_ID1, COMPLETION_DATE, ACTIVITY, ACTIVITY_ID, LEAD_ACTIVITY_ID) VALUES (10910,CAST('1/7/2011' AS DATE), 'Referral Date', 1, NULL)
INSERT INTO #TEMP_RANK (PAT_ID1, COMPLETION_DATE, ACTIVITY, ACTIVITY_ID, LEAD_ACTIVITY_ID) VALUES (10910,CAST('1/7/2011' AS DATE), 'Billing Start Date', 3, 4)
INSERT INTO #TEMP_RANK (PAT_ID1, COMPLETION_DATE, ACTIVITY, ACTIVITY_ID, LEAD_ACTIVITY_ID) VALUES (10910,CAST('4/29/2011' AS DATE), 'Billing End Date', 4, 3)
INSERT INTO #TEMP_RANK (PAT_ID1, COMPLETION_DATE, ACTIVITY, ACTIVITY_ID, LEAD_ACTIVITY_ID) VALUES (10910,CAST('5/25/2011' AS DATE), 'Billing Start Date', 3, 4)
INSERT INTO #TEMP_RANK (PAT_ID1, COMPLETION_DATE, ACTIVITY, ACTIVITY_ID, LEAD_ACTIVITY_ID) VALUES (10910,CAST('6/30/2011' AS DATE), 'Billing End Date', 4, 3)
INSERT INTO #TEMP_RANK (PAT_ID1, COMPLETION_DATE, ACTIVITY, ACTIVITY_ID, LEAD_ACTIVITY_ID) VALUES (10910,CAST('7/25/2011' AS DATE), 'Billing Start Date', 3, 4)
INSERT INTO #TEMP_RANK (PAT_ID1, COMPLETION_DATE, ACTIVITY, ACTIVITY_ID, LEAD_ACTIVITY_ID) VALUES (10910,CAST('8/26/2011' AS DATE), 'Billing End Date', 4, 3)
INSERT INTO #TEMP_RANK (PAT_ID1, COMPLETION_DATE, ACTIVITY, ACTIVITY_ID, LEAD_ACTIVITY_ID) VALUES (10910,CAST('10/17/2011' AS DATE), 'Billing Start Date', 3, 4)
INSERT INTO #TEMP_RANK (PAT_ID1, COMPLETION_DATE, ACTIVITY, ACTIVITY_ID, LEAD_ACTIVITY_ID) VALUES (10910,CAST('10/31/2011' AS DATE), 'Billing End Date', 4, 3)
INSERT INTO #TEMP_RANK (PAT_ID1, COMPLETION_DATE, ACTIVITY, ACTIVITY_ID, LEAD_ACTIVITY_ID) VALUES (10910,CAST('11/9/2011' AS DATE), 'Billing Start Date', 3, 4)
INSERT INTO #TEMP_RANK (PAT_ID1, COMPLETION_DATE, ACTIVITY, ACTIVITY_ID, LEAD_ACTIVITY_ID) VALUES (10910,CAST('11/24/2011' AS DATE), 'Billing End Date', 4, NULL)
August 21, 2013 at 3:04 pm
Please provide the expected output for the sample data you provided. Also, you may want to include a second PAT worth of data so we can understand what you intend to happen when that value changes. (same can be said for every field where a value change is meaningful to your expected output).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 21, 2013 at 3:07 pm
I don't see what columns make up the row unique? Do you want to match a Start Date with an End Date? That's what it looks like to me. With every referral being on it's own.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 21, 2013 at 6:38 pm
I suspect there's probably more business rules at play here than my simplistic solution addresses, but maybe it will give you an idea.
SELECT *
,UNIQUE_ID=CASE ACTIVITY_ID WHEN 1 THEN 1
ELSE ROW_NUMBER() OVER (PARTITION BY PAT_ID1 ORDER BY COMPLETION_DATE)/2 END
FROM #TEMP_RANK;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 22, 2013 at 6:30 am
Dwain,
Thats perfect... I never ever thought to divide by 2... Genius you are!!! lol
I appreciate everyones reply's as well!!!
Thank you,
John
August 22, 2013 at 5:43 pm
And thank you John for taking the time to let us know you got something you could use out of it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply