locked
SQL Start and Finish date query RRS feed

  • Question

  • User-1256377279 posted

    Hi All,

    I have very complex query request, not sure if anybody can help me to solve this.

    We have table call calendar which has below fields

    Calendar ID (Int)

    Calendar Name (Varchar)

    Start Date (Datetime)

    Finish Date (Datetime)

    Query: [Calendar].[Start] and [Calendar].[Finish]


    Note : Where used together, the Date From and Date To fields should return all calendar records falling completely or partially within the range.

    e.g. if Date From = 01/02/2016 and Date To = 29/02/2016, then return the following results

    Calendar.Start 31/01/2016, Calendar.Finish 01/02/2016
    Calendar.Start 10/02/2016, Calendar.Finish 20/02/2016
    Calendar.Start 15/02/2016, Calendar.Finish blank
    Calendar.Start 28/02/2016, Calendar.Finish 01/03/2016

    Note : Where Date From is present and Date To is not, then return all calendar records which start, finish or are in progress on the Date From value.

    e.g. if Date From = 01/02/2016 and DateTo is empty, then return the following results

    Calendar.Start 28/01/2015, Calendar.Finish 01/02/2016
    Calendar.Start 31/01/2015, Calendar.Finish 02/02/2016
    Calendar.Start 01/02/2016, Calendar.Finish blank
    Calendar.Start 01/02/2016, Calendar.Finish 04/02/2016

    Do not allow queries where Date To is present, but Date From is not.

     If anyone can provide me the solution would really appreciate.

    Many Thanks,

    Shabb

    Wednesday, January 13, 2016 11:31 AM

Answers

  • User-219423983 posted

    Hi shabbir_215,

    Where Date From is present and Date To is not, then return all calendar records which start, finish or are in progress on the Date From value.

    I have created a demo based on gimimex’s code as below and it works on my client machine to get the record when the “Date To” is null. According to the date above, the expected record is

    2, 'b', '20160131', '20160201'

    select * from @Calendar
    where
        ([Start] = @DateFrom or [Finish] = @DateFrom) or
        ([Finish] > @DateFrom and [Start] < @DateFrom)
    

    When both “Date From” and “Date to” are not null, all of the above codes could get the expected result.

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 25, 2016 12:07 PM

All replies

  • User-808054615 posted

    Hi,

    Try:

    select * from [Calendar]
    where
        ([Start] <= @DateTo or @DateTo is null) and
        ([Finish] >= @DateFrom or [Finish] is null)
    

    Hope this help

    Wednesday, January 13, 2016 12:10 PM
  • User-1256377279 posted

    It Display all records, It would be nice if it was something near to combination.

    Wednesday, January 13, 2016 12:26 PM
  • User-808054615 posted

    Try a test with the following script in SQL Server Management Studio:

    declare @Calendar table
    (CalendarID int, CalendarName varchar(40), Start date, Finish date);
    
    insert into @Calendar values
    (1, 'a', '20160101', '20160131'),
    (2, 'b', '20160131', '20160201'),
    (3, 'c', '20160210', '20160220'),
    (4, 'd', '20160215', null),
    (5, 'e', '20160228', '20160301'),
    (6, 'f', '20160301', '20160310');
    
    declare @DateFrom date;
    declare @DateTo date;
    
    set @DateFrom = '20160201';
    set @DateTo = '20160229';
    
    select * from @Calendar
    where
        ([Start] <= @DateTo or @DateTo is null) and
        ([Finish] >= @DateFrom or [Finish] is null)
        
    
    
    set @DateTo = null;
    
    select * from @Calendar
    where
        ([Start] <= @DateTo or @DateTo is null) and
        ([Finish] >= @DateFrom or [Finish] is null)
    

    Can you post some values that should not be in the result?

    Wednesday, January 13, 2016 12:43 PM
  • User-1716253493 posted

    Try like this (correct yourself if my query error)

    WHERE
    
    (@datefrom not is null and  @dateto not is null and ((start between @datefrom AND @dateto) OR (finist between @datefrom AND @dateto))
    
    OR
    
    (@datefrom not is null and  @dateto is null and (@datefrom between start AND finist))

    Thursday, January 14, 2016 2:01 AM
  • User-1256377279 posted

    Hi Oned

    I have correct your query but the result is not correct when finish date is null

    Thanks,

    Shabbir

    Thursday, January 14, 2016 10:27 AM
  • User-1256377279 posted

    Hi Gimi,

    I am still checking the result and update whether query is right or wrong.

    Regards,

    Shabbir

    Thursday, January 14, 2016 10:28 AM
  • User-219423983 posted

    Hi shabbir_215,

    Where Date From is present and Date To is not, then return all calendar records which start, finish or are in progress on the Date From value.

    I have created a demo based on gimimex’s code as below and it works on my client machine to get the record when the “Date To” is null. According to the date above, the expected record is

    2, 'b', '20160131', '20160201'

    select * from @Calendar
    where
        ([Start] = @DateFrom or [Finish] = @DateFrom) or
        ([Finish] > @DateFrom and [Start] < @DateFrom)
    

    When both “Date From” and “Date to” are not null, all of the above codes could get the expected result.

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 25, 2016 12:07 PM