WHERE Condition help needed - SQL

Please note, the SQL is handled dynamically by the server, alot of whats in my Where clause will look odd to you.

In my results, I get back a patient that should not be in my result set. I input an end-date of 12/31/2008 and asked it to return all my Diabetic patients. This patient has 2 encounters in my demo database (DOS 02/26/2008 and DOS 01/21/2009). On the 02/26/2008 DOS, I added a 250.00 diagnosis code to the encounter. Per the UDS manual, the patient must "Have been seen in the clinic at least twice". This patient based on my through date was seen only 1 time, not twice.

I believe I need to input a condition in the WHERE clause on the #tmp table query, but I'm not quite sure how to set that up right. Any help is appreciated!
SET NOCOUNT ON
 
CREATE TABLE #Patients
       (
         PatientProfileID INT ,
         PatientID VARCHAR(15) ,
         MRN VARCHAR(15) ,
         PatientName VARCHAR(100) ,
         Birthdate DATETIME ,
         Age VARCHAR(15) ,
         Sex VARCHAR(1) ,
         TicketNumber VARCHAR(15) ,
         Visit DATETIME ,
         PatientVisitID INT ,
         ICD9Code VARCHAR(10) ,
         Description VARCHAR(255) ,
         RaceCode VARCHAR(50) ,
         EthnicityCode VARCHAR(50) ,
         RaceMID2 INT
       )
 
DECLARE @AgeDate DATETIME
DECLARE @DXDate DATETIME
 
SET @AgeDate = '12/31/' + CONVERT(VARCHAR , YEAR('12/31/2008'))
SET @DXDate = '06/30/' + CONVERT(VARCHAR , YEAR('12/31/2008'))
 
INSERT INTO
    #Patients
    SELECT
        pp.PatientProfileID ,
        pp.PatientID ,
        pp.MedicalRecordNumber AS MRN ,
        RTRIM(RTRIM(ISNULL(pp.Last , '') + ' ' + ISNULL(pp.Suffix , '')) + ', ' + ISNULL(pp.First , '') + ' ' + ISNULL(pp.Middle , '')) AS PatientName ,
        pp.Birthdate ,
        ( DATEDIFF(day , pp.Birthdate , @AgeDate) / 365.25 ) AS Age ,
        pp.Sex ,
        pv.TicketNumber ,
        pv.Visit ,
        pv.PatientVisitID ,
        pvd.ICD9Code ,
        pvd.Description ,
        CASE WHEN r.Code = 'H'
                  OR e.Code = 'H' THEN 'Hispanic/Latino'
             WHEN r.Code = 'A' THEN 'Asian'
             WHEN r.Code = 'NH' THEN 'Native Hawaiian'
             WHEN r.Code = 'OPI' THEN 'Other Pacific Islander'
             WHEN r.Code = 'B' THEN 'Black/African American'
             WHEN r.Code = 'AI' THEN 'American Indian/Alaskan Native'
             WHEN r.Code = 'W' THEN 'White'
             WHEN r.Code = 'M' THEN 'More than one race'
             WHEN r.Code = 'U' THEN 'Unreported'
             ELSE 'Unreported'
        END AS RaceCode ,
        CASE WHEN r.Code = 'H'
                  OR e.Code = 'H' THEN 'Hispanic/Latino'
             ELSE 'All Others'
        END AS EthnicityCode ,
        cri.RaceMID2
    FROM
        PatientProfile pp
        LEFT JOIN PatientVisit pv ON pp.PatientProfileID = pv.PatientProfileId
        LEFT JOIN PatientVisitDiags pvd ON pv.PatientVisitID = pvd.PatientVisitID
        LEFT JOIN cusCRIInterview cri ON pp.patientprofileid = cri.patientprofileid
        LEFT JOIN Medlists r ON pp.RaceMID = r.MedListsID
        LEFT JOIN cusCRIMedLists e ON cri.RaceMID2 = e.MedListsID
    WHERE
        --Filter on facility
        (
          (
            NULL IS NOT NULL
            AND pv.FacilityID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND 
	--Filter on Company
        (
          (
            NULL IS NOT NULL
            AND pv.CompanyID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND ( DATEDIFF(day , pp.Birthdate , @AgeDate) / 365.25 ) > 17.99
        AND (
              (
                2 = 1
                AND pvd.ICD9Code LIKE ( '401.%' )
                AND pv.Visit >= @DXDate
              )
              OR (
                   2 = 2
                   AND pv.Visit >= @DXDate
                   AND (
                         (pvd.ICD9Code LIKE ( '250.%' )
                         OR pvd.ICD9Code IN ( '357.2' , '362.0' , '366.41' )
                         OR pvd.ICD9Code LIKE ( '648.0%' ))
                       )
                 )
            )
        AND ( DATEPART(Year , pv.visit) ) > ( DATEPART(Year , ( '12/31/2008' )) ) - 1
    GROUP BY
        pp.PatientProfileID ,
        pp.PatientId ,
        pp.MedicalRecordNumber ,
        pp.Birthdate ,
        pp.Sex ,
        pv.PatientVisitId ,
        pvd.ICD9Code ,
        pvd.Description ,
        pv.TicketNumber ,
        pv.Visit ,
        pp.Last ,
        pp.Suffix ,
        pp.First ,
        pp.Middle ,
        r.code ,
        e.code ,
        cri.raceMID2
    ORDER BY
        pp.PatientProfileID
 
SELECT
    p.PatientProfileID ,
    COUNT(*) AS cnt ,
    PatientID ,
    MRN ,
    PatientName ,
    Birthdate ,
    Age ,
    Sex ,
    RaceCode ,
    EthnicityCode ,
    RaceMID2
INTO
    #tmp
FROM
    #Patients p
    LEFT JOIN PatientVisit pv ON p.PatientProfileID = pv.PatientProfileID
 
GROUP BY
    p.PatientProfileID ,
    PatientID ,
    MRN ,
    PatientName ,
    Birthdate ,
    Sex ,
    age ,
    RaceCode ,
    EthnicityCode ,
    RaceMID2
HAVING
    COUNT(*) > 1
ORDER BY
    CASE WHEN '1' = 1 THEN MRN
         WHEN '1' = 2 THEN PatientName
         WHEN '1' = 3 THEN PatientID
         ELSE NULL
    END
 
--Select * from #Patients
SELECT * FROM #tmp
 
DROP TABLE #Patients
DROP TABLE #tmp

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Think the issue may be in the COUNT(*) statement as you will get the count of rows and not necessarily the count of visits since using a left join visits could be NULL.
SET NOCOUNT ON
 
CREATE TABLE #Patients
       (
         PatientProfileID INT ,
         PatientID VARCHAR(15) ,
         MRN VARCHAR(15) ,
         PatientName VARCHAR(100) ,
         Birthdate DATETIME ,
         Age VARCHAR(15) ,
         Sex VARCHAR(1) ,
         TicketNumber VARCHAR(15) ,
         Visit DATETIME ,
         PatientVisitID INT ,
         ICD9Code VARCHAR(10) ,
         Description VARCHAR(255) ,
         RaceCode VARCHAR(50) ,
         EthnicityCode VARCHAR(50) ,
         RaceMID2 INT
       )
 
DECLARE @AgeDate DATETIME
DECLARE @DXDate DATETIME
 
SET @AgeDate = '12/31/' + CONVERT(VARCHAR , YEAR('12/31/2008'))
SET @DXDate = '06/30/' + CONVERT(VARCHAR , YEAR('12/31/2008'))
 
INSERT INTO
    #Patients
    SELECT
        pp.PatientProfileID ,
        pp.PatientID ,
        pp.MedicalRecordNumber AS MRN ,
        RTRIM(RTRIM(ISNULL(pp.Last , '') + ' ' + ISNULL(pp.Suffix , '')) + ', ' + ISNULL(pp.First , '') + ' ' + ISNULL(pp.Middle , '')) AS PatientName ,
        pp.Birthdate ,
        ( DATEDIFF(day , pp.Birthdate , @AgeDate) / 365.25 ) AS Age ,
        pp.Sex ,
        pv.TicketNumber ,
        pv.Visit ,
        pv.PatientVisitID ,
        pvd.ICD9Code ,
        pvd.Description ,
        CASE WHEN r.Code = 'H'
                  OR e.Code = 'H' THEN 'Hispanic/Latino'
             WHEN r.Code = 'A' THEN 'Asian'
             WHEN r.Code = 'NH' THEN 'Native Hawaiian'
             WHEN r.Code = 'OPI' THEN 'Other Pacific Islander'
             WHEN r.Code = 'B' THEN 'Black/African American'
             WHEN r.Code = 'AI' THEN 'American Indian/Alaskan Native'
             WHEN r.Code = 'W' THEN 'White'
             WHEN r.Code = 'M' THEN 'More than one race'
             WHEN r.Code = 'U' THEN 'Unreported'
             ELSE 'Unreported'
        END AS RaceCode ,
        CASE WHEN r.Code = 'H'
                  OR e.Code = 'H' THEN 'Hispanic/Latino'
             ELSE 'All Others'
        END AS EthnicityCode ,
        cri.RaceMID2
    FROM
        PatientProfile pp
        LEFT JOIN PatientVisit pv ON pp.PatientProfileID = pv.PatientProfileId
        LEFT JOIN PatientVisitDiags pvd ON pv.PatientVisitID = pvd.PatientVisitID
        LEFT JOIN cusCRIInterview cri ON pp.patientprofileid = cri.patientprofileid
        LEFT JOIN Medlists r ON pp.RaceMID = r.MedListsID
        LEFT JOIN cusCRIMedLists e ON cri.RaceMID2 = e.MedListsID
    WHERE
        --Filter on facility
        (
          (
            NULL IS NOT NULL
            AND pv.FacilityID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND 
	--Filter on Company
        (
          (
            NULL IS NOT NULL
            AND pv.CompanyID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND ( DATEDIFF(day , pp.Birthdate , @AgeDate) / 365.25 ) > 17.99
        AND (
              (
                2 = 1
                AND pvd.ICD9Code LIKE ( '401.%' )
                AND pv.Visit >= @DXDate
              )
              OR (
                   2 = 2
                   AND pv.Visit >= @DXDate
                   AND (
                         (pvd.ICD9Code LIKE ( '250.%' )
                         OR pvd.ICD9Code IN ( '357.2' , '362.0' , '366.41' )
                         OR pvd.ICD9Code LIKE ( '648.0%' ))
                       )
                 )
            )
        AND ( DATEPART(Year , pv.visit) ) > ( DATEPART(Year , ( '12/31/2008' )) ) - 1
    GROUP BY
        pp.PatientProfileID ,
        pp.PatientId ,
        pp.MedicalRecordNumber ,
        pp.Birthdate ,
        pp.Sex ,
        pv.PatientVisitId ,
        pvd.ICD9Code ,
        pvd.Description ,
        pv.TicketNumber ,
        pv.Visit ,
        pp.Last ,
        pp.Suffix ,
        pp.First ,
        pp.Middle ,
        r.code ,
        e.code ,
        cri.raceMID2
    ORDER BY
        pp.PatientProfileID
 
SELECT
    p.PatientProfileID ,
    COUNT(pv.PatientProfileID) AS cnt ,
    PatientID ,
    MRN ,
    PatientName ,
    Birthdate ,
    Age ,
    Sex ,
    RaceCode ,
    EthnicityCode ,
    RaceMID2
INTO
    #tmp
FROM
    #Patients p
    LEFT JOIN PatientVisit pv ON p.PatientProfileID = pv.PatientProfileID
 
GROUP BY
    p.PatientProfileID ,
    PatientID ,
    MRN ,
    PatientName ,
    Birthdate ,
    Sex ,
    age ,
    RaceCode ,
    EthnicityCode ,
    RaceMID2
HAVING
    COUNT(pv.PatientProfileID) > 1
ORDER BY
    CASE WHEN '1' = 1 THEN MRN
         WHEN '1' = 2 THEN PatientName
         WHEN '1' = 3 THEN PatientID
         ELSE NULL
    END
 
--Select * from #Patients
SELECT * FROM #tmp
 
DROP TABLE #Patients
DROP TABLE #tmp

Open in new window

0
 
Jeff SAuthor Commented:
mwvisa1
I made that change and its still giving me the same result set. I dont think its looking at the through date and applying that logic in my #tmp query. Right?
0
 
dqmqCommented:
Joining back to patient visits  reintroduces the extra date. Either eliminate the LEFT JOIN or add a similar date condition here


...
INTO
    #tmp
FROM
    #Patients p
    LEFT JOIN PatientVisit pv ON p.PatientProfileID = pv.PatientProfileID
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Jeff SAuthor Commented:
dgmg -
I need help on that date condition there please.
0
 
dqmqCommented:
You really seem to like Temp tables.  Any reason this won't work just as well?
SELECT
        pp.PatientProfileID ,
        COUNT(pv.PatientProfileID) AS cnt ,
        pp.PatientID ,
        pp.MedicalRecordNumber AS MRN ,
        RTRIM(RTRIM(ISNULL(pp.Last , '') + ' ' + ISNULL(pp.Suffix , '')) + ', ' + ISNULL(pp.First , '') + ' ' + ISNULL(pp.Middle , '')) AS PatientName ,
        pp.Birthdate ,
        ( DATEDIFF(day , pp.Birthdate , @AgeDate) / 365.25 ) AS Age ,
        pp.Sex ,
             CASE WHEN r.Code = 'H'
                  OR e.Code = 'H' THEN 'Hispanic/Latino'
             WHEN r.Code = 'A' THEN 'Asian'
             WHEN r.Code = 'NH' THEN 'Native Hawaiian'
             WHEN r.Code = 'OPI' THEN 'Other Pacific Islander'
             WHEN r.Code = 'B' THEN 'Black/African American'
             WHEN r.Code = 'AI' THEN 'American Indian/Alaskan Native'
             WHEN r.Code = 'W' THEN 'White'
             WHEN r.Code = 'M' THEN 'More than one race'
             WHEN r.Code = 'U' THEN 'Unreported'
             ELSE 'Unreported'
        END AS RaceCode ,
        CASE WHEN r.Code = 'H'
                  OR e.Code = 'H' THEN 'Hispanic/Latino'
             ELSE 'All Others'
        END AS EthnicityCode ,
        cri.RaceMID2
    FROM
        PatientProfile pp
        LEFT JOIN PatientVisit pv ON pp.PatientProfileID = pv.PatientProfileId
        LEFT JOIN PatientVisitDiags pvd ON pv.PatientVisitID = pvd.PatientVisitID
        LEFT JOIN cusCRIInterview cri ON pp.patientprofileid = cri.patientprofileid
        LEFT JOIN Medlists r ON pp.RaceMID = r.MedListsID
        LEFT JOIN cusCRIMedLists e ON cri.RaceMID2 = e.MedListsID
    WHERE
        --Filter on facility
        (
          (
            NULL IS NOT NULL
            AND pv.FacilityID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND 
	--Filter on Company
        (
          (
            NULL IS NOT NULL
            AND pv.CompanyID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND ( DATEDIFF(day , pp.Birthdate , @AgeDate) / 365.25 ) > 17.99
        AND (
              (
                2 = 1
                AND pvd.ICD9Code LIKE ( '401.%' )
                AND pv.Visit >= @DXDate
              )
              OR (
                   2 = 2
                   AND pv.Visit >= @DXDate
                   AND (
                         (pvd.ICD9Code LIKE ( '250.%' )
                         OR pvd.ICD9Code IN ( '357.2' , '362.0' , '366.41' )
                         OR pvd.ICD9Code LIKE ( '648.0%' ))
                       )
                 )
            )
        AND ( DATEPART(Year , pv.visit) ) > ( DATEPART(Year , ( '12/31/2008' )) ) - 1
    GROUP BY
        pp.PatientProfileID ,
        pp.PatientId ,
        pp.MedicalRecordNumber ,
        pp.Birthdate ,
        pp.Sex ,
        pp.Last ,
        pp.Suffix ,
        pp.First ,
        pp.Middle ,
        r.code ,
        e.code ,
        cri.raceMID2
  Having count(*) > 1
    ORDER BY
        pp.PatientProfileID

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Good catch dqmq.  The visit information is already inserted into #Patients so don't need to join back to PatientVisit at all.  If you are looking for unique visit dates then COUNT(DISTINCT Visit); visit ids, COUNT(DISTINCT PatientVisitID).
SET NOCOUNT ON
 
CREATE TABLE #Patients
       (
         PatientProfileID INT ,
         PatientID VARCHAR(15) ,
         MRN VARCHAR(15) ,
         PatientName VARCHAR(100) ,
         Birthdate DATETIME ,
         Age VARCHAR(15) ,
         Sex VARCHAR(1) ,
         TicketNumber VARCHAR(15) ,
         Visit DATETIME ,
         PatientVisitID INT ,
         ICD9Code VARCHAR(10) ,
         Description VARCHAR(255) ,
         RaceCode VARCHAR(50) ,
         EthnicityCode VARCHAR(50) ,
         RaceMID2 INT
       )
 
DECLARE @AgeDate DATETIME
DECLARE @DXDate DATETIME
 
SET @AgeDate = '12/31/' + CONVERT(VARCHAR , YEAR('12/31/2008'))
SET @DXDate = '06/30/' + CONVERT(VARCHAR , YEAR('12/31/2008'))
 
INSERT INTO
    #Patients
    SELECT
        pp.PatientProfileID ,
        pp.PatientID ,
        pp.MedicalRecordNumber AS MRN ,
        RTRIM(RTRIM(ISNULL(pp.Last , '') + ' ' + ISNULL(pp.Suffix , '')) + ', ' + ISNULL(pp.First , '') + ' ' + ISNULL(pp.Middle , '')) AS PatientName ,
        pp.Birthdate ,
        ( DATEDIFF(day , pp.Birthdate , @AgeDate) / 365.25 ) AS Age ,
        pp.Sex ,
        pv.TicketNumber ,
        pv.Visit ,
        pv.PatientVisitID ,
        pvd.ICD9Code ,
        pvd.Description ,
        CASE WHEN r.Code = 'H'
                  OR e.Code = 'H' THEN 'Hispanic/Latino'
             WHEN r.Code = 'A' THEN 'Asian'
             WHEN r.Code = 'NH' THEN 'Native Hawaiian'
             WHEN r.Code = 'OPI' THEN 'Other Pacific Islander'
             WHEN r.Code = 'B' THEN 'Black/African American'
             WHEN r.Code = 'AI' THEN 'American Indian/Alaskan Native'
             WHEN r.Code = 'W' THEN 'White'
             WHEN r.Code = 'M' THEN 'More than one race'
             WHEN r.Code = 'U' THEN 'Unreported'
             ELSE 'Unreported'
        END AS RaceCode ,
        CASE WHEN r.Code = 'H'
                  OR e.Code = 'H' THEN 'Hispanic/Latino'
             ELSE 'All Others'
        END AS EthnicityCode ,
        cri.RaceMID2
    FROM
        PatientProfile pp
        LEFT JOIN PatientVisit pv ON pp.PatientProfileID = pv.PatientProfileId
        LEFT JOIN PatientVisitDiags pvd ON pv.PatientVisitID = pvd.PatientVisitID
        LEFT JOIN cusCRIInterview cri ON pp.patientprofileid = cri.patientprofileid
        LEFT JOIN Medlists r ON pp.RaceMID = r.MedListsID
        LEFT JOIN cusCRIMedLists e ON cri.RaceMID2 = e.MedListsID
    WHERE
        --Filter on facility
        (
          (
            NULL IS NOT NULL
            AND pv.FacilityID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND 
	--Filter on Company
        (
          (
            NULL IS NOT NULL
            AND pv.CompanyID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND ( DATEDIFF(day , pp.Birthdate , @AgeDate) / 365.25 ) > 17.99
        AND (
              (
                2 = 1
                AND pvd.ICD9Code LIKE ( '401.%' )
                AND pv.Visit >= @DXDate
              )
              OR (
                   2 = 2
                   AND pv.Visit >= @DXDate
                   AND (
                         (pvd.ICD9Code LIKE ( '250.%' )
                         OR pvd.ICD9Code IN ( '357.2' , '362.0' , '366.41' )
                         OR pvd.ICD9Code LIKE ( '648.0%' ))
                       )
                 )
            )
        AND ( DATEPART(Year , pv.visit) ) > ( DATEPART(Year , ( '12/31/2008' )) ) - 1
    GROUP BY
        pp.PatientProfileID ,
        pp.PatientId ,
        pp.MedicalRecordNumber ,
        pp.Birthdate ,
        pp.Sex ,
        pv.PatientVisitId ,
        pvd.ICD9Code ,
        pvd.Description ,
        pv.TicketNumber ,
        pv.Visit ,
        pp.Last ,
        pp.Suffix ,
        pp.First ,
        pp.Middle ,
        r.code ,
        e.code ,
        cri.raceMID2
    ORDER BY
        pp.PatientProfileID
 
SELECT
    p.PatientProfileID ,
    COUNT(DISTINCT Visit) AS cnt ,
    PatientID ,
    MRN ,
    PatientName ,
    Birthdate ,
    Age ,
    Sex ,
    RaceCode ,
    EthnicityCode ,
    RaceMID2
INTO
    #tmp
FROM
    #Patients p
GROUP BY
    p.PatientProfileID ,
    PatientID ,
    MRN ,
    PatientName ,
    Birthdate ,
    Sex ,
    age ,
    RaceCode ,
    EthnicityCode ,
    RaceMID2
HAVING
    COUNT(DISTINCT Visit) > 1
ORDER BY
    CASE WHEN '1' = 1 THEN MRN
         WHEN '1' = 2 THEN PatientName
         WHEN '1' = 3 THEN PatientID
         ELSE NULL
    END
 
--Select * from #Patients
SELECT * FROM #tmp
 
DROP TABLE #Patients
DROP TABLE #tmp

Open in new window

0
 
dqmqCommented:
>I need help on that date condition there please.

What happens when you remove the LEFT Join?

Or, try the above SQL.
0
 
Jeff SAuthor Commented:
Server: Msg 107, Level 16, State 2, Line 124
The column prefix 'pv' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 124
The column prefix 'pv' does not match with a table name or alias name used in the query.
When I remove the Join
0
 
dqmqConnect With a Mentor Commented:
I think you removed it from the wrong place.  Only remove it from line 140 in your question snippet.
0
 
Jeff SAuthor Commented:
Thanks for input .... ill roll with what i have. Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.