Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-11-2012
09:29 AM

04-11-2012
09:29 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-11-2012
11:11 PM

04-11-2012
11:11 PM

Re: Sum column values based on date range

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-16-2012
06:20 AM

04-16-2012
06:20 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-16-2012
07:33 AM

04-16-2012
07:33 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-16-2012
09:54 AM

04-16-2012
09:54 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-16-2012
10:25 AM

04-16-2012
10:25 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-17-2012
10:24 AM

04-17-2012
10:24 AM

Re: Sum column values based on date range

Thanks, Dieter. That seems to work.