× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Just so I'm understanding why you are doing this way,

So you are looking to perform a query for a certain set of years, a
certain set of months and a particular date range. Correct?!
If so, the your solution from Birgitta will work. Just know that your
example below, was using (CALY IN (2009, 20010)), then you
will only be returning those records for 2009 and 2010, that fall in month
07 and 08. And that its NOT a full YEAR long scope.

To each it's own on how you structure your query, just be sure that it is
producing what you want. Meaning, have you
tested it with a different year span?

David L. Mosley, Jr.
Technical Solutions Architect
Dancik International, Ltd.
2000 CentreGreen Way, Suite 250
Cary, NC 27513

www.dancik.com



Chandana Silva <chandana.silva@xxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
02/01/2010 02:25 PM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Re: AW: SQL Date Range Problem with Between - Resolved






Vern,

This is my resolution which came Birgitta.

Select sum(SALES)
From DaySales WHERE CPY = 22 and STR = 1234 AND
CALY In (2009, 2009) and CALM in (07,08) AND (CALD >=01 And CALD
<=31) ;// Birgitta
I ran it seperately for the Months for due diligence. :- )
This works and the numbers match.

The Concatenation below works but the results are bad ..
Select sum(SALES)
From DaySales WHERE CPY = 22 and SDSTR = 1234 AND
DIGITS(2009) CONCAT'-' CONCAT DIGITS (07) CONCAT'-' CONCAT
DIGITS(15) BETWEEN '2009-07-14' AND '2009-08-10';
I tried:
Select sum(SALES)
From DaySales WHERE CPY = 22 and SDSTR = 1234 AND
DIGITS(2009) CONCAT DIGITS (07) CONCAT DIGITS(15) BETWEEN
'20090714' AND '20090810';

Off by Ml.s

Multiplication below, Returns Zeo (.)

Select sum(SALES)
From DaySales WHERE SDCPY = 88 and SDSTR = 1234 AND
((2009*10000) + (07*100) + 15) BETWEEN '20090714' AND
'20090810';


So I am working with Birgitta's solution. Thank you Birgitta.

Sorry if I didn't provide a lot of details of the issue. The Table has
been created in 1990's and there is no long date or 8 digit date field.

Thanks Gene for the Create Function and I will defintly use.

Thank you Vern and Eric and all for sharing your thoughts !



________________________________
From: Vern Hamberg <vhamberg@xxxxxxxxxxx>
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Sent: Mon, February 1, 2010 9:30:03 AM
Subject: Re: AW: SQL Date Range Problem with Between

LOL Eric - the OP has already rejected this solution as well as the
concatenation one using DIGITS(). No idea why - the explanation below
doesn't make sense to me. I don't think he actually tried any
suggestions, decided a priori that they would not work. Been that, done
there, have no t-shirt!

Vern

DeLong, Eric wrote:
Whoa! Why would you say concatenating the date elements does not help?
Construct the concatenated fields in ISO format YYYYMMDD, then use in your
between clause.

Understand that determining ranges REQUIRES two endpoints. Your
compound BETWEEN logic does not construct a valid start or end point...

Select sum(SALES)
From Daysales
WHERE CPY = 22 and STR = 1234 AND
DEC(CALY*10000 + CALM*100 + CALD ,8,0) BETWEEN 20090714 And
20090810;

Hth,
-Eric DeLong

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Chandana Silva
Sent: Monday, February 01, 2010 12:36 AM
To: RPG programming on the IBM i / System i
Subject: Re: AW: SQL Date Range Problem with Between

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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.