Is there a better way for this query

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)

Is there a better way for this query

Author

Topic

rythm123us
Starting Member

27 Posts

Posted - 2006-03-23 : 12:10:33

I am going to but the example shown below into an INSTEAD OF INSERT Triger. I am only wonderingif there is a better way of determining my "@Action" then what I am doing.
I want to know if there exists a row such that it has both the MAX(Rank) and Max(Update_Date). I am getting this info through 2 select statements, was wondering if there is a better way.
SAMPLE:

DECLARE @TEST_tbl_Products_Stock TABLE(
[ProductID] [int] NOT NULL ,
[Quantity] [smallint] NOT NULL DEFAULT (0),
[Unit_Cost] [decimal](9, 2) NOT NULL DEFAULT (0),
[Price] [decimal](9, 2) NOT NULL DEFAULT (0),
[Rank] [smallint] NULL DEFAULT (0),
[Insert_Date] [smalldatetime] NOT NULL DEFAULT (GETDATE()),
[Update_Date] [smalldatetime] NOT NULL DEFAULT (GETDATE()));

DECLARE
@ProductID int,
@Quantity int,
@Unit_Cost DECIMAL(9,2),
@Price DECIMAL(9,2),
@Max_Rank int,
@New_rank int,
@Action bit; -- 1:Insert 0:Update
BEGIN

SET @ProductID = 1174 -- 1174, 1175
SET @Quantity = 2
SET @Unit_Cost = .45
SET @Price = .99
-- If it is a new Product
SET @Max_Rank = 1;
SET @New_Rank = 1;
SET @Action = 1;

INSERT @TEST_tbl_Products_Stock
SELECT 1173, 1, 0.45, .99, 2, '2/14/2006 6:41PM', '2/14/2006 6:41PM' UNION ALL
SELECT 1173, 2, 0.50, 1.00, 1, '3/14/2006 6:41PM', '3/14/2006 6:41PM' UNION ALL
SELECT 1173, 1, 0.45, .99, 3, '3/16/2006 6:41PM', '3/16/2006 6:41PM' UNION ALL
SELECT 1174, 5, 0.79, 1.00, 2, '2/14/2006 6:41PM', '2/14/2006 6:41PM' UNION ALL
SELECT 1174, 10, 0.99, 1.39, 1, '3/14/2006 6:41PM', '3/14/2006 6:41PM'

SELECT *
FROM @TEST_tbl_Products_Stock
WHERE ProductID = @ProductID
ORDER BY ProductID, Rank
/*
INSERT:
If there are no rows for the product.
If there are no rows matching the Cost & Price for the MAX(Rank) & Max(Update_Date).
ELSE
UPDATE
*/

SELECT @Max_Rank = Max_Rank.Rank, @New_Rank = Max_Rank.Rank + 1,
@Action =
CASE Max_Rank.Rank
WHEN Max_Date.Rank
THEN CASE
WHEN Max_Rank.Unit_Cost = @Unit_Cost AND Max_Rank.Price = @Price
THEN 0 -- Update
ELSE 1 -- Insert
END
ELSE 1 -- Insert
END
FROM
(
SELECT TOP 1 *
FROM @TEST_tbl_Products_Stock
WHERE ProductID = @ProductID
ORDER BY Rank DESC
) Max_Rank,
(
SELECT TOP 1 *
FROM @TEST_tbl_Products_Stock
WHERE ProductID = @ProductID
ORDER BY Update_Date DESC
) Max_Date

SELECT TOP 1 *
FROM @TEST_tbl_Products_Stock
WHERE ProductID = @ProductID
ORDER BY Rank DESC

SELECT TOP 1 *
FROM @TEST_tbl_Products_Stock
WHERE ProductID = @ProductID
ORDER BY Update_Date DESC

SELECT @Max_Rank AS 'Max Rank', @New_Rank AS 'New Rank', @Action AS Action

IF @Action = 1
BEGIN
INSERT INTO @TEST_tbl_Products_Stock
(ProductID, Quantity, Unit_Cost, Price, Rank)
VALUES
(@ProductID, @Quantity, @Unit_Cost, @Price, @New_Rank)
END
ELSE
BEGIN
UPDATE @TEST_tbl_Products_Stock
SET Quantity = Quantity + @Quantity,
Update_Date = GETDATE()
WHERE Rank = @Max_Rank
END

SELECT *
FROM @TEST_tbl_Products_Stock
WHERE ProductID = @ProductID
ORDER BY ProductID, Rank

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources