1 Reply Latest reply on Sep 19, 2018 6:42 AM by Alex Braun

    Date paramters with multiple date values

    Frederik Lundberg

      Hi

       

      I have a dataset with projects that have start and end dates.
      I have tried to make date paramters, so I only get:

       

      Projects that start in the period
      Projects that end in the period
      Projects that start before and end after the period
      Projects that start and end in the period

       

       

       

       

      SQL 1st TRY:

       

      WHERE p.status = 'ongoing'

      AND p.work_period_from < <Parameters.Dato fra> and p.work_period_to > <Parameters.Dato til>

      OR p.work_period_from > <Parameters.Dato fra> and p.work_period_to < <Parameters.Dato til>

      OR p.work_period_from > <Parameters.Dato fra> and p.work_period_from < <Parameters.Dato til>

      OR p.work_period_to > <Parameters.Dato fra> and p.work_period_to < <Parameters.Dato til>

       

       

      SQL 2nd TRY:

       

      WHERE p.status = 'ongoing'

      AND

      (p.work_period_from between <Parameters.Dato fra> and <Parameters.Dato til> or

             p.work_period_to between <Parameters.Dato fra> and <Parameters.Dato til>

            )

      OR

      (p.work_period_from < <Parameters.Dato fra> AND p.work_period_to > <Parameters.Dato til>

      )

      OR

      (p.work_period_from > <Parameters.Dato fra> AND p.work_period_to < <Parameters.Dato til>

      )

       

       

      But it doesn't seem to work for me. I have tried with the two following queries.. Appreciate any help

        • 1. Re: Date paramters with multiple date values
          Alex Braun

          First make sure you wrap your entire second statement in parenthesis

           

          where p.status = 'ongoing' and

          (

               (p.work_period_from between <parameter.start> and <parameter.end>) or

               (p.work_period_to between <parameter.start> and <parameter.end>) or

               (p.work_period_from < <parameter.start> and p.work_period_to > <parameter.end>) or

               (p.work_period_from > <parameter.start> and p.work_period_to < <parameter.end>)

          )