topic Re: Date range select option in Application Development and Automation Discussions

Date range select option

former_member693960 — Thu, 13 Aug 2020 18:53:23 GMT

Hello

I am stuck in a weird scenario where I need to find out all the users who are on holiday for those date entered.

I have a select option eg :- issue date ( date field ) .

I need to fetch all the records from a table where there are 2 fields i.e start date and end date . ( 2 different fields )

Below table for reference.

User || start date. || enddate

A. || 10.08.2020. || 15.08.2020

B. ||. 06.08.2020. ||. 08.08.2020

C. || 18.08.2020. || 20.08.2020

D. ||. 14.08.2020 ||. 17.08.2020

If user enters issue date = 13.08.2020 - 16.08.2020

Then user A and D should be shown.

Re: Date range select option

Sandra_Rossi — Thu, 13 Aug 2020 19:44:05 GMT

A user should match the selection dates (SEL) or not as shown in this diagram:

----------------------------------------> TIME

        |---------|
            SEL

User is selected if his/her dates are like this:
|------------|
     |---------------|
             |------------|

User is not selected if his/her dates are like this:
|---|                   |--------------|

Let's say the selection dates are named SEL_START and SEL_END, and the column names are STARTDATE and ENDDATE.

So, the user is selected if STARTDATE <= SEL_END AND ENDDATE >= SEL_START

NB: your table better formatted:

User  start date  end date
----  ----------  ----------
A     10.08.2020  15.08.2020
B     06.08.2020  08.08.2020
C     18.08.2020  20.08.2020
D     14.08.2020  17.08.2020

Re: Date range select option

BiberM — Thu, 13 Aug 2020 20:01:26 GMT

Are you sure that the select option is always exactly one date range to be inluded? A select option does offer a lot more possibilities.

Re: Date range select option

BiberM — Thu, 13 Aug 2020 20:03:40 GMT

Do you have a DB-Table which contains every possible date? You could Filter that with your select option and Join the result the way Sandra showed

Re: Date range select option

former_member693960 — Thu, 13 Aug 2020 20:56:49 GMT

So If user enters in selection screen (1input )

S_date 13.08.2020 - 15.08.2020 ( single select option )

Query =

Select user from dbtable where startdate le s_date-high and end_date ge s_date-low

Thankyou Sandra this might get the job done .

Respectively if I can use s_date-low and high in my select query ,

Will try 2mrw

Re: Date range select option

matt — Fri, 14 Aug 2020 08:06:06 GMT

Exactly. A select-option is often the wrong choice. How would you want the system to react if the user enters:

E BT 01.01.2020 31.01.2020

I EQ 01.02.2020

I GT 28.02.2020

Use two parameters instead. See my blog here: https://blogs.sap.com/2014/02/07/dates-and-select-options/

Re: Date range select option

ponrajasekharan — Fri, 14 Aug 2020 13:22:03 GMT

Hi try this ,

select *
  from Z_TABLE 
  into  fields of table iITAB
  where ( begda <= LOWDATE and endda >= HIGHDAT ) or
        ( begda between LOWDATE and HIGHDAT and
          endda between LOWDATE and HIGHDAT )   or
        ( begda <= LOWDATE and
          endda between LOWDATE and HIGHDAT )   or
        ( begda between LOWDATE and HIGHDAT and
          endda >= HIGHDAT ).