To CURSOR or not to CURSOR - SQL Server Forums

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

Transact-SQL (2000)

To CURSOR or not to CURSOR

Author

Topic

BCrowe
Starting Member

23 Posts

Posted - 2004-09-01 : 11:01:20

I have the following situation:

Source Table:

PriceSheet (
PriceSheetCode,
ApplicationCode,
StrengthCode,
TypeCode,
ColorCode,
Price )
This table has a compound primary key consisting of everything but Price (and the ModDate which i didn't include)... don't look at me I didn't design it.

I need to present the data in a report in the following form:

Field1: ProductType (determined by ColorCode, StrengthCode, TypeCode)
I'll have to assign a readable name and group these
Field2: Price1 (value in Price field when ApplicationCode = "HC")
Field3: Price2 (value in Price field when ApplicationCode = "SS")
Field4: Price3 (value in Price field when ApplicationCode = "ASL")
Field5: Price4 (value in Price field when ApplicationCode = "TSL")
...
There actually 4 additional price fields that come another table similar to the one described above but this should be enough to describe my predicament.

Right now I am looking at using a CURSOR to cycle through the PriceSheet table and use a LARGE case statement to sort the price into the correct slot along with the associated product fields. Below is the temporary table I have tentatively designed if that helps.

My question is: What is the most efficient and maintainable way to do this?

CREATE TABLE #PriceSheet (
PriceGroup varchar(4), -- A, AA, AAA, or LIST
ProdGrpIndex int, -- Ordering Index for Product Groups
ProdGrpName varchar(100), -- Product Group Name
ProdTypeIndex int, -- Ordering Index for Product Types within a group
ProdTypeName varchar(100), -- Product Type Name
ColorCode varchar(4), -- CLR, BRNZ, EADV, STFR, AZRA, etc.
StrengthCode int, -- in thirty-seconds of an inch
TypeCode char(2), -- CL, TN, LE, UC, RF, etc.
HeavyCasePrc decimal(8,2), -- Heavy Case price
HeavyCasePrcX bit, -- Heavy Case price - Is Exception (1 = true)
StockSheetPrc decimal(8,2), -- Single Sheet price
StockSheetPrcX bit, -- Single Sheet price - Is Exception (1 = true)
CutAnnlPrc decimal(8,2), -- Cut sheet annealed price
CutAnnlPrcX bit, -- Cut sheet annealed price - Is Exception (1 = true)
CutTempPrc decimal(8,2), -- Cut sheet tempered price
CutTempPrcX bit, -- Cut sheet tempered price - Is Exception (1 = true)
IGAnnlPrc decimal(8,2), -- Annealed IG price
IGAnnlPrcX bit, -- Annealed IG price - Is Exception (1 = true)
IGTempPrc decimal(8,2), -- Tempered IG price
IGTempPrcX bit, -- Tempered IG price - Is Exception (1 = true)
IGLowEAnnlPrc decimal(8,2), -- Low-E Annealed IG price
IGLowEAnnlPrcX bit, -- Low-E Annealed IG price - Is Exception (1 = true)
IGLowETempPrc decimal(8,2), -- Low-E Tempered IG price
IGLowETempPrcX bit -- Low-E Tempered IG price - Is Exception (1 = true)
)

BCrowe
Starting Member

23 Posts

Posted - 2004-09-01 : 17:23:58

No one can help me out with this?

BCrowe

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-01 : 17:29:55

I looked at it long enough to subscribe to it and remind myself to visit it later if no one else jumped in. It would help if you showed some sample input data and the desired output. It can probably be done without a cursor. I know your #PriceSheet table is close to a desired output spec but make it simple for us.

CREATE TABLE PriceSheet (PriceSheetCode Char(2), ...)
INSERT PriceSheet SELECT 'CL',....
INSERT PriceSheet SELECT 'TL',....

Desired output;
Col1 Col2 Col3
--- ---- ----...

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

BCrowe
Starting Member

23 Posts

Posted - 2004-09-01 : 17:57:44

Sample Data:

PriceSheetCode___ApplCode___StrengthCode__TypeCode__ColorCode___Price
A_______________HC________3_____________CL________CLR________0.32
A_______________SS________3_____________CL________CLR________0.45
A_______________ASL_______3_____________CL________CLR________0.77
A_______________HC________4_____________CL________CLR________0.44
A_______________SS________4_____________CL________CLR________0.60
A_______________ASL_______4_____________CL________CLR________1.00
A_______________TSL_______4_____________CL________CLR________2.09
A_______________HC________3_____________TN________BRNZ_______0.70
A_______________SS________3_____________TN________BRNZ_______0.91
A_______________ASL_______3_____________TN________BRNZ_______1.24
A_______________HC________4_____________TN________SLXA_______0.77
A_______________SS________4_____________TN________SLXA_______0.90

I need to get it in the form:

PriceSheetCode___ProductType__HCPrice__SSPrice__ASLPrice__TSLPrice
A_______________3/32 Clear___0.32_____0.45_____0.77______0.00
A_______________1/8 Clear____0.44_____0.60_____1.00______2.09
A_______________3/32 Bronze__0.70_____0.91_____1.24______0.00
A_______________1/8 Solexia__0.77_____0.90_____0.00______0.00

The Product Type is a combination of the Type_Code, Strength_Code, and Color_Code fields for while I have created a separate lookup table. The price columns are determined by the Appl_Code field.

Does this explain it better?

Brian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-01 : 19:10:03

Please post all tables involved. Please use CREATE TABLE statements as we need to pull the code onto our machines. When posting data, please post in the form of INSERT INTO statements. We can then try it out and come up with a solution.

Tara

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-01 : 19:57:11

this gets you most of the way there

select pricesheetcode, producttype, sum(HCPrice) as HCPrice, sum(SsPrice) as SSPrice, sum(ASLPrice) as ASLPrice, Sum(TSLPrice) as TSLPrice
from
(
select pricesheetcode, (cast(strengthcode as varchar) + typecode + colorcode) as productType, Price as HCPrice, null as SSPrice, null as ASLPrice, null as TSLPrice
from pricesheet where applcode = 'HC'
union
select pricesheetcode, (cast(strengthcode as varchar) + typecode + colorcode) as productType, null as HCPrice, Price as SSPrice, null as ASLPrice, null as TSLPrice
from pricesheet where applcode = 'SS'
union
select pricesheetcode, (cast(strengthcode as varchar) + typecode + colorcode) as productType, null as HCPrice, null as SSPrice, Price as ASLPrice, null as TSLPrice
from pricesheet where applcode = 'ASL'
union
select pricesheetcode, (cast(strengthcode as varchar) + typecode + colorcode) as productType, null as HCPrice, null as SSPrice, null as ASLPrice, Price as TSLPrice
from pricesheet where applcode = 'TSL'
) a
group by pricesheetcode, producttype
order by pricesheetcode, producttype

Obviously you'll need to use your lookup table for the product type (I've just concatenated them). Also note that I've assumed that you don't have duplicates of applcode and the primary key. Finally, if you want the resulting nulls showing as 0 (different meaning to me but hey) then you'll need to indclude the isnull() around the final sums.

Any problems let me know. Unless your table is small, this should still be way quicker than using a cursor.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-02 : 10:06:06

this is just a regular cross tab problem, and the easiest kind because the columns are predefined and won't change.


SELECT pricesheetcode, producttype, 
  SUM(CASE WHEN applcode='HC' THEN Price ELSE 0 END) as HCPrice,
  SUM(CASE WHEN applcode='SS' THEN Price ELSE 0 END) as SSPrice,
  SUM(CASE WHEN applcode='ASL' THEN Price ELSE 0 END) as ASLPrice,
  SUM(CASE WHEN applcode='TSL' THEN Price ELSE 0 END) as TSLPrice
FROM 
  ...
GROUP BY 
  Pricesheetcode, ProductType

- Jeff

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-02 : 22:29:22

hey that's nice - I've always done it the long way... - learn something new (as they say) every day

when you're constructing a cross tab selection
be sure to ask jeff for correction
just when you feel weary
he'll fix up your query
and rekindle your SQL affection

(He's really not all that cross - he's just a naughty little boy!)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-02 : 22:42:12

LOL -- good to have you back, SQLTeam Poet Laureate!

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources