Post reply

Calculate the number of months for overlapping dates in data set

  • January 30, 2024 at 7:08 pm

    Go to Answer

    #4370313

    Hi,

    I am trying to figure out the number of months for the overlapping start date and end date for a dataset I have. I have written the following query which from the research I have done should give me each overlapping date ranges and the number of months associated with the range:

    SELECT

    ID

    ,EH1.[Start Date]

    ,EH1.[End Date]

    ,DATEDIFF(month,EH1.[Start Date],EH1.[End Date]) [Total Months]

    FROM [tblDateRangeData] EH, [tblDateRangeData] EH1

    WHERE EH.[ID] = EH1.[ID]

    AND EH.[Start Date] < EH1.[Start Date]

    AND EH.[End Date] > EH1.[End Date]

    AND EH.[Relevant] = 'Yes'

    ORDER BY EH1.[ID],EH1.[Start Date]

    What is happening is the query is not returning all of the date ranges that overlap, I am wondering what I am doing wrong. I have a small dataset that I am using with the query, it is attached, and I highlighted the date ranges in the file that are not returning in the query. Any help is greatly appreciated.

    Thanks,

    Nelson B.

    Attachments:
    You must be logged in to view attached files.

  • January 30, 2024 at 9:32 pm

    Answer

    #4370380

    For future reference, many people are wary of opening random files from the Internet.  If you want help, please post a script to create a TEMP table and insert the data into the table using the {;}Code button above.

    Looking at your query, I'm guessing that the problem with your query is that your criteria are wrong.  There are 11 distinct relations between two intervals 9 of which overlap.  Your query only looks for 1 of those 9 ('Contains').  This is because you are making the common mistake of comparing start date to start date and end date to end date.  You need to compare the start date of each with the end date of the other.  Try the following:

    SELECT
           ID
          ,EH1.[Start Date]
          ,EH1.[End Date]
          ,DATEDIFF(month,EH1.[Start Date],EH1.[End Date]) [Total Months]
    FROM [tblDateRangeData] EH, [tblDateRangeData] EH1
    WHERE EH.[ID] = EH1.[ID]
      AND EH.[Start Date] < EH1.[End Date]  -- changed from EH1.[Start Date]
      AND EH.[End Date] > EH1.[Start Date]  -- changed from EH1.[End Date]
      AND EH.[Relevant] = 'Yes'
    ORDER BY EH1.[ID],EH1.[Start Date]

    This currently uses full-open date ranges (< and >).  You may want to change to (half) closed (<= and/or >=).

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • January 30, 2024 at 10:30 pm

    #4370392

    Thanks for the advice on the file, I will make sure I do the scripts next time and I will give your suggestion a try.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply