SQL 2005 - Sub-Query Assistance Needed

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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL 2005 - Sub-Query Assistance Needed

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2009-02-20 : 09:48:17

Please note, the SQL is handled dynamically by the SQL server, some of whats in the Where clause will look odd to you, please ignore this.

What I need help on is my subquery's - ([Last Appt Date]).

What I need is the patients that dont have a record on the subquery to still print and just put a NULL value. Any help is appreciated and please remember alot of the SQL is handled dynamically - please ignore the odd looking stuff in the where clause.

[CODE]
/* Patient List*/
SET NOCOUNT ON

DECLARE @Zip VARCHAR(40)
SELECT
@Zip = LTRIM(RTRIM('NULL')) + '%' ;
WITH cteMedlitsPatientStatus
AS (
SELECT
*
FROM
Medlists
WHERE
TableName = 'PatientProfileStatus'
)
SELECT
PatientID ,
RespSameAsPatient = ISNULL(PatientSameAsGuarantor , 0) ,
PatientName = CASE WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix , '')) <> ''
THEN RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix , '')) + ', ' + ISNULL(pp.First , '') + ' ' + ISNULL(pp.Middle , ''))
ELSE RTRIM(ISNULL(pp.First , '') + ' ' + ISNULL(pp.Middle , ''))
END ,
PatientAddr1 = pp.Address1 ,
PatientAddr2 = pp.Address2 ,
PatientCity = pp.City ,
PatientState = pp.State ,
PatientZip = pp.Zip ,
PatientRespName = CASE WHEN RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix , '')) <> ''
THEN RTRIM(RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix , '')) + ', ' + ISNULL(pr.First , '') + ' ' + ISNULL(pr.Middle , ''))
ELSE RTRIM(ISNULL(pr.First , '') + ' ' + ISNULL(pr.Middle , ''))
END ,
PatientRespAddr1 = pr.Address1 ,
PatientRespAddr2 = pr.Address2 ,
PatientRespCity = pr.City ,
PatientRespState = pr.State ,
PatientRespZip = pr.Zip ,
FinancialClass = ISNULL(ml.Description , 'none') ,
Doctor = df.ListName ,
PCP = ISNULL(pcp.Listname , 'No PCP Assigned') ,
Facility = df1.OrgName ,
Balance = ISNULL(ppa.PatBalance , 0) + ISNULL(ppa.InsBalance , 0) ,
pp.DeathDate ,
Status = ml1.Description ,
pp.BirthDate ,
(
SELECT TOP 1
apptstart
FROM
Appointments a
WHERE
apptstart >= ISNULL(NULL , '1/1/1900')
AND apptstart < DATEADD(d , 1 , ISNULL(NULL , '1/1/3000'))
AND pp.patientprofileid = a.ownerId
AND DATEDIFF(day , GETDATE() , apptstart) < 0
ORDER BY
apptstart DESC
) AS [Last Appt Date]
INTO
#TMP
FROM
PatientProfile pp
LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID
LEFT JOIN Guarantor pr ON pp.GuarantorID = pr.GuarantorID
LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID
LEFT JOIN DoctorFacility df ON pp.DoctorID = df.DoctorFacilityID
LEFT JOIN DoctorFacility df1 ON pp.FacilityId = df1.DoctorFacilityID
LEFT JOIN cteMedlitsPatientStatus ml1 ON pp.PatientStatusMId = ml1.MedlistsId
LEFT JOIN DoctorFacility pcp ON pp.PrimaryCareDoctorId = pcp.DoctorFacilityId
WHERE
--Filter on patient
(
(
NULL IS NOT NULL
AND pp.PatientProfileID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND -- Filter inactive patients
(
(
0 = 0
AND ( ml1.Code <> 'I' )
)
OR ( 0 = 1 )
)
AND -- Filter deceased patients
(
(
0 = 0
AND ( ml1.Code <> 'X' )
)
OR ( 0 = 1 )
)
AND --Filter on doctor
(
(
NULL IS NOT NULL
AND pp.DoctorID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on pcp doctor
(
(
NULL IS NOT NULL
AND pp.PrimaryCareDoctorId IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on financial class
(
(
NULL IS NOT NULL
AND pp.FinancialClassMId = NULL
)
OR ( NULL IS NULL )
)
AND --Filter on guarantor
(
(
NULL IS NOT NULL
AND pp.GuarantorID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on facility
(
(
NULL IS NOT NULL
AND pp.FacilityID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND (
(
NULL IS NOT NULL
AND 1 = 1
AND pp.zip LIKE @Zip
)
OR (
NULL IS NOT NULL
AND 1 = 2
AND pr.zip LIKE @Zip
)
OR ( NULL IS NULL )
)
SELECT
*
FROM
#tmp
WHERE
-- Filter on Last Visit Date
(
[Last Appt Date] >= ISNULL(NULL , '01/01/1900')
AND [Last Appt Date] < DATEADD(d , 1 , ISNULL(NULL , '01/01/3000'))
)
ORDER BY
PatientName
DROP TABLE #TMP
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 09:51:20

which is master table containing patient info?

JeffS23
Posting Yak Master

212 Posts

Posted - 2009-02-20 : 10:10:17

PatientProfile table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 10:17:10

try making where like this


...
WHERE
    -- Filter on Last Visit Date
    (
      [Last Appt Date] >= ISNULL(NULL , '01/01/1900')
      AND [Last Appt Date] < DATEADD(d , 1 , ISNULL(NULL , '01/01/3000'))
      OR  [Last Appt Date] IS NULL
    )

JeffS23
Posting Yak Master

212 Posts

Posted - 2009-02-20 : 10:29:55

visakh16

thankyou so much ... that did the trick. Your help is deeply appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 11:20:51

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources