SQL Server Performance

Easy way to find out if date is in a given period

Discussion in 'Getting Started' started by mikachu, May 10, 2007.

  1. mikachu New Member

    Hi!

    I got two questions related to my application. Its database structure looks like this:

    RowID | StartDate | EndDate | Amount
    ---------------------------------------
    1 | 26-Feb-07 | 05-Apr-07 | 1111
    2 | 08-Apr-07 | 12-Apr-07 | 2222
    3 | 13-Apr-07 | 09-May-07 | 3333
    4 | 31-Mar-07 | 11-May-07 | 4444

    In the application side the user gives a period (e.g. from 1-Apr-2007 to 30-Apr-2007).
    Now I would like to find only those rows, that belongs to given period. How? Obviously it's relatively simple to find rows 1-3 by using
    WHERE (StartDate BETWEEN '01-Apr-2007' AND '30-Apr-2007') OR (EndDate BETWEEN '01-Apr-2007' AND '30-Apr-2007) .. but how to find the 4th row?

    The second question concerns calculating the amount of days within the period. Is there an easy way to do this by using SQL functions?

    The result would be: (only the number is needed)
    Row 1: 5 (01-Apr to 05-Apr)
    Row 2: 5 (08-Apr to 12-Apr)
    Row 3: 18 (13-Apr to 30-Apr)
    Row 4: 30 (whole April)

    Thanks!

    -Mika
  2. mikachu New Member

    Okay, the answer to the first question was very apparent
    ... OR (StartDate < '01-Apr-2007') AND (EndDate > '30-Apr-2007')

    Any ideas to second question?
  3. FrankKalis Moderator

    Check out the DATEDIFF function.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  4. Madhivanan Moderator

    Datediff(day,date1,date2)

    Madhivanan

    Failing to plan is Planning to fail
  5. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />Datediff(day,date1,date2)<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Nope, there would need to be a CASE expression if Date1 is less than startdate. Likewise for Date2 and EndDate. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  6. Adriaan New Member

    As soon as you start to mix-and-match AND and OR, you need to pay close attention to bracketing.

    You're not clear about the given period:

    If either StartDate or EndDate is within the given period, then you have an overlap:
    WHERE ((StartDate BETWEEN @Start AND @End) OR (EndDate BETWEEN @Start AND @End))

    If both StartDate and EndDate are within the given period, then the whole period is within the given period:
    WHERE (StartDate BETWEEN @Start AND @End) AND (EndDate BETWEEN @Start AND @End)

    I guess you're looking for the first option!

    Note - try to use either the US mm/dd/yyyy date format, or the 'universal' yyyy-mm-dd format in your scripts. Spelling out months means you're depending on the server's language settings.

    For the number of days, use the DATEDIFF function:

    SELECT DATEDIFF(d, StartDate, EndDate) FROM MyTable

    ... and please post this type of question in the "New SQL Server Users / Getting Started" section of the forum,http://www.sql-server-performance.com/forum/forum.asp?FORUM_ID=48
  7. Madhivanan Moderator

    &lt;&lt;<br />Note - try to use either the US mm/dd/yyyy date format, or the 'universal' yyyy-mm-dd format in your scripts. <br />&gt;&gt;<br /><br />I would strongly suggest universal format [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  8. mikachu New Member

    <br />Thanks for prompt replies. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">If either StartDate or EndDate is within the given period, then you have an overlap:<br />WHERE ((StartDate BETWEEN @Start AND @End) OR (EndDate BETWEEN @Start AND @End))<br /><br />If both StartDate and EndDate are within the given period, then the whole period is within the given period:<br />WHERE (StartDate BETWEEN @Start AND @End) AND (EndDate BETWEEN @Start AND @End)<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />I'm afraid these does not work if neither StartDate or EndDate are in given period (see row 4). E.g. if user defines the period to be from 01-Apr (@Start) to 30-Apr (@End), and StartDate is 31-Mar and EndDate 11-May. In this case StartDate would not be BETWEEN @Start and @End. Same with EndDate.. <br /><br /><font face="Courier New">...@Start.............................@End<br />....01-Apr...USER DEFINED PERIOD..... 30-Apr<br />......|---------------------------------|<br /><br />.31-Mar....................................11-May<br />...|-----------------row4--------------------|</font id="Courier New"><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"> Note - try to use either the US mm/dd/yyyy date format, or the 'universal' yyyy-mm-dd format in your scripts. Spelling out months means you're depending on the server's language settings.<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />This is a good point and I in fact use CONVERT function in the actual code. <br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">... and please post this type of question in the "New SQL Server Users / Getting Started" section of the forum,<a target="_blank" href=http://www.sql-server-performance.com/forum/forum.asp?FORUM_ID=48>http://www.sql-server-performance.com/forum/forum.asp?FORUM_ID=48</a><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Jep.. [:I]
  9. Adriaan New Member

    quote:I'm afraid these does not work if neither StartDate or EndDate are in given period (see row 4). E.g. if user defines the period to be from 01-Apr (@Start) to 30-Apr (@End), and StartDate is 31-Mar and EndDate 11-May. In this case StartDate would not be BETWEEN @Start and @End. Same with EndDate..
    OK then - to include the oversized period ...

    WHERE ((StartDate BETWEEN @Start AND @End)
    OR (EndDate BETWEEN @Start AND @End)
    OR (StartDate > @Start AND EndDate < @Start))
    quote:I in fact use CONVERT function in the actual code.
    Are you applying CONVERT to the table columns? Not recommended! Show us what you have now.
  10. Madhivanan Moderator

    <<
    This is a good point and I in fact use CONVERT function in the actual code.
    >>

    Why?

    Madhivanan

    Failing to plan is Planning to fail
  11. mikachu New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />WHERE ((StartDate BETWEEN @Start AND @End)<br />OR (EndDate BETWEEN @Start AND @End)<br />OR (StartDate &gt; @Start AND EndDate &lt; @Start))<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Guess the last OR clause should be like (to find out also row4 in orig. posting): <br />OR (StartDate &lt; @Start) AND (EndDate &gt; @End)<br /><br />Anyway, this seems to work fine now. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><font face="Courier New"> <br />SELECT *, <br />DATEDIFF(D, StartDate, EndDate) +1 AS IntervalSteps,<br />DATEDIFF(D, StartDate, @Start) - 1 AS IntervalStepsStart, <br />DATEDIFF(D, @End, EndDate) + 1 AS IntervalStepsEnd, <br />DATEDIFF(D, @Start, @End) + 1 AS PeriodSteps<br />FROM vDataIsTakenHere<br />WHERE <br />(<br />(StartDate BETWEEN @Start AND @End)<br />OR <br />(EndDate BETWEEN @Start AND @End)<br />OR<br />(StartDate &lt; @Start) AND (EndDate &gt; @End)<br />)<br /></font id="Courier New"><br /><br />Now I still need to calculate the date differences to find out how many days each one of the rows have in a given period.<br /><br />Dunno if there is an easy way to do this, but this is how I was thinking to create it:<br />1) Calculate the date difference for each row (IntervalSteps)<br />2) Calculate the difference for StartDate and given period start date (IntervalStepsStart)<br />3) --^ Same for end date<br />4) Finally the total number of days in given period<br /><br />Then I would need to handle all the 4 cases as follows:<br />1) Case 1 (see row1 in original posting)<br />IF (IntervalStepsStart &gt; 0) AND (IntervalStepsEnd &lt; 0) THEN<br /> NumberOfDaysInPeriod = IntervalSteps - IntervalStepsStart<br />END<br /><br />2) Case 2<br />IF (IntervalStepsStart &lt; 0) AND (IntervalStepsEnd &lt; 0) THEN<br /> NumberOfDaysInPeriod = IntervalSteps<br />END<br /><br />3) Case 3<br />IF (IntervalStepsStart &lt; 0) AND (IntervalStepsEnd &gt; 0) THEN<br /> NumberOfDaysInPeriod = PeriodicSteps + IntervalStepsStart<br />END<br /><br />4) Case 4<br />IF (IntervalStepsStart &gt;= 0) AND (IntervalStepsEnd &gt;= 0) THEN<br /> NumberOfDaysInPeriod = PeriodicSteps <br />END<br /><br /><br />So .. as it seems it becomes pretty confusing. That's why the original question: is there a function in SQL that could do this trick?<br />
  12. FrankKalis Moderator

  13. mikachu New Member

    Probably not the most optimized solution, but works smoothly <img src='/community/emoticons/emotion-1.gif' alt=':)' /> Thanks for help guys!<br /><br />SELECT *, <br />CASE IntervalType<br /> WHEN 1 THEN IntervalSteps-IntervalStepsStart<br /> WHEN 2 THEN IntervalSteps<br /> WHEN 3 THEN PeriodSteps+IntervalStepsStart<br /> WHEN 4 THEN PeriodSteps<br /> ELSE 1<br />END DaysWithinPeriod<br /><br />FROM (<br />SELECT *,<br />IntervalType = <br />CASE<br /> WHEN (IntervalStepsStart &gt;= 0) AND (IntervalStepsEnd &lt;= 0) THEN 1<br /> WHEN (IntervalStepsStart &lt;= 0) AND (IntervalStepsEnd &lt;= 0) THEN 2<br /> WHEN (IntervalStepsStart &lt;= 0) AND (IntervalStepsEnd &gt;= 0) THEN 3<br /> WHEN (IntervalStepsStart &gt;= 0) AND (IntervalStepsEnd &gt;= 0) THEN 4<br />END <br /><br />FROM (<br /> SELECT *, <br /> DATEDIFF(D, StartDate, @Start) AS IntervalStepsStart, <br /> DATEDIFF(D, @End, EndDate) + 1 AS IntervalStepsEnd, <br /> DATEDIFF(D, @Start, @End) + 1 AS PeriodSteps<br /> FROM vDataIsTakenHere<br /> WHERE <br /> (<br /> (StartDate BETWEEN @Start AND @End)<br /> OR <br /> (EndDate BETWEEN @Start AND @End)<br /> OR<br /> (StartDate &lt; @Start) AND (EndDate &gt; @End)<br /> )<br /> ) Dataset1<br />) Dataset2<br />
  14. Adriaan New Member

    Using CASE is still the most direct option for getting the IntervalType ...

    SELECT
    (CASE WHEN (StartDate >= @Start) AND (EndDate <= @End) THEN 1
    ELSE CASE WHEN (StartDate <= @Start) AND (EndDate BETWEEN @Start AND @End) THEN 2
    ELSE CASE WHEN (StartDate BETWEEN @Start AND @End) AND (EndDate <= @End) THEN 3
    ELSE CASE WHEN (StartDate BETWEEN @Start AND @End) AND (EndDate > @End) THEN 4 END END END END) IntervalType
    FROM vDataIsTakenHere

    Look: no DATEDIFF in sight!
  15. Adriaan New Member

    Well, not <i>exactly</i> those criteria, but you should get the point.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page