Sum column values based on date range

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 Units
FROM 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
Enthusiast

Re: Sum column values based on date range

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

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 Units
FROM 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

Junior Contributor

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.