kanden
asked on
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
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
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
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
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?
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?
ASKER
ksaul,
I only want it to return a single row for each [DisplayCategory], the sql above returns multiple rows for each [DisplayCategory]
Thanks
I only want it to return a single row for each [DisplayCategory], the sql above returns multiple rows for each [DisplayCategory]
Thanks
This will give you one row per DisplayCategory
SELECT DisplayCategory, MAX(ItemRating)
FROM Items
WHERE ItemRating <= @ItemRating
GROUP BY DisplayCategory
SELECT DisplayCategory, MAX(ItemRating)
FROM Items
WHERE ItemRating <= @ItemRating
GROUP BY DisplayCategory
ASKER
How do I get the name with it, without getting multiple rows?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.ListItemsByItemRatingP
(@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)
FROM HomeMaster INNER JOIN
BillOfMaterials ON HomeMaster.HomeID = BillOfMaterials.HomeID INNER JOIN
BOMComponents ON BillOfMaterials.BillSequen
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
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.BillSequen
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
CREATE PROCEDURE dbo.ListItemsByItemRatingP
(@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)
FROM HomeMaster INNER JOIN
BillOfMaterials ON HomeMaster.HomeID = BillOfMaterials.HomeID INNER JOIN
BOMComponents ON BillOfMaterials.BillSequen
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
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.BillSequen
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
Looks good
SELECT Name, MAX(ItemRating)
FROM Items
WHERE ItemRating <= @ItemRating
GROUP BY Name