min / top 1 value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

min / top 1 value

Author

Topic

mpayne2010
Starting Member

14 Posts

Posted - 2013-08-07 : 15:34:22

I only want to result one value back per patient, however there could be multiple "fslv.value" on any patient. When I add logic to pull the top 1 value - im only getting a total of 1 result - so obviously Im doing something incorrect :) and I backed out of that. Its in my last Select Statement where I am select the fslv.value - is where it is generating multiple rows... should I put logic in my very last join to just pull the top 1 or min/max value(doesnt matter which one I pull - just need one)

Select distinct
cv.guid
,cv.clientdisplayname
,cv.visitidcode
,cv.idcode
,cv.ChartGUID
,cv.clientguid
,cv.admitdtm
,cv.dischargedtm
,cv.typecode
,cv.currentlocation
,cv.carelevelcode
,hid2.CodedHealthIssueGUID 'CodedHIGUID'
--,chi.code

into #tmpobspts

From CV3clientvisit cv
inner join CV3HealthIssueDeclaration hid2
on hid2.clientguid = cv.clientguid
and hid2.clientvisitguid = cv.guid
where cv.carelevelcode = 'Obsv Pt'
and cv.admitdtm between '2013-04-01' and '2013-06-30'-- will put FromDate and ToDate here

-- select * from #tmpobspts

select distinct #tmpobspts.* into #tmpobspts_withcodedHI from #tmpobspts
inner join CV3CodedHealthIssue chi
on chi.GUID = #tmpobspts.CodedHIGUID
where chi.typecode = 'ICD9' and chi.code not between '638.0' and '677'

select distinct guid
,clientdisplayname
,visitidcode
,idcode
,ChartGUID
,clientguid
,admitdtm
,dischargedtm
,typecode
,carelevelcode
,currentlocation
into #tmp_validpts
from #tmpobspts_withcodedHI
order by ClientdisplayName

select
fslv.value
,tvp.clientdisplayname
,tvp.visitidcode
,tvp.idcode
,cd.documentname

from #tmp_validpts tvp

INNER JOIN cv3clientdocument cd
ON cd.ClientGUID = tvp.ClientGUID
AND cd.ChartGUID = tvp.ChartGUID
and cd.ClientvisitGuid = tvp.guid
AND (cd.documentname = 'Initial Nursing Assessment' or cd.documentname = 'ED Triage Note - Adult')

INNER JOIN CV3ObservationDocument od
ON OD.OwnerGUID = cd.GUID
AND od.Active = 1

INNER JOIN CV3ObsCatalogMasterItem ocmi
ON ocmi.GUID = od.ObsMasterItemGUID
AND ocmi.Name = 'hs adm curr liv sit SL'

LEFT OUTER JOIN CV3Observation obs
ON obs.GUID = od.observationGUID
AND obs.StatusType = 1

LEFT OUTER JOIN SCMObsFSListValues fslv
ON fslv.ParentGUID = od.ObservationDocumentGUID
and fslv.ClientGUID = cd.ClientGUID

Thank you in advance!!!!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 16:04:07

Change the final left join to an outer apply like shown below:

SELECT  fslv.value ,
        tvp.clientdisplayname ,
        tvp.visitidcode ,
        tvp.idcode ,
        cd.documentname
FROM    #tmp_validpts tvp
        INNER JOIN cv3clientdocument cd ON cd.ClientGUID = tvp.ClientGUID
                                           AND cd.ChartGUID = tvp.ChartGUID
                                           AND cd.ClientvisitGuid = tvp.guid
                                           AND ( cd.documentname = 'Initial Nursing Assessment'
                                                 OR cd.documentname = 'ED Triage Note - Adult'
                                               )
        INNER JOIN CV3ObservationDocument od ON OD.OwnerGUID = cd.GUID
                                                AND od.Active = 1
        INNER JOIN CV3ObsCatalogMasterItem ocmi ON ocmi.GUID = od.ObsMasterItemGUID
                                                   AND ocmi.Name = 'hs adm curr liv sit SL'
        LEFT OUTER JOIN CV3Observation obs ON obs.GUID = od.observationGUID
                                              AND obs.StatusType = 1
        --LEFT OUTER JOIN SCMObsFSListValues fslv ON fslv.ParentGUID = od.ObservationDocumentGUID
        --                                           AND fslv.ClientGUID = cd.ClientGUID 
		OUTER APPLY
		( SELECT TOP (1) value FROM SCMObsFSListValues fslv
		  WHERE fslv.ParentGUID = od.ObservationDocumentGUID
		  AND fslv.ClientGUID = cd.ClientGUID 
		) AS fslv;

mpayne2010
Starting Member

14 Posts

Posted - 2013-08-07 : 16:20:29

That pulled out some data, however I am still getting more than one result for each patient..

Sample Results:
I am still getting multiple results - as in line 3 and 4 below
value VisitIDCode DocumentName
1. apartment 150061776780 INA
2. house 150061805985 INA
3. house 150061464221 ED Triage
4. house 150061464221 INA

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 16:26:04

That may be because there is more than one row for a given combination of od.ObservationDocumentGUID and cd.ClientGUID. Add those two columns to your output and see why they are different.

mpayne2010
Starting Member

14 Posts

Posted - 2013-08-07 : 16:28:24

Yes, the document names are different
one is INA the other is ED Traige - but the observation (value) on these documents are shared if that makes sense = so I only need to pull one row

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 16:40:50

I wasn't referring to the document names. Add two columns to your final query: 1) od.ObservationDocumentGUID and 2) cd.ClientGUID. See if they are the same for both rows, or different. They will be different. That is the reason you are getting the two rows.

If they are different, and you still want to get only one row, you need to change the WHERE clause inside the OUTER APPLY to make sure that you are getting only one row for the columns that you put in there.

Another way to look at it is as follows: Remove the outer apply and the cd.documentname from the select list. Do you get more than one row per patient?

mpayne2010
Starting Member

14 Posts

Posted - 2013-08-07 : 16:57:07

Yes, the observationdocumentguid is different... but that seems to be the only way to join the tables. Sorry, Im fairly new to SQL....

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 17:08:27

Take all the rows for one patient id. If you had to get only one row for that patient, look at each column and see what you want to get. Alternatively, if you can pick one row out of all those rows, which one do you want to get?

For example, this will give you one row per idcode. But that may not be exactly what you want for those columns

SELECT  MAX(fslv.VALUE) AS value ,
        MAX(tvp.clientdisplayname) AS  clientdisplayname,
        MAX(tvp.visitidcode)  visitidcode,
        tvp.idcode ,
        MAX(cd.documentname) documentname
FROM    #tmp_validpts tvp
        INNER JOIN cv3clientdocument cd ON cd.ClientGUID = tvp.ClientGUID
                                           AND cd.ChartGUID = tvp.ChartGUID
                                           AND cd.ClientvisitGuid = tvp.guid
                                           AND ( cd.documentname = 'Initial Nursing Assessment'
                                                 OR cd.documentname = 'ED Triage Note - Adult'
                                               )
        INNER JOIN CV3ObservationDocument od ON OD.OwnerGUID = cd.GUID
                                                AND od.Active = 1
        INNER JOIN CV3ObsCatalogMasterItem ocmi ON ocmi.GUID = od.ObsMasterItemGUID
                                                   AND ocmi.Name = 'hs adm curr liv sit SL'
        LEFT OUTER JOIN CV3Observation obs ON obs.GUID = od.observationGUID
                                              AND obs.StatusType = 1
        LEFT OUTER JOIN SCMObsFSListValues fslv ON fslv.ParentGUID = od.ObservationDocumentGUID
                                                   AND fslv.ClientGUID = cd.ClientGUID 
GROUP BY
	tvp.idcode

mpayne2010
Starting Member

14 Posts

Posted - 2013-08-07 : 19:26:11

THANK YOU!!!! That worked perfectly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources