none
SQL query with optional where condition RRS feed

  • Question

  • I have to get data for 4 sets of period.

    period 1 start date - period 1 end date

    period 2 start date - period 2 end date

    period 3 start date - period 3 end date

    period 4 start date - period 4 end date

    Only 1st period is compulsory. Rest 3 are optional.

    Whats the effective way to write a query?

    I want something,

    select * from table where startdate >= period 1 start date and enddate <= period 1 end date

    union all 

    select * from table where startdate >= period 2 start date and enddate <= period 2 end date

    union all

    select * from table where startdate >= period 3 start date and enddate <= period 3 end date

    union all

    select * from table where startdate >= period 4 start date and enddate <= period 4 end date

    each period will fetch around say 2 to 3 thousand rows. I have query with 4 to 5 left outer joins and inner joins.

    I guess union all will take quite a long time.

    ------------------

    If I give condition like,

    If (period 2 start date != '' and period 2 start date IS NOT NULL)

    BEGIN

    select * from table where startdate >= period 2 start date and enddate <= period 2 end date

    END

    Repeat this for rest 2 periods. But does not make much diff.

    ----------------

    If I write a stored procedure, What should be the correct logic.


    h2007

    Tuesday, December 12, 2017 2:00 AM

All replies

  • Can't say there are many alternatives, you have four date ranges, there's no simple way of selecting four date ranges other than going through each.

    Tuesday, December 12, 2017 2:28 AM
  • Hi h2007,

    To achieve this logic, you may consider using dynamic SQL to get expected results. For example,

    declare @v_period1_start_date date
    declare @v_period1_end_date date
    
    declare @v_period2_start_date date
    declare @v_period2_end_date date
    
    declare @v_query_sql varchar(max)
    
    set @v_query_sql='select * from table where (startdate >='''+ convert(varchar(10),@v_period1_start_date,110)+''' and enddate <='''+convert(varchar(10),@v_period1_end_date,110)+''')'
    
    If (@v_period2_start_date is not null and @v_period2_end_date IS NOT NULL)
    begin
    set @v_query_sql=@v_query_sql+' OR (startdate >='''+convert(varchar(10),@v_period2_start_date,110)+''' AND enddate <='''+convert(varchar(10),@v_period2_end_date,110)+''')'
    end
    
    EXEC(@v_query_sql)

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Junaid_Hassan Tuesday, December 12, 2017 1:31 PM
    Tuesday, December 12, 2017 5:36 AM
    Moderator
  • Only way to get this kind of result is Dynamic Query

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you.
    Junaid Hassan.

    Tuesday, December 12, 2017 1:32 PM