CODEWITHSUNDEEP
sqlsql-server
mslhrt
mslhrt

Reputation: 207

Re-visits within 30 Days

My main question was beautifully answered here by splash58 but I'm having an issue with the code that I think warrants another question (aka: the solution has spawned a monster of more questions!).

I have a sample of data, from 07/01/2014 to 12/31/2014. Here is my original SQL Query: 

SELECT 
  ADV.AcctNum
 ,ADV.Name
 ,ADV.HomePhoneNum
 ,ADV.ReasonForVisit
 ,ADV.ServiceDateTime
 ,ADI.ErDx
 ,PRV.PCP
 ,ADI.ErDispoID
 ,ADI.ErDispoName
 ,ADI.Dx

FROM Visits                    ADV
JOIN Discharge                 ADI  ON (ADI.VisitID  = ADV.VisitID AND
                                        ADI.SourceID = ADV.SourceID )
JOIN Providers                 PRV  ON (PRV.VisitID  = ADV.VisitID AND
                                        PRV.SourceID = PRV.SourceID )
WHERE ADV.ServiceDateTime BETWEEN '2014-07-01' AND '2014-12-31'          AND
  ADI.ErDispoName IS NOT NULL                               AND
( UPPER(ADV.ReasonForVisit) LIKE UPPER('%FLU%')             OR
  UPPER(ADV.ReasonForVisit) LIKE UPPER('%COLD%')            OR
  UPPER(ADI.ErDiagnosis) LIKE UPPER('%FLU%')                OR
  UPPER(ADI.ErDiagnosis) LIKE UPPER('%COLD%')               OR
  UPPER(ADI.Diagnosis) LIKE UPPER('%FLU%')                  OR
  UPPER(ADI.Diagnosis) LIKE UPPER('%COLD%'))                AND
ADV.FacilityID = '.' AND
ADV.Name LIKE '%SMITH%'
ORDER BY ADV.ServiceDateTime

And here is what that returns: 

AccountNum  Name        HomePhoneNum    ReasonForVisit  ServiceDateTime ErDx                        PCP         ErDispoID       ErDispoName                  Dx
       1    SMITH,JOHN  999-999-9999    FLU             8/12/2014       FLU                         FORZ            AMA         *AGAINST MEDICAL ADVICE (07) NULL
       2    SMITH,JOHN  999-999-9999    Cold            8/23/2014       UPPER REPIRATORY INFECTION  FORZ            H           *HOME (01)                   NULL
       3    SMITH,JOHN  999-999-9999    COUGH           8/24/2014       FLU                         FORZ            H           *HOME (01)                   NULL
       4    SMITH,JOHN  999-999-9999    COUGH           10/29/2014      PNEMONIA                    FORZ            MSHH        *ADMIT TO HH (09)            Flu
       5    SMITH,JOHN  999-999-9999    COUGH           11/5/2014       FLU                         FORZ            H           *HOME (01)                   NULL 
       6    SMITH,JOHN  999-999-9999    FLU             11/7/2014       FLU                         FORZ            H           *HOME (01)                   NULL
       7    SMITH,JOHN  999-999-9999    Flu             11/8/2014       FLU                         FORZ            H           *HOME (01)                   NULL
       8    SMITH,JOHN  999-999-9999    FLU             12/13/2014      FLU                         FORZ            H           *HOME (01)                   NULL

Notice the ServiceDateTime field. So now, I cut my code down (losing the joins) so that I could attempt to use the code from the other question. See below...

SELECT DISTINCT
  t1.AcctNum
 ,t1.UnitNum
 ,t1.Name
 ,t1.HomePhoneNum
 ,t1.ReasonForVisit
 ,t1.ServiceDateTime

FROM Visits t1
    Inner Join Visits t2    ON (t1.UnitNumber  = t2.UnitNumber   AND
                                t1.AccountNum != t2.AccountNum   AND
                                t1.ServiceDateTime > t2.ServiceDateTime AND
                 DATEDIFF(day, t1.ServiceDateTime, t2.ServiceDateTime) < 30)

WHERE t1.ServiceDateTime BETWEEN '2014-08-01' AND '2014-12-31'          AND
( UPPER(t1.ReasonForVisit) LIKE UPPER('%COLD%')                         OR
  UPPER(t1.ReasonForVisit) LIKE UPPER('%FLU%')                          OR
  UPPER(t1.ReasonForVisit) LIKE UPPER('%COUGH%'))                       AND
  t1.FacilityID = '.'                                                   AND
  t1.Name LIKE '%SMITH%'

I was super excited when I THOUGHT that this worked, but unfortunately there was one minor flaw. Here are the results...

AccountNum  UnitNum  Name       HomePhoneNum    ReasonForVisit  ServiceDateTime 
       1     1234    SMITH,JOHN 999-999-9999    FLU             8/12/2014
       3     1234    SMITH,JOHN 999-999-9999    COUGH           8/24/2014
       5     1234    SMITH,JOHN 999-999-9999    COUGH           11/5/2014
       6     1234    SMITH,JOHN 999-999-9999    FLU             11/7/2014
       7     1234    SMITH,JOHN 999-999-9999    Flu             11/8/2014

I'm expecting to get back AccountNum "3, 5, 6, and 7" but NOT number 1. Why is it returning Account 1? There were no visits prior to this account within 30 days, so it should be removed from this list. Additionally, as stated above, in this new query to get to the bottom of re-vists I have been forced, due to my limited knowledge of SQL, to omit my other joins from the original query. These are necessary to get all of the information I need.

So I need to know WHY this query is returning AccountNum "1", (when I change date range to after 08/12/14 it works just fine), and then I need help with my code to use this new query to include all the data in the first query.

Thanks for ANY help and let me know if any clarification is needed I know it's a complicated question!

UPDATE

Please note: I ran the following query to get "ALL" the visits for this person as requested by those in comments. I even ran the query from "2014-06-01" to "2014-12-31" to be sure.

SELECT 
  AcctNum
 ,Name
 ,LocationID
 ,ServiceDateTime
 ,ReasonForVisit
FROM Visits 
WHERE ServiceDateTime BETWEEN '2014-06-01' AND '2014-12-31' AND
      UnitNum = '1234'
ORDER BY ServiceDateTime

There are NO visits prior to 2014-08-12 and all the other visits that were filtered out of my other queries after that date clearly had no impact. See below:

AccountNum  Name        ServiceDateTime
1           SMITH,JOHN  2014-08-12
2           SMITH,JOHN  2014-08-23
3           SMITH,JOHN  2014-08-24
100         SMITH,JOHN  2014-08-26
99          SMITH,JOHN  2014-09-03
98          SMITH,JOHN  2014-09-04
97          SMITH,JOHN  2014-09-12
96          SMITH,JOHN  2014-10-13
95          SMITH,JOHN  2014-10-21
94          SMITH,JOHN  2014-10-24
4           SMITH,JOHN  2014-10-29
5           SMITH,JOHN  2014-11-05
6           SMITH,JOHN  2014-11-07
7           SMITH,JOHN  2014-11-08
8           SMITH,JOHN  2014-12-13

All accounts above 90 show that there are other visits within the date range (but not prior to 2014-08-12) and that the query properly filtered them out.

Upvotes: 2

Views: 230

Answers (1)

Tom H
Tom H

Reputation: 47454

I believe that the ordering of your parameters in the DATEDIFF function are backwards, although that still wouldn't explain why that row is returning. As @Zach Ford said, without knowing the actual data (as opposed to the results from some other query) it's hard to say. My guess however, is that there are rows for that UnitNumber which are not flu, cough, etc., so they don't return in your original query, but they still exist in the table and would still get caught by the INNER JOIN. If you want to exclude these from joining then you'll need to add that criteria to the JOIN clause for t2 as well.

Upvotes: 4

Related Questions