RANK get previous record but not always

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

RANK get previous record but not always

Author

Topic

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-04-16 : 10:15:36

I need to get the previous price for all my PROMOTION records but not when the previous record is a type PROMOTION also it needs to keep going back to get the price.

I have created a table with RANK in which works OK to get previous price for all but how can I say if previous price is type PROMOTION go to next previous prices...

Bets way to show an example is with a jpeg image I have but having trouble inserting into this message...

SELECT a.[StartPrice]
,a.[ProductID]
,a.[Colour]
,ISNULL(b.[Price],a.[Price]) AS [Price Before]
,a.[Price] AS [Promotion Price]

FROM PriceTable a

LEFT OUTER JOIN PriceTable b
ON a.[ProductID] = b.[ProductID]
AND a.[Colour] = b.[Colour]
AND a.[Rank] - 1 = b.[Rank]

WHERE a.[PriceCode] = 'PROMOTION'

Table

PriceCode StartPrice EndPrice ProductID Colour Price Rank
RETAIL 21-Oct-13 24-Dec-13 Bike15 BLUE 39.00 1
PROMOTION 29-Nov-13 01-Dec-13 Bike15 BLUE 31.20 2
PROMOTION 12-Dec-13 23-Dec-13 Bike15 BLUE 31.20 3
MARKDOWN 25-Dec-13 14-Jan-14 Bike15 BLUE 31.20 4
MARKDOWN 15-Jan-14 01-Jan-50 Bike15 BLUE 19.00 5
RETAIL 21-Oct-13 24-Dec-13 Seat22 GREEN 39.00 1
PROMOTION 29-Nov-13 01-Dec-13 Seat22 GREEN 31.20 2
PROMOTION 12-Dec-13 23-Dec-13 Seat22 GREEN 31.20 3
MARKDOWN 25-Dec-13 14-Jan-14 Seat22 GREEN 31.20 4
PROMOTION 15-Jan-14 01-Jan-50 Seat22 GREEN 19.00 5
RETAIL 21-Oct-13 24-Dec-13 Wheel1 BLACK 39.00 1
PROMOTION 29-Nov-13 01-Dec-13 Wheel1 BLACK 31.20 2
RETAIL 12-Dec-13 23-Dec-13 Wheel1 BLACK 31.20 3
MARKDOWN 25-Dec-13 14-Jan-14 Wheel1 BLACK 31.20 4
MARKDOWN 15-Jan-14 01-Jan-50 Wheel1 BLACK 19.00 5

Want I'm trying to do select PriceCode PROMOTION and get previous price: Get previous price
PROMOTION 29-Nov-13 01-Dec-13 Wheel1 BLACK 31.20 39.00

But in this example it picks up the PROMOTION before and I need to ignore this and get rank 1 price for both
PROMOTION 29-Nov-13 01-Dec-13 Bike15 BLUE 31.20 39.00
PROMOTION 12-Dec-13 23-Dec-13 Bike15 BLUE 31.20 39.00

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-04-16 : 10:17:44

If did think about adding AND b.[PriceCode] = 'PROMOTION'
but then the rank won't join up e.g. because it is looking for rank - 1 all the time... stuck..

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-04-16 : 10:18:26

Correction
I did think about adding

AND b.[PriceCode] <> 'PROMOTION'

but then the rank won't join up e.g. because it is looking for rank - 1 all the time... stuck..

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-16 : 12:09:46

1. Any chance you can put your data in a consumable format (create table, insert data)?
2. What version of SQL server are you using?

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-04-16 : 13:09:09

OK Lamprey, Thanks.
I'm using SQL 2005

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-04-16 : 13:09:43

-- Drop the Temp Table
IF (SELECT Object_id('tempdb..#PriceTable')) <> 0
BEGIN
DROP TABLE #PriceTable
END

-- Create Temp tables
CREATE TABLE [dbo].#PriceTable
( [PriceCode] [varchar](10) NULL,
[StartPrice] [datetime] NULL,
[EndPrice] [datetime] NULL,
[ProductID] [varchar](20) NULL,
[Colour] [varchar](10) NULL,
[Price] [decimal](38, 20) NULL,
[Rank] [bigint] NULL
)
;

INSERT INTO #PriceTable
VALUES('RETAIL','21-Oct-13','24-Dec-13','Bike15','BLUE','39.00','1')
;
INSERT INTO #PriceTable
VALUES('PROMOTION','29-Nov-13','01-Dec-13','Bike15','BLUE','31.20','2')
;
INSERT INTO #PriceTable
VALUES('PROMOTION','12-Dec-13','23-Dec-13','Bike15','BLUE','31.20','3')
;
INSERT INTO #PriceTable
VALUES('MARKDOWN','25-Dec-13','14-Jan-14','Bike15','BLUE','31.20','4')
;
INSERT INTO #PriceTable
VALUES('MARKDOWN','15-Jan-14','01-Jan-50','Bike15','BLUE','19.00','5')
;
INSERT INTO #PriceTable
VALUES('RETAIL','21-Oct-13','24-Dec-13','Seat22','GREEN','39.00','1')
;
INSERT INTO #PriceTable
VALUES('PROMOTION','29-Nov-13','01-Dec-13','Seat22','GREEN','31.20','2')
;
INSERT INTO #PriceTable
VALUES('PROMOTION','12-Dec-13','23-Dec-13','Seat22','GREEN','31.20','3')
;
INSERT INTO #PriceTable
VALUES('MARKDOWN','25-Dec-13','14-Jan-14','Seat22','GREEN','31.20','4')
;
INSERT INTO #PriceTable
VALUES('PROMOTION','15-Jan-14','01-Jan-50','Seat22','GREEN','19.00','5')
;
INSERT INTO #PriceTable
VALUES('RETAIL','21-Oct-13','24-Dec-13','Wheel1','BLACK','39.00','1')
;
INSERT INTO #PriceTable
VALUES('PROMOTION','29-Nov-13','01-Dec-13','Wheel1','BLACK','31.20','2')
;
INSERT INTO #PriceTable
VALUES('RETAIL','12-Dec-13','23-Dec-13','Wheel1','BLACK','31.20','3')
;
INSERT INTO #PriceTable
VALUES('MARKDOWN','25-Dec-13','14-Jan-14','Wheel1','BLACK','31.20','4')
;
INSERT INTO #PriceTable
VALUES('MARKDOWN','15-Jan-14','01-Jan-50','Wheel1','BLACK','19.00','5')
;

SELECT * FROM #PriceTable

SELECT a.[StartPrice]
,a.[PriceCode]
,a.[ProductID]
,a.[Colour]
,ISNULL(b.[Price],a.[Price]) AS [Price Before]
,a.[Price] AS [Promotion Price]

FROM #PriceTable a

LEFT OUTER JOIN #PriceTable AS b
ON a.[ProductID] = b.[ProductID]
AND a.[Colour] = b.[Colour]
AND a.[Rank] - 1 = b.[Rank]

WHERE a.[PriceCode] = 'PROMOTION'

-- this line 2013-12-12 Bike15 BLUE should be previous price 39.00 not PROMOTION 31.20
-- this line 2013-12-12 Seat22 GREEN should be previous price 39.00 not PROMOTION 31.20

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-16 : 13:59:39

This works for your sample data (at least as I understand it). And I think it'll work if you have multiple Retail prices and multiple Promo prices for the same product:

SELECT
	Promo.StartPrice 
	,Promo.PriceCode
	,Promo.ProductID
	,Promo.Colour
	,COALESCE(Retail.Price, Promo.Price) AS [Price Before]
	,Promo.Price AS [Promotion Price]
FROM 
	#PriceTable AS Promo
OUTER APPLY
	(
		SELECT TOP 1 
			Price
		FROM 
			#PriceTable
		WHERE 
			PriceCode = 'RETAIL'
			AND Promo.ProductID = ProductID
			AND Promo.[Rank] > [Rank]
		ORDER BY
			[Rank] DESC
	
	) AS Retail
WHERE 
	Promo.PriceCode = 'PROMOTION'

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-04-16 : 14:00:46

Many Thanks Lamprey I'll give it a go.

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2014-04-17 : 03:06:36

Pure class, top stuff that bit of code Lamprey many thanks.

I just changed one line of code PriceCode = 'RETAIL' to PriceCode <> 'PROMOTION' as the price before can sometimes be different to RETAIL just has to not be PROMOTION but you wouldn't have seen that in the sample insert code I sent as they all were RETAIL looking at it now, but looks like it stills works OK on doing PriceCode <> 'PROMOTION'.

I haven't worked out what the OUTER APPLY is doing yet but will dive in to work it out so I understand.

Many thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources