Comparing the value of one row with another row MS SQL

mountainyman

I have a database where I want to compare the data in a field and row

For example
Product, Row4

With the row above

For example
Product,Row5

and then act on it if it is the same.

Does anyone know how to do this in Microsoft SQL 2000 compliant SQL?

Thanks

MM

kayos

You can do it just need more info on your schema to be able to help you.

mountainyman

A simplified Schema goes like this:

CREATE TABLE
#ProductTable
(
Product INT,
ProductName VARCHAR (50),
CatID INT,
Category VARCHAR(50),
Discount VARCHAR(50)
)


Every Category has a single Discount. I want to identify all instances where there is a Category that repeats twice :
So that:
Category in Row4
is the same as
Category in Row 5

And where this is the case I want to
First overwrite the Category value in Row 5 with the Discount Value from Row 5.

I know that this is effectively formatting in SQL (which I also know is bad practise) but it is soemthing I have been asked to do to help someone else genreate reports in Excel.

Many thanks

MM

kayos

Right first off what you are looking to do is fix a problem caused by poor schema design.

Based on your schema what you really need is a Product Table and Category Table

Product
ProductID (PK)
ProductName
CategoryID

Category
CategoryID (PK)
CategoryName
Discount

This would stop you from having multiple values for the same CategoryID. Also why would the discount be stored as a varchar? Surely this is a numeric value i.e. a percentage discount or a flat value discount.

While I can see what you are trying to do its kinda hard to do it right without doing it manually. As it stands you can have multiple discounts per category. i.e. CatID 1 could have discounts of 10%, 20% and 50%. Trying to correct this via a script would mean you blindly update the Discount and unless you can say always use the min or max or average discount value for the category you wont know what rate you are going to up the table with.

Any way based on what you have given something like the below would be something to start from hopefully it will give you a pointer in the right direction. FYI I have changed discount to be a numeric.

CREATE TABLE 
	#ProductTable 
	(
	Product INT, 
	ProductName VARCHAR (50), 
	CatID INT,
	Category VARCHAR(50),
	Discount float
	)

INSERT
	#productTable
SELECT
1,'Prod1',1,'Cat1',0.01
UNION
SELECT
2,'Prod2',1,'Cat1',0.02
UNION
SELECT
3,'Prod3',2,'Cat2',0.02
UNION
SELECT
4,'Prod4',2,'Cat2',0.02


PRINT 'Current Product Table Values'
SELECT * FROM #productTable

UPDATE
	PU 
SET
	PU.Discount = PNewDis.NewDis
FROM
	#ProductTable PU
	JOIN
	(
	SELECT
		PT.CatID,
		MIN(PT.Discount) AS NewDis –Could use max or work out the average whatever best suits the business requirements.
	FROM
		#ProductTable AS PT
		JOIN
			(--This gets the CatID's that have multiple Discount values
			SELECT 
				#ProductTable.CatID,
				#ProductTable.Discount,
				COUNT(#ProductTable.CatID) AS NumDups

			FROM 
				#ProductTable
			GROUP BY 
				#ProductTable.CatID,
				#ProductTable.Discount
			HAVING 
				COUNT(#ProductTable.CatID)  > 1
			) AS DupCats ON DupCats.CatID = PT.CatID
	GROUP BY PT.CatID
	) AS PnewDis ON (PU.CatID = PNewDis.CatID)

PRINT 'Updated Product Table Values'
SELECT * FROM #productTable

DROP TABLE #ProductTable

mountainyman

Thanks Kayos.

The discount is actually a varchar as it is a descriptive discount rather than a percentage amount, sounds odd but it is actually a guideline for discounting rather than the rate.

Many Many Thanks

MM