What is wring with my SQL ? Why am I getting the same item repeated ?

Question

What is wring with my SQL ? Why am I getting the same item repeated ?

mw_sql_developer

SSCoach

Points: 19445
More actions
June 24, 2015 at 9:24 am

#305332

INSERT INTO #findings( PatientID, Summary )
SELECT
DC.PatientID,
STUFF( (SELECT ',[<' + CONVERT( CHAR(8), s.ActivityDate, 112 ) + '>' + s.TermDescription + ']'
FROM rdw_views.dbo.DiagnosesComprehensive as s inner join #t T on ( T.RDW_PATIENT_ID = s.PatientID )
WHERE
s.PatientID = DC.PatientID
and
(
s.TermCodeMapped LIKE '415.1%'
OR
s.TermCodeMapped LIKE '453.2'
OR
s.TermCodeMapped LIKE '453.4%'
OR
s.TermCodeMapped LIKE '453.6'
OR
s.TermCodeMapped LIKE '453.8%'
)
and
s.ActivityDate between DATEADD(DAY,-10, T.AIMS_Scheduled_DT ) AND DATEADD(DAY,10, T.AIMS_Scheduled_DT )
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1, 2, '') as PE_DVT
FROM rdw_views.dbo.DiagnosesComprehensive DC inner join #t T on ( T.RDW_PATIENT_ID = DC.PatientID )
WHERE
(
DC.TermCodeMapped LIKE '415.1%'
OR
DC.TermCodeMapped LIKE '453.2'
OR
DC.TermCodeMapped LIKE '453.4%'
OR
DC.TermCodeMapped LIKE '453.6'
OR
DC.TermCodeMapped LIKE '453.8%'
)
and
DC.ActivityDate between DATEADD(DAY,-10, T.AIMS_Scheduled_DT ) AND DATEADD(DAY,10, T.AIMS_Scheduled_DT )
GROUP BY
DC.PatientID
See the attached file for the results
I don't like the fact that items are getting repeated.
The idea of using the STUFF function is to CONCATENATE entries into one row.
Help

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Your view is apparently returning duplicate diagnoses. Not strange since you're looking at multiple activity dates, each of which might create a separate description of the diagnoses (thus duplicating the diagnosis code). The STUFF is returning a single concatenated list,but isn't set up to decide what to include or not (i.e. any entry it scans through will be appended, duplicate or no).

You'd have to insert a DISTINCT somewhere (not sure if that should belong in your view or within this particular query.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

Quick thought, you need to have two from clauses, one for the XML and one for the outer query and then link them with a where clause in the inner query.

😎

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

I think your query should look something like this:

SELECT

T.RDW_PATIENT_ID,

x.PE_DVT

FROM #t T

CROSS APPLY (

SELECT STUFF( (

SELECT ',[<' + CONVERT( CHAR(8), s.ActivityDate, 112 ) + '>' + s.TermDescription + ']'

FROM rdw_views.dbo.DiagnosesComprehensive as s

WHERE ( T.RDW_PATIENT_ID = s.PatientID )

and s.ActivityDate between DATEADD(DAY,-10, T.AIMS_Scheduled_DT ) AND DATEADD(DAY,10, T.AIMS_Scheduled_DT )

and

(

s.TermCodeMapped LIKE '415.1%'

OR

s.TermCodeMapped LIKE '453.2'

OR

s.TermCodeMapped LIKE '453.4%'

OR

s.TermCodeMapped LIKE '453.6'

OR

s.TermCodeMapped LIKE '453.8%'

)

FOR XML PATH(''), TYPE).value('.', 'varchar(max)')

,1, 2, '') as PE_DVT

) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mw_sql_developer SSCoach Points: 19445 More actions · Answer 4

Lets close the case folks!

TO begin with there were duplicate entries in one of the tables.

THE SQL syntax was fine!

Sorry for your time.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

mw112009 (6/24/2015)
...THE SQL syntax was fine!

The query is at stage 1 of the three stages of SQL development:

1. Make it work

2. Make it fast

3. Make it pretty (formatting and documentation)

Don't forget to finish it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden