SQL - Stored Procedure Help - MS SQL SERVER

I am using MS SQL Server.

I have a single table [Items] the fields are as follows:

ItemId
ItemRating
Name
DisplayCategory


I need a stored procedure that pulls all items that have an [ItemRating] of 3 (this is really a parameter @ItemRating) or if they don't have an [ItemRating] of 3 I need to see if they have an [ItemRating] of 2 IF and only if they have the same [DisplayCategory]. And-so-on 2 to 1 and 1 to 0(ONLY IF THEY HAVE THE SAME [DisplayCategory])

So in short it would select the highest [ItemRating] per [DisplayCategory] that was not greater than the @ItemRating parameter.

Thanks









kandenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ksaulCommented:

SELECT Name, MAX(ItemRating)
FROM Items
WHERE ItemRating <= @ItemRating
GROUP BY Name
0
ksaulCommented:
Wait... you want it by DisplayCategory right:

SELECT DisplayCategory, MAX(ItemRating)
FROM Items
WHERE ItemRating <= @ItemRating
GROUP BY DisplayCategory

Or you can group by Name and DisplayCategory:

SELECT Name, DisplayCategory, MAX(ItemRating)
FROM Items
WHERE ItemRating <= @ItemRating
GROUP BY Name, DisplayCategory

0
LowfatspreadCommented:
sorry i don't know if your question has been answered or not...

please give an example...

do you want all items returned for the item rating?
what is the display category supposed to be the same as?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

kandenAuthor Commented:
ksaul,

I only want it to return a single row for each [DisplayCategory], the sql above returns multiple rows for each [DisplayCategory]

Thanks
0
ksaulCommented:
This will give you one row per DisplayCategory

SELECT DisplayCategory, MAX(ItemRating)
FROM Items
WHERE ItemRating <= @ItemRating
GROUP BY DisplayCategory
0
kandenAuthor Commented:
How do I get the name with it, without getting multiple rows?
0
ksaulCommented:
I'm not sure.  Can you have different names for the same Rating?

Try:
SELECT i.DisplayCategory, i.Name, i.ItemRating
FROM Items i
INNER JOIN
  (SELECT DisplayCategory, MAX(ItemRating) MaxRating
  FROM Items
  WHERE ItemRating <= @ItemRating
  GROUP BY DisplayCategory) g
  ON i.DisplayCategory = g.DisplayCategory AND i.ItemRating = g.MaxRating

A problem is if you can have
ItemId ItemRating Name DisplayCategory
1        3               'a'       'cat1'
1        3               'b'       'cat1'

The join to the subquery will get the rating levels you want, but you'll still get two rows and then you would have to use another aggregate query to decide which name to display (a or b).  If you want b:

SELECT i.DisplayCategory,MAX( i.Name), i.ItemRating
FROM Items i
INNER JOIN
  (SELECT DisplayCategory, MAX(ItemRating) MaxRating
  FROM Items
  WHERE ItemRating <= @ItemRating
  GROUP BY DisplayCategory) g
  ON i.DisplayCategory = g.DisplayCategory AND i.ItemRating = g.MaxRating
GROUP BY i.DisplayCategory, i.ItemRating
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kandenAuthor Commented:
Thanks you got me close. Here is the actual SProc I am using; do you see anything I am doing Wrong. Thanks for the help:

CREATE PROCEDURE dbo.ListItemsByItemRatingPlus
(@HomeID as Int, @ItemRating as Int)
 AS

Declare @Temp1 table (
ItemID Int NULL,
ItemRating Int NULL,
DisplayCategory varChar (255) NULL,
RoomCategory varChar (255) NULL
)

Declare @Temp2 table (
HomeID Int NULL,
ModelNumber varChar (255) NULL,
Bedrooms varChar (255) NULL,
Baths varChar (255) NULL,
SqFt varChar (255) NULL,
ComponentQuantity Int NULL,
ItemID Int NULL,
Description varChar (255) NULL,
ItemRating Int NULL,
MSRP float NULL,
THDSku varChar (255) NULL,
ShrtModelNumber varChar (255) NULL,
RoomCategory varChar (255) NULL,
DisplayCategory varChar (255) NULL
)

INSERT  @Temp1(ItemID, ItemRating, DisplayCategory, RoomCategory)

SELECT     MAX(ItemMaster.ItemID) AS ItemID, MAX(ItemMaster.ItemRating) AS ItemRating, ItemMaster.DisplayCategory, BOMComponents.RoomCategory
FROM         HomeMaster INNER JOIN
                      BillOfMaterials ON HomeMaster.HomeID = BillOfMaterials.HomeID INNER JOIN
                      BOMComponents ON BillOfMaterials.BillSequenceID = BOMComponents.BillSequenceID INNER JOIN
                      ItemMaster ON BOMComponents.ItemID = ItemMaster.ItemID
WHERE     (HomeMaster.Active = 1) AND (HomeMaster.HomeID = @HomeID) AND (ItemMaster.Quotable = 1) AND (ItemMaster.ItemRating <= @ItemRating)
GROUP BY ItemMaster.DisplayCategory, BOMComponents.RoomCategory

INSERT @Temp2(HomeID, ModelNumber, Bedrooms, Baths, SqFt, ComponentQuantity, ItemID, Description, ItemRating, MSRP, THDSku, ShrtModelNumber, RoomCategory, DisplayCategory)

SELECT  HomeMaster.HomeID, HomeMaster.ModelNumber, HomeMaster.Bedrooms, HomeMaster.Baths, HomeMaster.SqFt, ComponentQuantity,
                      ItemMaster.ItemID, ItemMaster.Description, ItemMaster.ItemRating, PriceListings.MSRP, ItemMaster.THDSku, ShrtModelNumber, RoomCategory, DisplayCategory
FROM         HomeMaster INNER JOIN
                      BillOfMaterials ON HomeMaster.HomeID = BillOfMaterials.HomeID INNER JOIN
                      BOMComponents ON BillOfMaterials.BillSequenceID = BOMComponents.BillSequenceID INNER JOIN
                      ItemMaster ON BOMComponents.ItemID = ItemMaster.ItemID INNER JOIN
                      PriceListings ON ItemMaster.ItemID = PriceListings.ItemID
WHERE    ( ItemMaster.ItemID IN(SELECT ItemID FROM @Temp1)) AND (HomeMaster.HomeID = 1)

SELECT *
FROM @Temp2
ORDER BY RoomCategory ASC, DisplayCategory ASC
GO
0
ksaulCommented:
Looks good
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.