January 26, 2011 at 6:24 am
Hi
I have the following script. The main body give me the correct answer but i have more than one row per patient.
I need the result to give me a distinct list on the UKEY field
I have added a #temp table and done it that way but is there a way of incorporating it into the main script????????????????????????????????
Thanks in advance
SELECT tbl_PS_IXP_MEDREC_PROCS.crn +':' + tbl_PS_IXP_MEDREC_PROCS.admission AS UKEY, tbl_PS_IXP_MEDREC_PROCS.admdate_dte,
tbl_PS_IXP_MEDREC_PROCS.ADM_YEAR, tbl_PS_IXP_MEDREC_PROCS.ADM_MONTH, tbl_PS_IXP_MEDREC_PROCS.ADM_MONTH_NUMBER,
tbl_PS_IXP_MEDREC_PROCS.disdate_dte, tbl_PS_IXP_MEDREC_PROCS.DIS_YEAR, tbl_PS_IXP_MEDREC_PROCS.DIS_MONTH,
tbl_PS_IXP_MEDREC_PROCS.DIS_MONTH_NUMBER, tbl_PS_IXP_MEDREC_PROCS.[med rec epi],
tbl_PS_IXP_MEDREC_PROCS.EPISODE_START_DATE, tbl_PS_IXP_MEDREC_PROCS.EPISODE_END_DATE, tbl_PS_IXP_MEDREC_PROCS.opcscode,
tbl_PS_IXP_MEDREC_DIAG.ICD_POSITION, tbl_PS_IXP_MEDREC_PROCS.OPCS_Desc, tbl_PS_IXP_MEDREC_PROCS.[PROC POSITION],
tbl_PS_IXP_MEDREC_PROCS.hrgcode, tbl_PS_IXP_MEDREC_PROCS.HRG_DESC, tbl_PS_IXP_MEDREC_PROCS.[SPELL LOS],
tbl_PS_IXP_MEDREC_PROCS.intmgt, tbl_PS_IXP_MEDREC_PROCS.INTMGT_DESC, tbl_PS_IXP_MEDREC_PROCS.admtype,
tbl_PS_IXP_MEDREC_PROCS.ADM_TYPE_DESC, tbl_PS_IXP_MEDREC_PROCS.operationdate_dte, tbl_PS_IXP_MEDREC_PROCS.surgeon,
tbl_PS_IXP_MEDREC_PROCS.SURGEON_SURN, tbl_PS_IXP_MEDREC_PROCS.specialty, tbl_PS_IXP_MEDREC_PROCS.SPEC_DESC,
tbl_PS_IXP_MEDREC_PROCS.consultant, tbl_PS_IXP_MEDREC_PROCS.CONS_SURN, tbl_PS_IXP_MEDREC_PROCS.discode,
tbl_PS_IXP_MEDREC_DIAG.ICD_DESC, tbl_PS_IXP_MEDREC_DIAG.icdcode, R_FD_BasketOf25_Daycases.Basket_of_25
into #pstemp
FROM tbl_PS_IXP_MEDREC_PROCS INNER JOIN
tbl_PS_IXP_MEDREC_DIAG ON tbl_PS_IXP_MEDREC_PROCS.crn = tbl_PS_IXP_MEDREC_DIAG.crn AND
tbl_PS_IXP_MEDREC_PROCS.admission = tbl_PS_IXP_MEDREC_DIAG.admission AND
tbl_PS_IXP_MEDREC_PROCS.[med rec epi] = tbl_PS_IXP_MEDREC_DIAG.[med rec epi] INNER JOIN
R_FD_BasketOf25_Daycases ON tbl_PS_IXP_MEDREC_PROCS.opcscode = R_FD_BasketOf25_Daycases.OPCS_Code
WHERE (tbl_PS_IXP_MEDREC_PROCS.opcscode BETWEEN 'W82%' AND 'W89%') AND (tbl_PS_IXP_MEDREC_DIAG.ICD_POSITION = 1) AND
(tbl_PS_IXP_MEDREC_PROCS.EPISODE_END_DATE between '2010-04-01' and '2010-04-30')and
tbl_PS_IXP_MEDREC_PROCS.[SPELL LOS] = 0
----------------------------------------
-----------------------------------------
select distinct UKEY
from #pstemp
order by #pstemp.ukey
drop table #pstemp
January 26, 2011 at 7:28 am
If you only need the list of UKEY, why are you selecting all the other columns?
Not having all your code to understand completely what's going on, I may get this wrong.
What I'd do, based on what you've described and shown, is use this as a derived table, not a sub-select. As a derived table you can join against it with the tables in the rest of the query to get what you're looking for.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 26, 2011 at 7:41 am
Without seeing table definitions, I can only suggest:
a)If you are getting duplicates, then you are probably missing a condition on one of your joins.
b)If any of your tables allow one to many relationships, then you need to refine your query.
January 26, 2011 at 7:49 am
I have sorted this now, thanks for you advice.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply