Ranges depending on a parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Ranges depending on a parameter

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-07-06 : 10:36:24

Hello Board
I am having difficulty finding a solution for the below query and wonder if somone would be able to take a second look please:

SELECT left(c.productcode,6) as [Product Code],i.storenum as [Store Number Sold In], c.[Description], c.[section] as [Section Number], k.[description] as [Section Description],i.[ProfitMargin] as [Itemsales Margin],K.[Band 9 Low], K.[Band 9 High]
FROM itemsales_test i INNER JOIN ChoiseProductJCT C ON (cast(i.[ProdCode] as char(6)) = left(C.ProductCode,6)) INNER JOIN KatiesMargins K ON (c.[section] = right('000' + k.[section], 3))
WHERE round(i.[ProfitMargin],2) not between K.[Band 9 Low] and K.[Band 9 High]
The field i.storenum (highlighted in red) can contain several storenumbers that relate to one of the nine band numbers for instance

store number 56,43,67 relates to band 7
storenumber 55,22,47 relates to band 9
storenumber 44,33,21 relates to band 2

etc etc etc.....

at the moment I am assuming that all stores are getting compared against band 9(highlighted in green) but this must obviously be dependent on the specific store number..

Can anyone advise which would be the best way to achieve this please

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-06 : 10:46:50

You can formulate your where clause as shown below:

where 1 = (case when storenum in (56, 43, 67) then
case when round(i.[ProfitMargin],2) not between K.[Band 7 Low] and K.[Band 7 High] then 1 else 0
when storenum in (55,22,47) then
case when round(i.[ProfitMargin],2) not between K.[Band 9 Low] and K.[Band 9 High] then 1 else 0
when storenum in (44,33,21 ) then
case when round(i.[ProfitMargin],2) not between K.[Band 2 Low] and K.[Band 2 High] then 1 else 0)

Harsh Athalye
India.
"Nothing is Impossible"

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-07-06 : 10:47:56

Harsh to the rescue , will give that a try mate thank you muchly....

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-07-06 : 11:14:14

How would I then display the relevant margin in the select clause depending on the one that got used ?
SELECT left(c.productcode,6) as [Product Code],i.storenum as [Store Number Sold In], c.[Description], c.[section] as [Section Number], k.[description] as [Section Description],i.[ProfitMargin] as [Itemsales Margin],K.[Band 9 Low], K.[Band 9 High]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-06 : 11:28:13

quote:
Originally posted by Jim77

How would I then display the relevant margin in the select clause depending on the one that got used ?
SELECT left(c.productcode,6) as [Product Code],i.storenum as [Store Number Sold In], c.[Description], c.[section] as [Section Number], k.[description] as [Section Description],i.[ProfitMargin] as [Itemsales Margin],K.[Band 9 Low], K.[Band 9 High]

how about...

select (case when storenum in (56, 43, 67) then K.[Band 7 Low]
when storenum in (55,22,47) then K.[Band 9 Low]
when storenum in (44,33,21) then K.[Band 2 Low]) as [Band Low],
(case when storenum in (56, 43, 67) then K.[Band 7 High]
when storenum in (55,22,47) then K.[Band 9 High]
when storenum in (44,33,21) then K.[Band 2 High]) as [Band High]
....

but if you dynamically want to change column names also (like [Band 7 High] or [Band 2 High]), I'm out of options there.

Harsh Athalye
India.
"Nothing is Impossible"

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-07-06 : 12:44:28

can anyone else add there two cents worth because this does'nt seem to be doing the trick ? thankx.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-07-12 : 08:49:18

Thank you Harsh I worked it out going on your advise ...Thank you very much.

SELECT left(c.productcode,6) as [Product Code],i.storenum as [Store Number Sold In], c.[Description], c.[section] as [Section Number], k.[description] as [Section Description],i.[ProfitMargin] as [Itemsales Margin],case when i.storenum = 113 then K.[Band 1 Low]
when i.storenum in (117,104,183,109,103,186,111,185,110,180,112,108) then K.[Band 2 Low]
when i.storenum in (105,114,118,184) then K.[Band 3 Low]
when i.storenum = 190 then K.[Band 4 Low]
when i.storenum in (182,189,181) then K.[Band 5 Low]
when i.storenum in (210,270)then K.[Band 6 Low]
when i.storenum in (203,202,201,204,278) then K.[Band 7 Low]
when i.storenum in (211,205) then K.[Band 8 Low]
when i.storenum in (271,272,273,274,276,279,208) then K.[Band 9 Low]
when i.storenum in (281,280,206,207) then K.[Band 10 Low]
end as [Product Band Low],
case when i.storenum = 113 then K.[Band 1 High]
when i.storenum in (117,104,183,109,103,186,111,185,110,180,112,108) then K.[Band 2 High]
when i.storenum in (105,114,118,184) then K.[Band 3 High]
when i.storenum = 190 then K.[Band 4 High]
when i.storenum in (182,189,181) then K.[Band 5 High]
when i.storenum in (210,270)then K.[Band 6 High]
when i.storenum in (203,202,201,204,278) then K.[Band 7 High]
when i.storenum in (211,205) then K.[Band 8 High]
when i.storenum in (271,272,273,274,276,279,208) then K.[Band 9 High]
when i.storenum in (281,280,206,207) then K.[Band 10 High]
end as [Product Band High]
FROM itemsales_test i INNER JOIN ChoiseProductJCT C ON (cast(i.[ProdCode] as char(6)) = left(C.ProductCode,6)) INNER JOIN KatiesMargins K ON (c.[section] = right('000' + k.[section], 3))
WHERE 1 = case when storenum = 113 then
(case when round(i.[ProfitMargin],2) not between K.[Band 1 Low] and K.[Band 1 High] then 1 else 0 end)
when storenum in (117,104,183,109,103,186,111,185,110,180,112,108) then
(case when round(i.[ProfitMargin],2) not between K.[Band 2 Low] and K.[Band 2 High] then 1 else 0 end)
when storenum in (105,114,118,184) then
(case when round(i.[ProfitMargin],2) not between K.[Band 3 Low] and K.[Band 3 High] then 1 else 0 end)
when storenum = 190 then
(case when round(i.[ProfitMargin],2) not between K.[Band 4 Low] and K.[Band 4 High] then 1 else 0 end)
when storenum in (182,189,181) then
(case when round(i.[ProfitMargin],2) not between K.[Band 5 Low] and K.[Band 5 High] then 1 else 0 end)
when storenum in (210,270) then
(case when round(i.[ProfitMargin],2) not between K.[Band 6 Low] and K.[Band 6 High] then 1 else 0 end)
when storenum in (203,202,201,204,278) then
(case when round(i.[ProfitMargin],2) not between K.[Band 7 Low] and K.[Band 7 High] then 1 else 0 end)
when storenum in (211,205) then
(case when round(i.[ProfitMargin],2) not between K.[Band 8 Low] and K.[Band 8 High] then 1 else 0 end)
when storenum in (271,272,273,274,276,279,208) then
(case when round(i.[ProfitMargin],2) not between K.[Band 9 Low] and K.[Band 9 High] then 1 else 0 end)
when storenum in (281,280,206,207) then
(case when round(i.[ProfitMargin],2) not between K.[Band 10 Low] and K.[Band 10 High] then 1 else 0 end)
end

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-12 : 10:46:27

Jim - This is horrendous!

You're having to do horrible things (although they don't have to be quite as horrible as this) because your data structure is not normalised.

I recommend that instead of...

create table KatiesMargins (K.[Band 1 Low], K.[Band 2 Low], ... , K.[Band 1 High], K.[Band 2 High], ...)

...you use (more rows, fewer columns)...

create table KatiesMargins (bandId int, low int, high int)

...and that you store what storenums are in what bands in a table...

declare stores table (storenum int, bandId int)

insert stores
          select 113, 1
union all select 117, 2
union all select 104, 2
union all select 183, 2
union all select 109, 2
union all select 103, 2
union all select 186, 2
union all select 111, 2
union all select 185, 2
union all select 110, 2
union all select 180, 2
union all select 112, 2
union all select 108, 2
union all select 105, 3
union all select 114, 3
union all select 118, 3
union all select 184, 3

Then you can just join your tables together, and your query will be shorter, more efficient, easier to understand, easiert to maintain.

And, I recommend you read these...

http://www.datamodel.org/NormalizationRules.html
http://www.agiledata.org/essays/dataModeling101.html
http://en.wikipedia.org/wiki/Database_normalization

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-07-12 : 11:31:34

Cheers Ryan a couple of things though this is a datamart so I am aiming for a denormalised structure and secondly I don't know how a join between the katiesmargin table and your recomended stores table will make this query simpler, I mean you are still going to need to find out the band margin based on which product band the specific product is in ! ...I always thought the less joins you have for a reporting query the better?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-12 : 12:05:14

The case statement is like a join with no index usage. With separate tables, you can create indexes and optimise better.

Try it out!

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources