Substract new row from old row in same column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Substract new row from old row in same column?

Author

Topic

Norden1980
Starting Member

3 Posts

Posted - 2008-03-12 : 08:54:17

Hi

I'm having a bit troubble creating a SQL-sentence which substract the newest row from the second newest row in the same column. The table looks like this:

Pricecalcid Date Price Itemid

2000 2006-12-12 3000 100

2488 2007-10-11 2800 100

3100 2008-08-07 2500 100

What I need is that the largest "Pricecalcid" that is 3100 equals "Price" 2500 and the second largest "Pricecalcid" eqauls 2800 results in a pricecalculation that substracts 2500 from 2800 for "Itemid" 100.

How do I do that?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 08:56:42

Using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

Norden1980
Starting Member

3 Posts

Posted - 2008-03-12 : 09:04:04

SQL Server 2000...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 09:16:47

[code]DECLARE @Sample TABLE (PriceCalcID INT, Date DATETIME, Price MONEY, ItemID INT)

SET DATEFORMAT YMD

INSERT @Sample
SELECT 2000, '2006-12-12', 3000, 100 UNION ALL
SELECT 2488, '2007-10-11', 2800, 100 UNION ALL
SELECT 3100, '2008-08-07', 2500, 100 UNION ALL
SELECT 3000, '2006-12-12', 1000, 200 UNION ALL
SELECT 3488, '2007-10-11', 1800, 200 UNION ALL
SELECT 4100, '2008-08-07', 1500, 200

SELECT s1.ItemID,
s2.Price - s1.Price AS Diff
FROM (
SELECT t1.PriceCalcID,
(SELECT MAX(t2.PriceCalcID) FROM @Sample AS t2 WHERE t2.ItemID = t1.ItemID AND t2.PriceCalcID < t1.PriceCalcID) AS PcID
FROM (
SELECT ItemID,
MAX(PriceCalcID) AS PriceCalcID
FROM @Sample
GROUP BY ItemID
) AS t1
) AS c
INNER JOIN @Sample AS s1 ON s1.PriceCalcID = c.PriceCalcID
INNER JOIN @Sample AS s2 ON s2.PriceCalcID = c.PcID[/code]

E 12°55'05.25"
N 56°04'39.16"

Norden1980
Starting Member

3 Posts

Posted - 2008-03-12 : 10:07:00

First of all thank you for the code above.

Perhaps I didn’t explaned it good enough at first but the information is stored in a table called “bomcalctable” and the output i am looking for is:

Pricecalcid Date Price Itemid Difference

2000 2006-12-12 3000 100 …

2488 2007-10-11 2800 100 -200

3100 2008-08-07 2500 100 -300

Any suggestions?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 10:18:13

This is not what you wrote twice in the original post.

-- Prepare sample data. Not part of solution, only to mimic your environment.
DECLARE	@Sample TABLE (PriceCalcID INT, Date DATETIME, Price MONEY, ItemID INT)

SET DATEFORMAT YMD

INSERT	@Sample
SELECT	2000, '2006-12-12', 3000, 100 UNION ALL
SELECT	2488, '2007-10-11', 2800, 100 UNION ALL
SELECT	3100, '2008-08-07', 2500, 100 UNION ALL
SELECT	3000, '2006-12-12', 1000, 200 UNION ALL
SELECT	3488, '2007-10-11', 1800, 200 UNION ALL
SELECT	4100, '2008-08-07', 1500, 200

-- Solution starts here with stage source data
DECLARE	@Stage TABLE (RowID INT IDENTITY(1, 1), PriceCalcID INT, ItemID INT)

INSERT		@Stage
SELECT		PriceCalcID,
		ItemID
FROM		@Sample
ORDER BY	ItemID,
		Date

-- And here is the final query
SELECT		sa1.PriceCalcID,
		sa1.Date,
		sa1.Price,
		sa1.ItemID,
		sa1.Price - sa2.Price AS Diff
FROM		@Stage AS st1
LEFT JOIN	@Stage AS st2 ON st2.ItemID = st1.ItemID
			AND st2.RowID + 1 = st1.RowID
LEFT JOIN	@Sample AS sa1 ON sa1.PriceCalcID = st1.PriceCalcID
			AND sa1.ItemID = st1.ItemID
LEFT JOIN	@Sample AS sa2 ON sa2.PriceCalcID = st2.PriceCalcID
			AND sa2.ItemID = st2.ItemID
ORDER BY	st1.RowID

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources