Results 1 to 14 of 14

Thread: DateDiff

  1. #1
    Join Date
    Jul 2012
    Posts
    31

    Unanswered: DateDiff

    Hello-
    I have a table with multiple rows for Members.
    I need to pull from this table the rows for Members cases where the difference between the start date for one instance is <31 days from the the end of another case for that member. They may have 2,3,4,5 cases show up on the initial table. For example, this member as 4 rows, but I only want the last three on the report:

    MemberID---Case ID-------Begin Dt-------End Dt
    89504------7144008014---2014-01-06---2014-01-10
    89504------7144155289---2014-04-11---2014-04-15
    89504------7144159173---2014-04-15---2014-04-17
    89504------7144177053---2014-04-25---2014-05-06

    This result is after using
    #DAYS(Begin Dt)-DAYS(End Dt)<31#

    Thanks
    Last edited by laurastreng; 05-16-14 at 12:42. Reason: Tried to make it more readable

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is untested, but I'd use:
    Code:
    SELECT *
       FROM MembersCases AS this
       WHERE EXISTS (SELECT 1
          FROM MembersCases AS prev
          WHERE  prev.MemberID = this.memberID
             AND prev.CaseID = this.CaseID
             AND prev.StartDt < this.StartDt
             AND this.StartDt - 31 Days < prev.EndDt)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2012
    Posts
    31
    Thank you very much. I understand what it is doing. Can I apply this to a subselect, i.e., MembersCases is actually a subquery below the "where exists" code you've given me?

    so ...
    Select * from

    (sql code pulling in the complete rows I need to extract those cases for members that have a start date and end date for another case less than 31 days) MembersCases

    where exists (.... now how do I add your code here???)

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I can tell that the answer is "yes", but I don't understand your query enough to answer the "how" part!

    Without knowing the structure of your table or tables, how you are joining them and what problem you're having accomplishing that goal, I'm not sure how to coach you. I'm 99% sure that you can just write a SELECT statement as you see fit then tack on my EXISTS clause and toddle merrily on your way!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2012
    Posts
    31
    Here is the basic gist of my query. The result of this code contains the 4 rows I'd provided as an example where I have multiple rows back for one member, some of which I need to keep as the date in CaseBeginDt falls within 30 days of another case's DischargeDt for that same member. The example I'd provided had 1 row that was in January, the other three fell within 30 days of each other. I need to exclude the case in January (outside of 30 days). How do I apply your suggested "where exists" code? Thank you

    Code:
    SELECT   
    a.CaseID, 
    a.CaseBeginDt, 
    b.DischargeDt, 
    a.MemberID
    
    FROM     DB2PROD.TMDTRPTMR a,            
    DB2PROD.TMDTRPPRVSRV b   
    
    WHERE    a.I_TRIMED_SYS_KEY=b.PRV.I_TRIMED_SYS_KEY   
    AND      a.C_TMR_TYPE = 'PC'  
    and 	b.D_SERV_BEG Between '2014-01-01' and '2014-04-30'  
    and     a.C_SBU <> '35'  
    and 	a.C_REPORT_CATG Not In ('FEP','MEDS','NATL')
    and     b.C_REMARK_CATG <> 'L'  
    and   b.Q_UNITS_APPR <> '0'


  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I had this blasted thing typed up two hours ago, but keep getting interrupted! I'll send it off, but feel free to ask questions if something isn't clear.

    -PatP
    Code:
    SELECT a.CaseID, a.CaseBeginDt
    ,  b.DischargeDt, a.MemberID
       FROM DB2PROD.TMDTRPTMR    AS a
       JOIN DB2PROD.TMDTRPPRVSRV AS b
          ON (b.PRV.I_TRIMED_SYS_KEY = a.I_TRIMED_SYS_KEY)
       WHERE  a.C_TMR_TYPE = 'PC'  
          AND a.C_SBU <> '35'  
          AND a.C_REPORT_CATG Not In ('FEP','MEDS','NATL')
          AND b.C_REMARK_CATG <> 'L'  
          AND b.Q_UNITS_APPR <> '0'
          AND b.D_SERV_BEG Between '2014-01-01' and '2014-04-30'  
          AND EXISTS (SELECT 1
             FROM DB2PROD.TMDTRPTMR    AS c
             JOIN DB2PROD.TMDTRPPRVSRV AS d
                ON (d.PRV.I_TRIMED_SYS_KEY = c.I_TRIMED_SYS_KEY)
             WHERE  c.MemberID = d.MemberID                  -- Match patient
                AND d.DischargeDt <> b.DischargeDt           -- Exclude "b" row
                AND d.DischargeDt - 31 Days < a.CaseBeginDt) -- Interesting date
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I need to pull from this table the rows for Members cases
    where the difference between the start date for one instance is <31 days from the the end of another case for that member.
    If a gap more than 30 days was in middle of a member(like memberID = 89506 in the following sample),
    which row(s) should take and which row(s) should drop?

    MembersCases table:
    Code:
    MemberID Case_ID    Begin_Dt   End_Dt
    89504    7144008014 2014-01-06 2014-01-10 /* Drop */
    89504    7144155289 2014-04-11 2014-04-15 /* Take */
    89504    7144159173 2014-04-15 2014-04-17 /* Take */
    89504    7144177053 2014-04-25 2014-05-06 /* Take */
    
    89506    6144008014 2014-01-06 2014-02-10
                                /* 2014-03-11 - 2014-02-10 = 29 Days */
    89506    6144155289 2014-03-11 2014-03-12 /* Drop this? */
                                /* 2014-04-15 - 2014-03-12 = 34 Days */
    89506    6144159173 2014-04-15 2014-04-17 /* or Drop this? */
                                /* 2014-04-25 - 2014-04-17 =  8 Days */
    89506    6144177053 2014-04-25 2014-05-06
    Last edited by tonkuma; 05-17-14 at 01:51.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by tonkuma View Post
    If a gap more than 30 days was in middle of a member(like memberID = 89506 in the following sample),
    which row(s) should take and which row(s) should drop?
    You make a good point, which I considered while constructing my query. That's why I kept the date logic on one line so that no matter which choice was needed the change would be trivial.

    i took my best guess and assumed that the "base" row and not the row with the additional discharge date should be returned. This query has evolved a bit since the original post, and it could spin a number of different ways.

    All I can do is answer questions as posted, and sometimes ask questions to help the user realized what they might be missing. Without access to detailed problem specifications, anything else is just a guess.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jul 2012
    Posts
    31
    Hi-
    Thank you for your responses. I'll give your code a whirl -looks like it should give me what I need which is a list of members who were hospitalized, discharged, then readmitted within 30 days of the previous hospitalization.
    I'll be back with questions if need be.
    Thanks again!
    Laura

  10. #10
    Join Date
    Jul 2012
    Posts
    31
    I had to adjust the code because the member ID is only on one of the tables. I decided I could pull in the information on the second table after getting the right admits on the base result set. That being said, this is how I tweaked the code, which isn't quite right because I'm still getting 4 cases for the one member (the one that I don't want to see the January case).
    Code:
    SELECT a.I_CONTRACT,a.D_BIRTH,a.N_FIRST,a.N_LAST,a.I_TRIMED_SYS_KEY,a.D_TMR_BEGIN,a.D_TMR_END,
    a.D_CREATED,a.C_REPORT_CATG,a.C_DIAG_PRIN, a.T_DIAG_PRIN,a.C_SERVICE_CAT
    
    FROM     DB2PROD.TMDTRPTMR a, 
             DB2PROD.TMDTRPTMR b 
    WHERE    a.I_TRIMED_SYS_KEY=b.I_TRIMED_SYS_KEY 
    AND      a.I_CONTRACT=b.I_CONTRACT
    and a.C_TMR_TYPE = 'PC'  
          AND a.C_SBU <> '35'  
          AND a.C_REPORT_CATG Not In ('FEP','MEDS','NATL')
    	and a.C_SERVICE_CAT in ('MED','SUR','MAT')
      AND a.D_TMR_BEGIN Between '2014-01-01' and '2014-04-30'  
    AND EXISTS (SELECT 1
             FROM DB2PROD.TMDTRPTMR    AS c
             JOIN DB2PROD.TMDTRPTMR AS d
                ON (d.I_TRIMED_SYS_KEY = c.I_TRIMED_SYS_KEY)
             WHERE  c.I_CONTRACT = d.I_CONTRACT                  -- Match patient
                AND d.D_TMR_END <> b.D_TMR_END           -- Exclude "b" row
                AND d.D_TMR_END - 31 Days < a.D_TMR_BEGIN) -- Interesting date
    and, what I really want is to only pull back those members with more than one admission. I tried to do a group by... having count>1, but can't figure out how to pull in detail.

    This isn't right, but shows what I was trying to do
    Code:
    SELECT 
      TMR.I_CONTRACT, 
    TMR.D_BIRTH,            
    TMR.C_GENDER,
    TMR.N_FIRST, 
    TMR.N_LAST,
    TMR.C_REPORT_CATG,
    TMR.I_TRIMED_SYS_KEY, 
    TMR.C_TMR_TYPE,            
    TMR.D_TMR_BEGIN, 
    TMR.D_TMR_END,
    TMR.C_DIAG_PRIN,
    TMR.T_DIAG_PRIN, 
    TMR.C_SERVICE_CAT,      
    PRV.I_PROVIDER_NUM,
    
    
    FROM     DB2PROD.TMDTRPTMR TMR,            
    DB2PROD.TMDTRPPRVSRV PRV,   
    (SELECT a.I_CONTRACT,a.D_BIRTH,a.N_FIRST,a.N_LAST,count(*)as "Row_Count" 
    
    FROM     DB2PROD.TMDTRPTMR a, 
             DB2PROD.TMDTRPTMR b 
    WHERE    a.I_TRIMED_SYS_KEY=b.I_TRIMED_SYS_KEY 
    AND      a.I_CONTRACT=b.I_CONTRACT
    and a.C_TMR_TYPE = 'PC'  
          AND a.C_SBU <> '35'  
          AND a.C_REPORT_CATG Not In ('FEP','MEDS','NATL')
    	and a.C_SERVICE_CAT in ('MED','SUR','MAT')
      AND a.D_TMR_BEGIN Between '2014-01-01' and '2014-04-30'  
    AND EXISTS (SELECT 1
             FROM DB2PROD.TMDTRPTMR    AS c
             JOIN DB2PROD.TMDTRPTMR AS d
                ON (d.I_TRIMED_SYS_KEY = c.I_TRIMED_SYS_KEY)
             WHERE  c.I_CONTRACT = d.I_CONTRACT                  -- Match patient
                AND d.D_TMR_END <> b.D_TMR_END           -- Exclude "b" row
                AND d.D_TMR_END - 31 Days < a.D_TMR_BEGIN) -- Interesting date
     group by a.I_CONTRACT, a.D_BIRTH, a.C_GENDER, a.N_FIRST, a.N_LAST
    
    
    HAVING (COUNT(1) > 1))e
    
    WHERE    TMR.I_TRIMED_SYS_KEY=PRV.I_TRIMED_SYS_KEY   
    AND      TMR.C_TMR_TYPE = 'PC'  
    and   PRV.D_SERV_BEG Between '2014-01-01' and '2014-04-30'  
    and      TMR.C_SBU <> '35'  
    and   TMR.C_REPORT_CATG Not In ('FEP','MEDS','NATL')
    and   TMR.C_SERVICE_CAT In ('MED','SUR','MAT')
    and   PRV.C_HCFA_POT ='21'  
    and      PRV.C_REMARK_CATG <> 'L'  
    and      PRV.Q_UNITS_APPR <> '0' 
    and TMR.I_CONTRACT=e.I_CONTRACT and TMR.D_BIRTH=e.D_BIRTH
    
    GROUP BY TMR.I_CONTRACT, 
    TMR.D_BIRTH,            
    TMR.C_GENDER,
    TMR.N_FIRST, 
    TMR.N_LAST,
    TMR.C_REPORT_CATG,
    TMR.I_TRIMED_SYS_KEY, 
    TMR.C_TMR_TYPE,            
    TMR.D_TMR_BEGIN, 
    TMR.D_TMR_END,
    TMR.C_DIAG_PRIN,
    TMR.T_DIAG_PRIN, 
    TMR.C_SERVICE_CAT,      
    PRV.I_PROVIDER_NUMBER
    
    ORDER BY
    TMR.N_LAST,
    TMR.N_FIRST, 
    TMR.I_CONTRACT, 
    TMR.D_BIRTH,            
       
    TMR.D_TMR_BEGIN, 
    TMR.D_TMR_END

    any insight is appreciated.
    thanks

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by laurastreng View Post
    (the one that I don't want to see the January case).
    Aha! That was the clue that I needed!
    Code:
    SELECT a.I_CONTRACT, a.D_BIRTH, a.N_FIRST
    ,  a.N_LAST, a.I_TRIMED_SYS_KEY, a.D_TMR_BEGIN
    ,  a.D_TMR_END, a.D_CREATED, a.C_REPORT_CATG
    ,  a.C_DIAG_PRIN, a.T_DIAG_PRIN, a.C_SERVICE_CAT
       FROM DB2PROc.TMDTRPTMR AS a 
       JOIN DB2PROc.TMDTRPTMR AS b
          ON (b.I_TRIMED_SYS_KEY = a.I_TRIMED_SYS_KEY
          AND b.I_CONTRACT       = a.I_CONTRACT)
       JOIN DB2PROc.TMDTRPTMR AS c
          ON (c.I_TRIMED_SYS_KEY = a.I_TRIMED_SYS_KEY
          AND c.I_CONTRACT       = a.I_CONTRACT      -- Match patient
          AND c.D_TMR_END        < b.D_TMR_END)      -- Earlier visit
       WHERE  a.C_TMR_TYPE = 'PC'  
          AND a.C_SBU <> '35'  
          AND a.C_REPORT_CATG Not In ('FEP', 'MEDS', 'NATL')
          AND a.C_SERVICE_CAT in ('MED', 'SUR', 'MAT')
          AND a.D_TMR_BEGIN Between '2014-01-01' and '2014-04-30'  
          AND c.D_TMR_END < b.D_TMR_BEGIN - 31 Days) -- Interesting date
    The "aha" lead to a much simpler query, that I think ought to get you the results that you want.

    -PatP
    Last edited by Pat Phelan; 05-19-14 at 23:36. Reason: Fixed typo!
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Jul 2012
    Posts
    31
    Hi-
    No rows returned when I ran the above code.

    My one thought is, if all of the I_TRIMED_SYS_KEY fields are joined, how would the comparison code work?

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without having your tables/data to work with I can't experiment to figure out what I might have done wrong. It is almost certainly in the last line (with the comment: Interesting date). Please think through (and maybe add the c.D_TMR_END and b.D_TMR_BEGIN rows as diagnostics to your result set).

    I probably just fumbled something in the comparison, but without seeing your data/results I'm not willing or able to spend more time on this.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    If a gap more than 30 days was in middle of a member(like memberID = 89506 in the following sample),
    which row(s) should take and which row(s) should drop?

    MembersCases table:
    Code:
    MemberID Case_ID    Begin_Dt   End_Dt
    89504    7144008014 2014-01-06 2014-01-10 /* Drop */
    89504    7144155289 2014-04-11 2014-04-15 /* Take */
    89504    7144159173 2014-04-15 2014-04-17 /* Take */
    89504    7144177053 2014-04-25 2014-05-06 /* Take */
    
    89506    6144008014 2014-01-06 2014-02-10
                                /* 2014-03-11 - 2014-02-10 = 29 Days */
    89506    6144155289 2014-03-11 2014-03-12 /* Drop this? */
                                /* 2014-04-15 - 2014-03-12 = 34 Days */
    89506    6144159173 2014-04-15 2014-04-17 /* or Drop this? */
                                /* 2014-04-25 - 2014-04-17 =  8 Days */
    89506    6144177053 2014-04-25 2014-05-06
    I hope you to publish your expected results from my example.
    (If the sample data never exist, please explain where was the inconsistency.)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •