?
Solved

SQL - Stored Procedure Help - MS SQL SERVER

Posted on 2007-03-26
9
Medium Priority
?
184 Views
Last Modified: 2010-03-19
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









0
Comment
Question by:kanden
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 10

Expert Comment

by:ksaul
ID: 18795014

SELECT Name, MAX(ItemRating)
FROM Items
WHERE ItemRating <= @ItemRating
GROUP BY Name
0
 
LVL 10

Expert Comment

by:ksaul
ID: 18795052
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18795129
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:kanden
ID: 18795311
ksaul,

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

Thanks
0
 
LVL 10

Expert Comment

by:ksaul
ID: 18795512
This will give you one row per DisplayCategory

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

Author Comment

by:kanden
ID: 18795821
How do I get the name with it, without getting multiple rows?
0
 
LVL 10

Accepted Solution

by:
ksaul earned 1500 total points
ID: 18796349
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
 

Author Comment

by:kanden
ID: 18796389
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
 
LVL 10

Expert Comment

by:ksaul
ID: 18797568
Looks good
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question