April 2, 2012 at 11:07 am
I'm rewriting a cursor-riddled SP and I'm stuck on one particular part.
Basic testbed:
CREATE TABLE #outpatient_service
(
[key_id] [int] IDENTITY(1, 1)
NOT NULL ,
[auth_id] [int] NULL ,
[cpt4_id] [char](30) NULL
)
CREATE TABLE #TRANS
(
[auth_id] INT ,
[auth_date] DATETIME
)
INSERT INTO #TRANS
( auth_id ,
auth_date
)
SELECT 95412 , GETDATE()
UNION
SELECT 95410 , GETDATE()
UNION
SELECT 95406 , GETDATE()
UNION
SELECT 95405 , GETDATE()
UNION
SELECT 95402 , GETDATE()
UNION
SELECT 95385 , GETDATE()
INSERT INTO #outpatient_service
( auth_id ,
cpt4_id
)
SELECT 95412 , '00148'
UNION
SELECT 95410 , '95810'
UNION
SELECT 95410 , 'NULL'
UNION
SELECT 95407 , '93.22'
UNION
SELECT 95406 , '92611'
UNION
SELECT 95406 , 'NULL'
UNION
SELECT 95405 , '92559'
UNION
SELECT 95402 , '22222'
UNION
SELECT 95385 , '64614'
UNION
SELECT 95385 , 'J0585'
UNION
SELECT 95400 , 'e0935'
Here's the basic code (note that generally @auth_id is set via a cursor)
DECLARE @auth_id INT
SET @AUTH_ID = 95385
SELECT UPPER(cpt4_id)
FROM #outpatient_service
WHERE auth_id = @auth_id
AND key_id IN ( SELECT MIN(key_id)
FROM #outpatient_service
WHERE auth_id = @auth_id )
Expected result for above would be '64614'
Goal: change "WHERE auth_id = @auth_id" to an inner join with the #TRANS table and get rid of that subquery.
Help?
April 2, 2012 at 11:16 am
Pam Brisjar (4/2/2012)
Goal: change "WHERE auth_id = @auth_id" to an inner join with the #TRANS table and get rid of that subquery.
Help?
question where in the query is the #TRANS table. right now its a sub query to #outpatient_service?
Did you miss part of your query when paring it down for public consumption?
EDIT:
you want the join to trans to get the latest or earliest auth code by date?
also i see in your sample data
SELECT 95385 , '64614'
UNION
SELECT 95385 , 'J0585'
Is that correct for for 2 cpt4_id to have the same auth_id?
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 2, 2012 at 11:26 am
Data is correct. Each auth_id can have 1 - many entries in the #outpatient_service table.
For every auth in #TRANS, I need to get the first cpt entered based on the key_id column (lowest value).
Date in the #TRANS table is irrelevant for this particular query.
April 2, 2012 at 11:31 am
DECLARE @auth_id INT
SET @AUTH_ID = 95385
SELECT UPPER(cpt4_id)
FROM #outpatient_service os1
INNER JOIN
(SELECT MIN(key_id) AS min_key_id
FROM #outpatient_service
WHERE auth_id = @auth_id) os2
ON os1.key_id = os2.key_id
WHERE os1.auth_id = @auth_id
Does this work for you?
Jared
CE - Microsoft
April 2, 2012 at 11:32 am
You can also do the same, but move the query to a CTE to generate the min numbers, then join.
Jared
CE - Microsoft
April 2, 2012 at 11:35 am
SQLKnowItAll (4/2/2012)
DECLARE @auth_id INTSET @AUTH_ID = 95385
SELECT UPPER(cpt4_id)
FROM #outpatient_service os1
INNER JOIN
(SELECT MIN(key_id) AS min_key_id
FROM #outpatient_service
WHERE auth_id = @auth_id) os2
ON os1.key_id = os2.key_id
WHERE os1.auth_id = @auth_id
Does this work for you?
No, because I need the INNER JOIN to the #TRANS table to replace the @auth_id variable
April 2, 2012 at 11:40 am
;WITH row AS (SELECT ROW_NUMBER() OVER (Partition by auth_id ORDER BY key_id) AS rownum, cpt4_id, auth_id FROM #outpatient_service)
SELECT UPPER(cpt4_id), auth_id FROM row where rownum = 1
GO
no inner join to the trans table but gets what you want out of the #outpatient_service I think
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 2, 2012 at 11:42 am
Ahh... Now I see. Try this.
SELECT os1.auth_id, os2.min_key_id, UPPER(os2.cpt4_id)
FROM #outpatient_service os1
INNER JOIN #TRANS t
ON os1.auth_id = t.auth_id
INNER JOIN (SELECT auth_id, MIN(key_id) AS min_key_id
FROM #outpatient_service
GROUP BY auth_id) os2
ON os1.key_id = os2.min_key_id
AND os1.auth_id = os2.auth_id
Jared
CE - Microsoft
April 2, 2012 at 11:43 am
i was wrong, here it is with the join and only pulling authid's in the trans table
;WITH row AS (SELECT ROW_NUMBER() OVER (Partition by auth_id ORDER BY key_id) AS rownum, cpt4_id, auth_id FROM #outpatient_service)
SELECT UPPER(cpt4_id), r.auth_id FROM row r
INNER JOIN #TRANS t
ON r.auth_id = t.auth_id
where rownum = 1
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 2, 2012 at 11:46 am
capn.hector (4/2/2012)
i was wrong, here it is with the join and only pulling authid's in the trans table
;WITH row AS (SELECT ROW_NUMBER() OVER (Partition by auth_id ORDER BY key_id) AS rownum, cpt4_id, auth_id FROM #outpatient_service)SELECT UPPER(cpt4_id), r.auth_id FROM row r
INNER JOIN #TRANS t
ON r.auth_id = t.auth_id
where rownum = 1
Ha! Nice! Realized after I posted that I did not need to do a self-join for this...
Jared
CE - Microsoft
April 2, 2012 at 11:47 am
SQLKnowItAll (4/2/2012)
capn.hector (4/2/2012)
i was wrong, here it is with the join and only pulling authid's in the trans table
;WITH row AS (SELECT ROW_NUMBER() OVER (Partition by auth_id ORDER BY key_id) AS rownum, cpt4_id, auth_id FROM #outpatient_service)SELECT UPPER(cpt4_id), r.auth_id FROM row r
INNER JOIN #TRANS t
ON r.auth_id = t.auth_id
where rownum = 1
Ha! Nice! Realized after I posted that I did not need to do a self-join for this...
thought of something else. moving the join to the cte cuts the rows we number over
;WITH row AS (SELECT ROW_NUMBER() OVER (Partition by s.auth_id ORDER BY key_id) AS rownum, cpt4_id, s.auth_id
FROM #outpatient_service s
INNER JOIN #TRANS t
ON s.auth_id = t.auth_id)
SELECT UPPER(cpt4_id), r.auth_id FROM row r
where rownum = 1
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 2, 2012 at 11:51 am
SELECT UPPER(cpt4_id)
FROM #TRANS T
INNER JOIN #outpatient_service OS ON T.auth_id = OS.auth_id AND
OS.key_id = (SELECT MIN(key_id)
FROM #outpatient_service OS2
WHERE OS.auth_id = OS2.auth_id)
WHERE T.auth_id = @auth_id
April 2, 2012 at 11:51 am
Oooh, nice.
Thanks.
Bogged down in a lot of muck and brain's on overload.
April 2, 2012 at 11:52 am
capn.hector (4/2/2012)
SQLKnowItAll (4/2/2012)
capn.hector (4/2/2012)
i was wrong, here it is with the join and only pulling authid's in the trans table
;WITH row AS (SELECT ROW_NUMBER() OVER (Partition by auth_id ORDER BY key_id) AS rownum, cpt4_id, auth_id FROM #outpatient_service)SELECT UPPER(cpt4_id), r.auth_id FROM row r
INNER JOIN #TRANS t
ON r.auth_id = t.auth_id
where rownum = 1
Ha! Nice! Realized after I posted that I did not need to do a self-join for this...
thought of something else. moving the join to the cte cuts the rows we number over
;WITH row AS (SELECT ROW_NUMBER() OVER (Partition by s.auth_id ORDER BY key_id) AS rownum, cpt4_id, s.auth_idFROM #outpatient_service s
INNER JOIN #TRANS t
ON s.auth_id = t.auth_id)
SELECT UPPER(cpt4_id), r.auth_id FROM row r
where rownum = 1
Heh... Too bad we can't filter here without the CTE or a temp table. Nicely done!
Jared
CE - Microsoft
April 2, 2012 at 11:53 am
this should work fine...
SELECT UPPER(os.cpt4_id)
FROM #TRANS t
CROSS APPLY ( SELECT TOP 1 cpt4_id
FROM #outpatient_service
WHERE auth_id = t.auth_id
ORDER BY Key_id
) os
--WHERE auth_id = @auth_id
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply