Data not pivoting as expected 1

[waubain]

I am not quite sure where I am going wrong. I am trying to disply if a person is on more than one drug it displays on one line with a YES if they are and a blank if they are not. Each drug is a separate column. My problem is when a person is on more than one drug both columns are blank rather than a YES in each column. I think my problem lies with the second query rather than the final query.

#cohort is previous queried and stored in a temp table for now.

When I run the first SELECT independently the data looks like this:
[tt]
PatientSID DrugNameWithoutDoseSID RowId
16051414 308716 1
16051414 308716 2
16051414 308716 3
16051414 321133 1
16066127 308716 1
16066127 308716 2
16067974 321133 1
16157440 321133 1
25397863 308716 1
[/tt]

In this example personID 16051414 is the only person on 2 drug.

Here is my code:

; With Drug AS
(
SELECT 
      rx.PatientSID
	  ,drugid.DrugNameWithoutDoseSID
	  ,ROW_NUMBER() OVER (PARTITION BY rx.PatientSID, 
                                            drugid.DrugNameWithoutDoseSID
			    ORDER BY     rx.PatientSID 
                              ) As RowId 
FROM 
	  RxOut.RxOutpat	AS rx
	  INNER JOIN #cohort	 AS cohort	ON rx.PatientSID = cohort.PatientSID
      INNER JOIN Dim.NationalDrug    AS drugid	ON rx.NationalDrugSID  = drugid.NationalDrugSID
WHERE
	  drugid.DrugNameWithoutDoseSID IN (321133, 308716, 309474) 
	  AND rx.IssueDate Between '2008-01-01' AND '2014-03-12'
	  AND rx.RxStatus IN ('ACTIVE', 'SUSPENDED','EXPIRED', 'HOLD')
)
,
MinDrug AS
(  
SELECT 
		Drug.PatientSID
		,MIN(Case When drug.DrugNameWithoutDoseSID = 321133
                 Then 'YES'
	        ELSE '' 
                 End) As [GLIPIZIDE]
                 ,MIN(Case When drug.DrugNameWithoutDoseSID = 308716
                 Then 'YES'
	        ELSE '' 
                 End) As [METFORMIN]
		,MIN(Case When drug.DrugNameWithoutDoseSID = 309474
                 Then 'YES' 
	        ELSE ''
                 End) As [GLIP/MET COMBO]
FROM Drug
WHERE	RowID = 1
GROUP BY 
	Drug.PatientSID
)
SELECT 
	cohort.PatientSID
	,mindrug.[Glipizide]
	,mindrug.[Metformin]
	,mindrug.[Glip/Met Combo]
FROM #cohort AS cohort
	INNER JOIN MinDrug		ON cohort.PatientSID = mindrug.PatientSID
ORDER BY
	cohort.PatientSID

The above gives this output. As you can see person 16051414 has a blank under both Glipizide and Metformin.

[tt]
PatientSID Glipizide Metformin Glip/Met Combo
16051414
16066127 YES
16067974 YES
16157440 YES
25397863 YES
[/tt]

Thanks for any suggestions.



You don't know what you don't know...

 

[imex]

Try changing the "Min" function by "Max" ('Yes' is greater than '').

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[Olaf Doschke]

Just to state the obvious: If your data isn't voting correctly, the campaign had errors

Serious: Your MinDrug query with the Glipizide Metformin Glip/Met Combo columns always just analyses WHERE RowID = 1, therefore you only ever will get one YES. Remove that WHERE, you only get one row per PAtient ID as you group by it.

Bye, Olaf.

 

[waubain]

Imex: That did the trick.

Olaf: At this point in my SQL learning curve nothing is obvious. I knew what the acroymn SQL meant to I was volunteered to put my finger in the dike while waiting for help . I appreciate the obvious, although I am not sure why in this example I don't need it, but in other queries of the same type I need RowID = 1, based on examples provided in a past thread.

Thanks again to the both of you.

You don't know what you don't know...

 

[Olaf Doschke]

Well, I'll have to go into detail myself:

16051414 308716 1
16051414 308716 2
16051414 308716 3
16051414 321133 1
16066127 308716 1
16066127 308716 2
16067974 321133 1
16157440 321133 1
25397863 308716 1

The third value is the RowID. In case you want to get aggregated values for all records, you don't want to filter by rowid, otherwise your overall result is just aggregated (grouped) from these records:
16051414 308716 1
16051414 321133 1
16066127 308716 1
16067974 321133 1
16157440 321133 1
25397863 308716 1

Indeed that would also give the right result as now any combination of PatientID and DrugNameWithoutDoseSID is only in there once.
The change to MAX instead of MIN was more important.

I'd say without any CTE all of this could run faster, creating in between results surely helps debugging and understanding, but normally just leads to less good optimizations.
Anyway, glad you got that solved.

Bye, Olaf.

Bye, Olaf.