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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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