Database

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- 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

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

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

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

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

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

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

Thanks, Dieter. That seems to work.

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.