Re: AW: SQL Date Range Problem with Between

I find it easier to work with dates when they are actually dates. I wrote a
little RPG service program, called TRUEDATE, to convert year, month, day
into a date and then created the following SQL function, also called
TRUEDATE, to make it available in SQL.


CREATE FUNCTION TRUEDATE (DEC(4,0), DEC(2,0), DEC(2,0))
RETURNS DATE
EXTERNAL NAME 'QGPL/TRUEDATE(TRUEDATE)'
LANGUAGE RPGLE
NO SQL
NOT DETERMINISTIC
NOT FENCED
RETURNS NULL ON NULL INPUT


Then just use the following:

Select sum(SALES)
From Daysales WHERE CPY = 22 and STR = 1234 AND
truedate(caly,calm,cald) between '2009-07-14' and '2009-07-31'





On Mon, Feb 1, 2010 at 7:24 AM, Vern Hamberg <vhamberg@xxxxxxxxxxx> wrote:

Concatenation WILL work - you have to be missing something in what we
all have said - or you have not told us everything about th problem.

Hey!! ALL of us said to use concatenation - or to use the multiplication
method. We can't be all wrong - we could be misinformed, however.

And Birgitta went to the trouble of giving you a method that can take
advantage of indexes and not use calculations, which result in table scans.

You will have to tell us why it doesn't work - I don't understand the
reference to 1990 - if you have the year, as your example showed, there
is no problem. As long as you concatenate the 3 fields in YYYYMMDD
order, all is good. Nothing in your solution speaks about years, so I
still don't understand that problem. Help me here!

Vern

Chandana Silva wrote:

Thank you all for the quick responses.

Due to the date being created as three fields Year, Month and Day in the

table (pre. 1990 era ), concatenating the date fields does not help.

and it's becoming more complex because of the day values. So I decided to

run the query seperately if the month/Year has changed and that works.It
uses the same open data path.

First I will get the Total for July 14 thru 31; cald Between 14 and 31

and then run again for Aug 1 thru 10; cald between 1 and 14;

Of course, CALM will be between 07 and 07 and 08 and 08, respectively.

I am yet to verify the performance on this large file; I think

Setll/Reade will be better.

Thanks for sharing !

Best Regards !




________________________________
From: Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Sent: Sun, January 31, 2010 3:16:15 AM
Subject: AW: SQL Date Range Problem with Between

Chandana,

you may check if the performance with the concatenation or multiplication
will be sufficient.
(An index may be used if there are also other selection criteria).

If there are no other selection criteria a table scan may be executed

when

concatenating or multiplying. If it is a large file a table scan will

take a

lot of time.
If so you may try something like the following where conditions, which

are

more complex, but the optimizer may use an index built over the year,

month

and date field

where (MyYear = 2009 and MyMonth = 10 and MyDay >= 15)
or (MyYear = 2009 and MyMonth in(11, 12))
or (MyYear = 2010 and MyMonth <= 3 )
or (MyYear = 2010 and MyMonth = 4 and MyDay <= 8)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training

them

and keeping them!"


-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]

Im

Auftrag von Vern Hamberg
Gesendet: Saturday, 30. January 2010 17:40
An: RPG programming on the IBM i / System i
Betreff: Re: SQL Date Range Problem with Between

Hi Chandana

Well, your second one won't work, because BETWEEN is the same as CALD >=
14 and CALD <= 10 - there is nothing that fits that criteria.

It is never reliable or simple to compare multiple values with ranges -
you end up with VERY complex conditions. I mean, you'd require multiple
BETWEENs for the second one - the first would be from 14 to the last day
of the month - which is not the same always, so you have to account for
that plus leap years - well, you see what I mean!!

I think you really need to either convert your numbers to character and
concatenate to a single value. You probably need to use the DIGITS
function if these will come from columns. I assume that CALY is 4,0 and
CALM and CALD are both 2,0 - DIGITS() preserves leading zeroes, which
you must have here.

Select sum(SALES)
From Daysales WHERE CPY = 22 and STR = 1234 AND
digits(CALY) concat digits(calm) concat digits(cald)
BETWEEN '20090714' And '20090810';

Or use multiplication

Select sum(SALES)
From Daysales WHERE CPY = 22 and STR = 1234 AND
CALY * 10000 = calm * 100 + cald BETWEEN 20090714
And 20090810;

Mulitiplication tends to take more cycles than concatenation, I think,
but maybe it doesn't matter these days.

HTH
Vern
Chandana Silva wrote:

Hello,

I am sure someone has hit this bottleneck before with the BETWEEN

operator.

My search online was not successful.

Objective is to get a total for a date range within different

months/years.

This statement works fine, for the same month.

Select sum(SALES)
From Daysales WHERE CPY = 22 and STR = 1234 AND
CALY BETWEEN 2009 And 2009 AND
CALM BETWEEN 07 And 07 AND : This is in July
CALD BETWEEN 14 And 31;

This statement Returns zero if the CALD is less than 14. But I haven't

verified if value is correct even if the CALD > 14.

Select sum(SALES)
From Daysales WHERE CPY = 22 and STR = 1234 AND
CALY BETWEEN 2009 And 2009 AND
CALM BETWEEN 07 And 08 AND : This is from July thr

Aug

10

CALD BETWEEN 14 And 10;

Any help is greatly appreciated.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.