Re: SQL Date Range Problem with Between

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

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.
>