topic open SQL help please in Application Development and Automation Discussions

open SQL help please

Former Member — Fri, 09 Mar 2007 22:01:49 GMT

I have a "Date range" as a selection screen criteria where user can enter single date or a range. For this range, I have to look in some table that has begda(begin date) and endda(enddate) and see that the range I entered on selection screen falls within begda and endda. I know it is easy for the single date (see my SQL below) but not sure how it could be done for a range! I heard this makes the logic complicated and any help you could provide would be great. Hope I gave all the details and please let me know if anyone needs more information. thanks in advance.

select kostl syst begda endda from zhrccroll
into table i_staffccs
where syst = 'STAFF'
and begda <= p_date
and endda >= p_date.

Thanks.

--Mithun

Re: open SQL help please

former_member187255 — Fri, 09 Mar 2007 22:09:09 GMT

As i understood that if user enter single data or range it should work....

you can check in this way.....

if p_date-high is not initial.

select kostl syst begda endda from zhrccroll

into table i_staffccs

where syst = 'STAFF'

and begda => p_date-low

and endda <= p_date-high .

else.

select kostl syst begda endda from zhrccroll

into table i_staffccs

where syst = 'STAFF'

and begda <= p_date

and endda >= p_date.

endif.

let me know if there is miscommunication...

Re: open SQL help please

Former Member — Fri, 09 Mar 2007 22:09:14 GMT

Try this:

Declare s_Date as select-option

select kostl syst begda endda from zhrccroll

into table i_staffccs

where syst = 'STAFF'

and begda GE s_date-low

and endda LE s_date-high.

Thanks,

Santosh

Re: open SQL help please

Clemenss — Fri, 09 Mar 2007 22:26:59 GMT

Hi Mithun,

not only it makes the logic complicated, but also it must be defined what the expectzed result will be:

If the user has a select-option for the date, entering of exclusions, ranges , range exclusions and all kinds of comparison operators are allowed.

I think, to do it correct, you have to compare all dates between begda and endda against the range entered and check if any (or all?) dates are IN the selection range.

First it has to be defined along this example

If the users enters march 1st to march 10th.

Does this match with

begda january 1st and endda march 2nd or

begda march 2nd and endda april 1st or

begda january 1st and endda april 1st or

begda march 2nd and endda march 9th

or all of them?

This is a process question.

And how to act if a specific date is excluded?

Or, if exclusion and/or compaisons are not allowed, you must restrict the entry posibilities - because: What is possible will be done.

All is possible but first you must know what you want. This question is not so much open SQL, more business requirement.

Regards,

Clemens