Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

SQL Query Issue - HAVING clause ignored in query

First, let me state, the SQL is handled dynamically by the SQL Server, therefore, what you see in the WHERE clause may look odd to you. Its ok - I promise!

My query below is almost working ... for some reason it seems to be completely ignoring the following:

HAVING
    COUNT(*) > 1

I am getting back patients with 1 count. Not sure where I messed it up.
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 @DXDate DATETIME
DECLARE @AgeDate DATETIME
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME 
 
 
SET @startdate = ISNULL('01/01/2008' , '1/1/1900') 
SET @enddate = DATEADD(DAY , 1 , ISNULL('12/31/2008' , '1/1/3000'))
SET @AgeDate = '6/30/' + CONVERT(VARCHAR , YEAR(CASE WHEN '12/31/2008' IS NULL THEN GETDATE()
                                                     ELSE '12/31/2008'
                                                END))
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 Date
        (
          pv.Visit >= @startdate
          AND pv.Visit < @enddate
        )
        AND --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 <= @enddate /*@DXDate*/
                   AND (
                         (pvd.ICD9Code LIKE ( '250.%' )
                         OR pvd.ICD9Code IN ( '357.2' , '362.0' , '366.41' )
                         OR pvd.ICD9Code LIKE ( '648.0%' ))
                       )
                 )
            )
   
    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
    
WHERE
    --Filter on Date
    (
      pv.Visit >= @startdate
      AND pv.Visit < @enddate
    )
    
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
Jeff S
Asked:
Jeff S
  • 13
  • 12
  • 4
  • +2
1 Solution
 
nmarunCommented:
Doing a COUNT(*) is quite inefficient, use COUNT(p.PatientProfileID). I know this is not directly associated with your issue, but try and see if that fixes.

0
 
Jeff SAuthor Commented:
Same results back ...
0
 
enigmasolutionsCommented:

IDEA #1

Try changing COUNT(*) to COUNT(PatientID)

IDEA # 2

To work out what is going wrong do the following:
* take out the count(*) and the group by and the having
* order by
    p.PatientProfileID ,
    PatientID ,
    MRN ,
    PatientName ,
    Birthdate ,
    Sex ,
    age ,
    RaceCode ,
    EthnicityCode ,
    RaceMID2

Then look at the results.

IDEA # 3

Why are you using Temporary tables so much?  There is a big overhead + SQL can't optimise use of indexes.  In particular you don't need #tmp.  You should be able to just use the select statement to return the results.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
SharathData EngineerCommented:
I have commented #tmp table in your script. can you run it and post the result?
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 @DXDate DATETIME
DECLARE @AgeDate DATETIME
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME 
 
 
SET @startdate = ISNULL('01/01/2008' , '1/1/1900') 
SET @enddate = DATEADD(DAY , 1 , ISNULL('12/31/2008' , '1/1/3000'))
SET @AgeDate = '6/30/' + CONVERT(VARCHAR , YEAR(CASE WHEN '12/31/2008' IS NULL THEN GETDATE()
                                                     ELSE '12/31/2008'
                                                END))
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 Date
        (
          pv.Visit >= @startdate
          AND pv.Visit < @enddate
        )
        AND --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 <= @enddate /*@DXDate*/
                   AND (
                         (pvd.ICD9Code LIKE ( '250.%' )
                         OR pvd.ICD9Code IN ( '357.2' , '362.0' , '366.41' )
                         OR pvd.ICD9Code LIKE ( '648.0%' ))
                       )
                 )
            )
   
    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,
    COUNT(*) -- will check what count we are getting here
INTO #tmp I have commented this
FROM
    #Patients p
    LEFT JOIN PatientVisit pv ON p.PatientProfileID = pv.PatientProfileID
    
WHERE
    --Filter on Date
    (
      pv.Visit >= @startdate
      AND pv.Visit < @enddate
    )
    
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
 
Jeff SAuthor Commented:
Sharath 123
I get this error -
Server: Msg 8155, Level 16, State 1, Line 136
No column was specified for column 12 of '#tmp'.
0
 
Scott PletcherSenior DBACommented:
I suspect it may be related to the WHERE clause:

WHERE
    --Filter on Date
    (
      pv.Visit >= @startdate
      AND pv.Visit < @enddate
    )


Since pv is joined using a LEFT OUTER JOIN (LOJ), the columns in that table will be set to NULL if a matching row is not found.  A NULL column will never by >= or < anything.


First try moving those conditions from the WHERE into the LOJ:

FROM
    #Patients p
    LEFT JOIN PatientVisit pv ON p.PatientProfileID = pv.PatientProfileID AND
        (pv.Visit >= @startdate AND pv.Visit < @enddate)
0
 
SharathData EngineerCommented:
this one. missed out commenting INTO #tmp in previous post.
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 @DXDate DATETIME
DECLARE @AgeDate DATETIME
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME 
 
 
SET @startdate = ISNULL('01/01/2008' , '1/1/1900') 
SET @enddate = DATEADD(DAY , 1 , ISNULL('12/31/2008' , '1/1/3000'))
SET @AgeDate = '6/30/' + CONVERT(VARCHAR , YEAR(CASE WHEN '12/31/2008' IS NULL THEN GETDATE()
                                                     ELSE '12/31/2008'
                                                END))
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 Date
        (
          pv.Visit >= @startdate
          AND pv.Visit < @enddate
        )
        AND --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 <= @enddate /*@DXDate*/
                   AND (
                         (pvd.ICD9Code LIKE ( '250.%' )
                         OR pvd.ICD9Code IN ( '357.2' , '362.0' , '366.41' )
                         OR pvd.ICD9Code LIKE ( '648.0%' ))
                       )
                 )
            )
   
    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,
    COUNT(*)
--INTO #tmp
FROM
    #Patients p
    LEFT JOIN PatientVisit pv ON p.PatientProfileID = pv.PatientProfileID
    
WHERE
    --Filter on Date
    (
      pv.Visit >= @startdate
      AND pv.Visit < @enddate
    )
    
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
 
Jeff SAuthor Commented:
Sharath 123
I attached image file of my result set. I will take one of the first select query as well and show you the results. More posts to come.

2-10-2009-5-28-06-PM.png
0
 
enigmasolutionsCommented:
From the results it would appear you problem is not with the Having clause because count seems to be ALWAYS > 1

Check your data + maybe take a look at the results without the Group By.  See my earlier post.
0
 
SharathData EngineerCommented:
Yes, the problem is not with HAVING clause as you have count(*) > 1. can you post your expected result?
0
 
Jeff SAuthor Commented:
When I take this top section and comment out the #tmp stuff below I get the following results in my data set (see image file below).
(Wait for next post to see what i get when i introduce it back).

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 @DXDate DATETIME
DECLARE @AgeDate DATETIME
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME 
 
 
SET @startdate = ISNULL('01/01/2008' , '1/1/1900') 
SET @enddate = DATEADD(DAY , 1 , ISNULL('12/31/2008' , '1/1/3000'))
SET @AgeDate = '6/30/' + CONVERT(VARCHAR , YEAR(CASE WHEN '12/31/2008' IS NULL THEN GETDATE()
                                                     ELSE '12/31/2008'
                                                END))
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 Date
        (
          pv.Visit >= @startdate
          AND pv.Visit < @enddate
        )
        AND --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 <= @enddate /*@DXDate*/
                   AND (
                         (pvd.ICD9Code LIKE ( '250.%' )
                         OR pvd.ICD9Code IN ( '357.2' , '362.0' , '366.41' )
                         OR pvd.ICD9Code LIKE ( '648.0%' ))
                       )
                 )
            )
   
    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
    
WHERE
    --Filter on Date
    (
      pv.Visit >= @startdate
      AND pv.Visit < @enddate
    )
  
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

results.png
0
 
enigmasolutionsCommented:
Try adding distinct to the 1st query
0
 
enigmasolutionsCommented:
Woops thats not your problem.  But clearly your #Patients tabel has multiple records per patient.  And that seems to be your problem
0
 
enigmasolutionsCommented:
Which is caused by left outer joins.  It seems that the records highlighted in green do infact represent patients who have made more than one visit.  I think SQL is giving the right answers.

But perhaps you need to think about the "question" you are asking SQL to solve.

By the way what is the question?
0
 
Jeff SAuthor Commented:
When I go back and un-comment that section back ... I get this in my results ... see image file attached. Yellow highlighted people should not be in this since they had one count.
 

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 @DXDate DATETIME
DECLARE @AgeDate DATETIME
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME 
 
 
SET @startdate = ISNULL('01/01/2008' , '1/1/1900') 
SET @enddate = DATEADD(DAY , 1 , ISNULL('12/31/2008' , '1/1/3000'))
SET @AgeDate = '6/30/' + CONVERT(VARCHAR , YEAR(CASE WHEN '12/31/2008' IS NULL THEN GETDATE()
                                                     ELSE '12/31/2008'
                                                END))
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 Date
        (
          pv.Visit >= @startdate
          AND pv.Visit < @enddate
        )
        AND --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 <= @enddate /*@DXDate*/
                   AND (
                         (pvd.ICD9Code LIKE ( '250.%' )
                         OR pvd.ICD9Code IN ( '357.2' , '362.0' , '366.41' )
                         OR pvd.ICD9Code LIKE ( '648.0%' ))
                       )
                 )
            )
   
    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
    
WHERE
    --Filter on Date
    (
      pv.Visit >= @startdate
      AND pv.Visit < @enddate
    )
  
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

new-results.png
0
 
Jeff SAuthor Commented:
enigma -
The clients need to report back patients 18 and older who have been diagnosed as diabetic at some point during their time as a patient at the CHC (Community Health Center). Patients must have been seen in the clinic at least twice (thus the count() ).
0
 
Jeff SAuthor Commented:
ScottPletcher -
I tried your suggestion ... and got the same issue. Maybe the threads from your post to this one will explain something to you better.
0
 
SharathData EngineerCommented:
For PatientProfileID  =  2, You have two records in your #Patient table. Similary for 22, 3 records. How do you avoid these records? These records passed the filter condition (AVING COUNT(*) > 1).
0
 
enigmasolutionsCommented:
It seems to be data related.

Can you post results by taking out the group by in the second query + add the primary key fields of the PatientVisit table + Order by what you were grouping by.

Then we might get to see why you are gettign the reslts you are getting
0
 
Jeff SAuthor Commented:
Sharath 123 -
I dont follow. When I look at the #Patient Table output, I see one record for those two test patients. These are all dummy patients so no privacy laws to worry about by the way.
I am looking at my dataset results with the green highlighted patients.
Are you referring to Robert Benson and Lucille Anderson. Robert came in one time (Visit Date 12/22/08) and Lucille came in once (Visit Date 10/29/08). Maybe you can elaborate more.
0
 
enigmasolutionsCommented:
So try something like this code
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 @DXDate DATETIME
DECLARE @AgeDate DATETIME
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME 
 
 
SET @startdate = ISNULL('01/01/2008' , '1/1/1900') 
SET @enddate = DATEADD(DAY , 1 , ISNULL('12/31/2008' , '1/1/3000'))
SET @AgeDate = '6/30/' + CONVERT(VARCHAR , YEAR(CASE WHEN '12/31/2008' IS NULL THEN GETDATE()
                                                     ELSE '12/31/2008'
                                                END))
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 Date
        (
          pv.Visit >= @startdate
          AND pv.Visit < @enddate
        )
        AND --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 <= @enddate /*@DXDate*/
                   AND (
                         (pvd.ICD9Code LIKE ( '250.%' )
                         OR pvd.ICD9Code IN ( '357.2' , '362.0' , '366.41' )
                         OR pvd.ICD9Code LIKE ( '648.0%' ))
                       )
                 )
            )
   
    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 ,
	TicketNumber,	--Add this plus any other key fields to identify visits
    MRN ,
    PatientName ,
    Birthdate ,
    Age ,
    Sex ,
    RaceCode ,
    EthnicityCode ,
    RaceMID2
INTO
    #tmp
FROM
    #Patients p
    LEFT JOIN PatientVisit pv ON p.PatientProfileID = pv.PatientProfileID
    
WHERE
    --Filter on Date
    (
      pv.Visit >= @startdate
      AND pv.Visit < @enddate
    )
    
--GROUP BY
ORDER 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
 
Jeff SAuthor Commented:
I think you have to keep the Group By or you get this times every field you select ...
....is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
0
 
Jeff SAuthor Commented:
I just commented out the count  .... so im looking at my new results. Ill post back results.
0
 
enigmasolutionsCommented:
Arrrggh comment out the COUNT(*) in the select list.

I am trying to see how SQL returns the data without the group by
0
 
Jeff SAuthor Commented:
enigma -
I did. It looks like your onto something ... why in the world is it pulling these all in? New Data Set Image attached.

2-10-2009-6-20-09-PM.png
0
 
enigmasolutionsCommented:
It seems you have more visits than you thought.

For example PatientProfileID 2 has two tickets.

You should see this by running this query

select * from PatientVisits where PatientProfileID=2

I think the answer is to do with your data - happy hunting...
0
 
enigmasolutionsCommented:
By the way the reason you get these results is because the first query is quite restrictive (ie has more criteria - eg joins to other tables ertc)

But the second query is less restrictive on the patient visits table (which if Left Joined only with the date raneg restriction)
0
 
enigmasolutionsCommented:
Perhaps you should not Left outer join the Patient Visits table at all for the second query.

I think your first query is trying to establish the basic result set and then the second query does the having count(*)>1 right?

So just put all the info / criteria into the first query.

Then go with a simpler query for the second query (without a join to the visits table)
0
 
Jeff SAuthor Commented:
I'll work on this more ... thanks.
0
 
Jeff SAuthor Commented:
Thanks Enigma for all your help. I will re-work this report more on my end. Cheers
0
 
enigmasolutionsCommented:
You're welcome.

FYI - some tips... (particularly if dealing with large databases - eg over 50,000 records)

1) Dynamically generated queries are fine but perhaps you should only join in tables if they contribute to the criteria - so dynamically join them (or exclude them all together).

2) Left Outer Joins will restrict result sets when no records exist on the right hand side of the join to match to the left hand set.

3) Performance wise SQL Server is not as smart as you might think so conditions like (1=2)  AND (more conditions) may still cause SQL to test (more conditions) until it works out it doesn't need to.  The big point here is - try to make your queries simpler - it will help you and help SQL Server.  I am not too sure about this one except to say that SQL Server isn't always too smart at working out the most efficient way of doing things (see next point) so the simpler the better.

4) Use of @variables is a good practice and believe it or not can boost performance - Type "SQL Parameter Sniffing" into google for some shocking revelations.

5) Use Indexes (SQL Server Profiler can help to identify indexes to create)

6) Avoid using "Temporary tables" - they have to be written to the hard drive - instead try "table variables" (which can reside in memory) or better still use nested queries.

eg table variable
=============
declare @myTblVar tabe (MyStrField varchar(10), MyIntField int)

insert into @myTblVar
Select AStringField, AIntField
From SomeTables
Where ...

eg Nested Queries
===============
Select n.ABunchOfFields, count(*)
from
(
Select ABunchOfFields from ABunchOFTables where Criteria
) n
Group By n.ABunchOfFields
Having count(*)>1
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 13
  • 12
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now