?
Solved

WHERE Condition help needed - SQL

Posted on 2009-02-12
10
Medium Priority
?
232 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:Jeff S
  • 4
  • 4
  • 2
10 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 23623529
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
 
LVL 7

Author Comment

by:Jeff S
ID: 23623595
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
 
LVL 42

Expert Comment

by:dqmq
ID: 23623598
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 7

Author Comment

by:Jeff S
ID: 23623685
dgmg -
I need help on that date condition there please.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 23623747
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 23623753
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
 
LVL 42

Expert Comment

by:dqmq
ID: 23623767
>I need help on that date condition there please.

What happens when you remove the LEFT Join?

Or, try the above SQL.
0
 
LVL 7

Author Comment

by:Jeff S
ID: 23623823
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
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 1000 total points
ID: 23623924
I think you removed it from the wrong place.  Only remove it from line 140 in your question snippet.
0
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 31546115
Thanks for input .... ill roll with what i have. Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question