UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Fastest Way To Find Out If Date Range Is Within A Date Range, Any Versions    
 
   
cocoflipper
post Mar 22 2018, 11:02 AM
Post#1



Posts: 1,184
Joined: 11-August 03
From: Denver - CO


Hi all,

I'm doing some revisions to an Access/SQL Server solution, and found that many times I am trying to find out if a date range is within a date range.

To clarify, it would be a situation where perhaps someone is on medications for a length of time and I would want to know if a person was on medications during any specified timeframe (returning a 1 for yes and 0 for no)

Example : For the selected date range of 1/1/2017 - 12/31/2017, how many people were on x medication.
- person 1 has an entry for the medication with a start date of 3/14/2017, no end date ---> return 1
- person 2 has no entry in the table --> return 0
- person 3 has an entry for the medication with a start date of 10/20/2013, and an end date of 2/24/2018---> return 1
- person 4 has an entry for the medication with a start date of 1/23/2018, and an end date of 2/24/2018---> return 0
etc, etc.

Here is a function (below) I use to get this information. I'm not sure if it is the *optimal* way to get this information.

--> **** My question - is this the fastest way to get my output, which is whether a date range is within a date range? ***** <---

Note: In Access, I use a pass through query to update the date range table in SQL with a start and end date for the period I want to look at (record tied to the user's ID). That start and end date selection of the user is then the output of the view used in the function below.

CODE
-- ====================================================================
-- Description:    Assesses whether a period of time has any Start or End which allows it to fall within the
--               specified date range (View = vwdr)
--              Three senarios fit:
--                1. Start date of period within date range (between vwdr.StartDate and vwdr.EndDate)
--                2. End date of period within date range (between vwdr.StartDate and vwdr.EndDate)
--                  ** NOTE : both start and end date of period could be between date range, so not counting it
--                            as separate senario
--                3. Either start or end of date range is within period (between Start and End of period)
--              If any of the three above senarios fit, return 1.  Otherwise return 0
-- ====================================================================
CREATE FUNCTION [dbo].[AssessDateRange_wi_vwdr] (@StartDate datetime, @EndDate datetime)
RETURNS int
    -- return int instead of bit so that function can be used in max, min, etc. operators
AS
BEGIN
DECLARE @rtnVar int;
DECLARE @vwdrStartDate datetime;
DECLARE @vwdrEndDate datetime;

    BEGIN
    SELECT @vwdrStartDate = StartDate, @vwdrEndDate = EndDate
    FROM vwdr
    
    IF(@StartDate Between @vwdrStartDate And @vwdrEndDate) OR
        (isnull(@EndDate , GETDATE()) Between @vwdrStartDate And @vwdrEndDate) OR
        (@vwdrStartDate Between @StartDate And isnull(@EndDate , GETDATE()))
        SET @rtnVar = 1
    ELSE
        SET @rtnVar =     0    
    END
    RETURN @rtnVar
END


Go to the top of the page
 
LPurvis
post Mar 22 2018, 01:20 PM
Post#2


UtterAccess Editor
Posts: 16,345
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

Presumably, you have a table of some sort, which contains person, med and the date range that for which it is valid.
TableName
--------
PersonID
MedType
StartDate
EndDate

And you want to determine which of these are between a specified period? (Specified as a set of fixed dates/parameters, or between dates stored in some other table?)

The requirement for intersection (not necessarily being contained) is just that the:
Start is before the Limit End and the End is ebfore the Limit Start.

SELECT * FROM TableName
WHERE StartDate <= @EndDate AND EndDate >=@StartDate
WHERE MedType = @MedType

You'll need to provide more if the scenario is different.

Cheers
Go to the top of the page
 
LPurvis
post Mar 22 2018, 01:28 PM
Post#3


UtterAccess Editor
Posts: 16,345
Joined: 27-June 06
From: England (North East / South Yorks)


Of course, if there's the possibility that the same person could appear more than once within a time period and you want to exclude those, then I suppose

SELECT COUNT(DISTINCT PersonID) As YourCount FROM TableName
WHERE StartDate <= @EndDate AND EndDate >=@StartDate
WHERE MedType = @MedType

If you were running it Access client side (I can't imagine why you would) then you'd not have COUNT DISTINCT at your disposal. But an aggregate subquery would soon set you on course.

Cheers
Go to the top of the page
 
cocoflipper
post Mar 22 2018, 01:57 PM
Post#4



Posts: 1,184
Joined: 11-August 03
From: Denver - CO


Hi LPurvis,

This is actually a little tricker than that, and the parameters should be contained in the function I wrote, but, a picture is worth a thousand words, right? so I've included a diagram here to show the three scenarios (four really, but scenario three is contained in scenarios 1 and 2).

This would be all server side, so trying to optimize SQL Server to pull this data as fast as possible.

Thanks for the feedback!

Also - one additional item here....some of the records will have no end date (since on the medication), but I account for that by converting the end date to today with GETDATE, then assessing whether it is current using that as the end date.
This post has been edited by cocoflipper: Mar 22 2018, 01:59 PM
Attached File(s)
Attached File  Date_Range_Assessment_Visual.png ( 17.35K )Number of downloads: 9
 
Go to the top of the page
 
LPurvis
post Mar 22 2018, 02:19 PM
Post#5


UtterAccess Editor
Posts: 16,345
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

The complication of without an end date does seem an issue, I grant you. But surely with no end date at all, is a date far off in the future not preferable? So that the Start Date is all that impacts the results?
(But it's your choice if the current date is preferred.)

So that aside... have you actually tried what I offered? :-p

Cheers
Go to the top of the page
 
cocoflipper
post Mar 22 2018, 02:38 PM
Post#6



Posts: 1,184
Joined: 11-August 03
From: Denver - CO


Sometimes I feel like I come up with the BEST methods to do things, and completely overlook the simple. I could save hours if I hit these message boards more often

Yes - just looked at your suggestion and it fits all scenarios in my visual. Do know why that was so hard for me iconfused.gif - thanks for the suggestion! cheers.gif
This post has been edited by cocoflipper: Mar 22 2018, 02:38 PM
Go to the top of the page
 
zaxbat
post Mar 22 2018, 03:53 PM
Post#7



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


To find out if two distinct date ranges overlap you must do four (4) comparisons. I know because I was making a medical report earlier today and that is what it came down to.

medication start and end dates can be m1 and m2

report start and end dates can be r1 and r2

So when I try to print a report containing every patient who received medication within the date range i must check:

m1 between r1 and r2
or
m2 between r1 and r2
or
r1 between m1 and m2
or
r2 between m1 and m2


This is the only chain of logic that will catch all pertinent records for you. But I am not clear on if this is exactly what you are trying to do. It sure look like it.

The code might look similar to this excerpt from my working report: (concentrate on the WHERE clause showing the date checks. for my project the client wanted todays date put in as medication end date if no date was present so that is what the adjusted end date is all about)


CODE
         MySqlTxt = " SELECT zCOMPLETE.*"
         MySqlTxt = MySqlTxt & _
            " INTO " & MyTmpTblName & _
            " FROM zCOMPLETE" & _
            " WHERE (" & _
            "       ((MedStartDate Between #" & GlobalStartDate & "# And #" & GlobalEndDate & "#)" & _
            "    OR " & _
            "       (IIf(IsDate([medenddate]),[medenddate],#" & AdjustedEndDate & "# ) Between #" & GlobalStartDate & "# And #" & GlobalEndDate & "#)" & _
            "    OR " & _
            "       (#" & GlobalStartDate & "# Between medstartdate And (IIf(IsDate([medenddate]),[medenddate], #" & AdjustedEndDate & "#)))" & _
            "    OR " & _
            "       (#" & GlobalEndDate & "# Between medstartdate And (IIf(IsDate([medenddate]),[medenddate], #" & AdjustedEndDate & "#))))" & _
                    wheretag & wherestat & wherefacility & _
            "       )" & _
            " ORDER BY facility & Station, Psort, AdmDate Desc, DTDATE Desc, MedStartDate Desc, LabDate Desc, RadDate Desc, CULDate Desc," & _
            "         GramDate Desc, BUGDate Desc, IntDate Desc, DevDate Desc" & _
            ";"

This post has been edited by zaxbat: Mar 22 2018, 04:01 PM
Go to the top of the page
 
LPurvis
post Mar 22 2018, 05:55 PM
Post#8


UtterAccess Editor
Posts: 16,345
Joined: 27-June 06
From: England (North East / South Yorks)


zaxbat, without looking too long at your code, have you also tried the method? Just tried it?

Cheers
Go to the top of the page
 
LPurvis
post Mar 22 2018, 05:55 PM
Post#9


UtterAccess Editor
Posts: 16,345
Joined: 27-June 06
From: England (North East / South Yorks)


cocoflipper, no problem. Glad you got there ;-)

Cheers
Go to the top of the page
 
zaxbat
post Mar 22 2018, 10:24 PM
Post#10



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Hi, i looked at your method. It seems to account for only one date range and not two. I specifically need to include all records where the two different date ranges overlap. There are 4 ways in which they might overlap so there must be 4 checks to make sure you catch them all.
Go to the top of the page
 
cheekybuddha
post Mar 23 2018, 05:31 AM
Post#11


UtterAccess Moderator
Posts: 13,045
Joined: 6-December 03
From: Telegraph Hill


Zaxbat,

I have only skimmed this thread, but sometimes it's useful to know that searching for what things are not is often easier than searching for things that are!

See Allen Browne for an explanation of what I guess Leigh is trying to get you to try.

hth,

d

Go to the top of the page
 
zaxbat
post Mar 23 2018, 08:52 AM
Post#12



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Yes sure.....you can take the is the cup half full or half empty approach....either will give same result. The Allen Browne method still requires 4 comparisons OR'd with each other. No gain, no loss, simply approached form a different angle. The biggest problem I have with Allen Browne's approach is that I really have to study it to see that it works. With my approach, I feel it is much clearer what the code is doing.
Go to the top of the page
 
LPurvis
post Mar 23 2018, 09:32 AM
Post#13


UtterAccess Editor
Posts: 16,345
Joined: 27-June 06
From: England (North East / South Yorks)


OK
I'm all for reasoning something out, looking at it logically and forming conditions based on that.
I used to do that for this scenario, and then noticed that it'd be simpler to switch to the NOT condition, a bit like Allen Brown also demonstrates.

However, there are times when you can just pick up a useful snippet that may just require you to notice something. (Or just try something and take someone's word it's worth trying :-p You have to determine if it's someone who you feel their word is worth much of course. :-s)
So look at cocoflipper's diagram earlier.
Line 1 (at the top) is showing us the range in which we're interested.
Notice anything about every line under that? (The ones in which we're interested, that are considered a match)

Every one of the has a start date which is before the end date of the range.
Every one of them has an end date which is after the start date of the range.

That's two conditions that includes them all.
So now, just reassure yourself that the only possibilities that we don't want aren't satisfied by those two conditions.
If a range is entirely before or after the range (so no intersection) then we don't want it.
If it's before, then it fails on the 2md condition as its end date is not after the start of the range.
If it's after then it fails on the 1st condition as the start date is not before the end date.

So you can actually describe the requirement, using two conditions. Or, as I put it earlier (and cocoflipper then actually tried)
WHERE StartDate <= @EndDate AND EndDate >=@StartDate

Cheers
Go to the top of the page
 
zaxbat
post Mar 23 2018, 10:50 AM
Post#14



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


I admit it is slick and part of me does like it. But I worry should anything anywhere in the rest of that SQL go haywire....that segment will be the first to fall under suspicion and may be thrown out since it is not apparent that it actually works without dissecting and diagramming it first.
Go to the top of the page
 
cheekybuddha
post Mar 23 2018, 10:59 AM
Post#15


UtterAccess Moderator
Posts: 13,045
Joined: 6-December 03
From: Telegraph Hill


Perhaps seeing it graphically using your conditions might help:
CODE
Zaxbat conditions - any one of four must be satisfied
1   m1 between r1 and r2
    or
2   m2 between r1 and r2
    or
3   r1 between m1 and m2
    or
4   r2 between m1 and m2

Leigh's condition - both must be satisfied
5   m1 <= r2
    and
6   m2 >= r1

                                                            Satisfied
              r1 <------------------> r2                  1 2 3 4   5 6
              
                        m1 <----------------> m2          y n n y   y y
                        m1 <--> m2                        y y n n   y y
      
      m1 <----------------> m2                            n y y n   y y
                    m1 <--> m2                            y y n n   y y

      m1 <----------------------------------> m2          n n y y   y y


When hitting a large recordset you might have to consider whether you compare on 2 conditions vs 4.

thumbup.gif

d
Go to the top of the page
 
zaxbat
post Mar 23 2018, 11:41 AM
Post#16



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Very pertinent point. Since my data range overlap schema pertains to many of the projects reports....I intend to do a time test on both options. If my current method is considerably slower I will switch. Fair enough?
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th July 2020 - 02:26 PM