Database
Enthusiast

## Sum column values based on date range

I have a table (product) that has week01 through week30.  I have the user enter a start date and another table (promotions) tells the system how many weeks to add to find out the ending date for a promotion.

What I am trying to do is display a total the number of units for each product to order based on the starting week/date that the user entered through the ending week/date.  I am trying to use code like below to accomplish that.

`SELECT Product, Sum(Case when p2.startweek <= 1 and 1<= p2.endweek then week01   when p2.startweek <= 2 and 2<= p2.endweek then week01   when p2.startweek <= 3 and 3<= p2.endweek then week01   when p2.startweek <= 4 and 4<= p2.endweek then week01   when p2.startweek <= 5 and 5<= p2.endweek then week01   when p2.startweek <= 6 and 6<= p2.endweek then week01   when p2.startweek <= 7 and 7<= p2.endweek then week01   when p2.startweek <= 8 and 8<= p2.endweek then week01   when p2.startweek <= 9 and 9<= p2.endweek then week01   when p2.startweek <= 10 and 10<= p2.endweek then week01   when p2.startweek <= 11 and 11<= p2.endweek then week01   when p2.startweek <= 12 and 12<= p2.endweek then week01   when p2.startweek <= 13 and 13<= p2.endweek then week01..... --(through all 30 weeks)else 0 end) as UnitsFROM product p1, promotions p2`

The results I am getting are if the user entered week 3 and the promotions table tells the system to add 4 weeks, I am only getting back the number of units for week03, rather than the sum of week03 through week07.

Any suggestions would be greatly appreciated.  Thanks.
6 REPLIES 6
Enthusiast

## Re: Sum column values based on date range

The Week01 is repeating for all the cases after "then"

Highlighted
Enthusiast

## Re: Sum column values based on date range

Sorry about that.  A typo on my part when I cut and paste.  I was trying to give an example without actually giving the code which uses a lot of alias.  Here is a corrected version of the code.

`SELECT Product, Sum(Case when p2.startweek <= 1 and 1<= p2.endweek then week01   when p2.startweek <= 2 and 2<= p2.endweek then week02   when p2.startweek <= 3 and 3<= p2.endweek then week03   when p2.startweek <= 4 and 4<= p2.endweek then week04   when p2.startweek <= 5 and 5<= p2.endweek then week05   when p2.startweek <= 6 and 6<= p2.endweek then week06   when p2.startweek <= 7 and 7<= p2.endweek then week07   when p2.startweek <= 8 and 8<= p2.endweek then week08   when p2.startweek <= 9 and 9<= p2.endweek then week09   when p2.startweek <= 10 and 10<= p2.endweek then week10   when p2.startweek <= 11 and 11<= p2.endweek then week11   when p2.startweek <= 12 and 12<= p2.endweek then week12   when p2.startweek <= 13 and 13<= p2.endweek then week13..... --(through all 30 weeks)else 0 end) as UnitsFROM product p1, promotions p2`

Enthusiast

## Re: Sum column values based on date range

Can you share all the columns in both the tables product p1, promotions p2.

If possible sample data.

Enthusiast

## Re: Sum column values based on date range

Here is what I can provide:

create table product (

product, integer not null,

description varchar(30)

colorid,,integer,

sizeid,,integer,

brandname,varchar(30),

year_introduced,,smallint

)

unique primary index(productnumber)

create table promotions (

product,,integer  not null,

vendornumber,integer, not null,

week01 DECIMAL(18,3) COMPRESS 0.000 ,

week02 DECIMAL(18,3) COMPRESS 0.000 ,

week03 DECIMAL(18,3) COMPRESS 0.000 ,

week04 DECIMAL(18,3) COMPRESS 0.000 ,

week05 DECIMAL(18,3) COMPRESS 0.000 ,

week06 DECIMAL(18,3) COMPRESS 0.000 ,

week07 DECIMAL(18,3) COMPRESS 0.000 ,

week08 DECIMAL(18,3) COMPRESS 0.000 ,

week09 DECIMAL(18,3) COMPRESS 0.000 ,

week10 DECIMAL(18,3) COMPRESS 0.000 ,

week11 DECIMAL(18,3) COMPRESS 0.000 ,

week12 DECIMAL(18,3) COMPRESS 0.000 ,

week13 DECIMAL(18,3) COMPRESS 0.000 ,

week14 DECIMAL(18,3) COMPRESS 0.000 ,

week15 DECIMAL(18,3) COMPRESS 0.000 ,

week16 DECIMAL(18,3) COMPRESS 0.000 ,

week17 DECIMAL(18,3) COMPRESS 0.000 ,

week18 DECIMAL(18,3) COMPRESS 0.000 ,

week19 DECIMAL(18,3) COMPRESS 0.000 ,

week20 DECIMAL(18,3) COMPRESS 0.000 ,

week21 DECIMAL(18,3) COMPRESS 0.000 ,

week22 DECIMAL(18,3) COMPRESS 0.000 ,

week23 DECIMAL(18,3) COMPRESS 0.000 ,

week24 DECIMAL(18,3) COMPRESS 0.000 ,

week25 DECIMAL(18,3) COMPRESS 0.000 ,

week26 DECIMAL(18,3) COMPRESS 0.000 ,

week27 DECIMAL(18,3) COMPRESS 0.000 ,

week28 DECIMAL(18,3) COMPRESS 0.000 ,

week29 DECIMAL(18,3) COMPRESS 0.000 ,

week30 DECIMAL(18,3) COMPRESS 0.000

)

primary index (productnumber)

sample data:

product table:

115234,'button front shirt',1209,308,'thomas brown',1980

115267,'polo shirt',1209,308,'thomas brown',1983

115252,'mock shirt',1209,308,'thomas brown',1991

116410,'cardigan sweater',1209,308,'thomas brown',1994

116258,'button up sweater',1209,308,'thomas brown',1986

promotion table:

115234,2351,0.007,0.007,0.007,0.005,0.027,0.013,0.007,0.007,0.007,0.006,0.006,0.007,0.006,0.006,0.028,0.028,0.028,0.028,0.028,0.028,0.028,0.028,0.028,0.028,0.028,0.028,0.028,0.028

115267,2351,0.712,0.712,0.712,0.712,0.712,0.712,0.712,0.712,0.712,0.712,0.712,0.712,0.712,0.712,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000

116410,2351,0.002,0.002,0.002,0.002,0.002,0.002,0.002,0.002,0.002,0.002,0.002,0.002,0.002,0.002,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000

116458,2351,0.354,0.374,0.380,0.377,0.387,0.384,0.377,0.391,0.340,0.306,0.296,0.296,0.296,0.303,2.083,2.083,2.083,2.083,2.083,2.083,2.083,2.083,2.083,2.083,2.083,2.083,2.083,2.083

115252,2351,0.062,0.062,0.062,0.062,0.062,0.062,0.062,0.062,0.062,0.062,0.062,0.062,0.062,0.062,0.194,0.196,0.180,0.153,0.157,0.175,0.194,0.238,0.182,0.187,0.187,0.189,0.162,0.160

## Re: Sum column values based on date range

I think this is what you want:

`Sum(Case when p2.startweek <= 1 and 1<= p2.endweek then week01 else 0 end   + case when p2.startweek <= 2 and 2<= p2.endweek then week02 else 0 end   + case when p2.startweek <= 3 and 3<= p2.endweek then week03 else 0 end..... --(through all 30 weeks)else 0 end) as Units`

CASE stops when the first WHEN is true.

Dieter

Enthusiast

## Re: Sum column values based on date range

Thanks, Dieter.  That seems to work.