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.
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 aLEFT OUTER JOIN PriceTable bON a.[ProductID] = b.[ProductID]AND a.[Colour] = b.[Colour]AND a.[Rank] - 1 = b.[Rank]WHERE a.[PriceCode] = 'PROMOTION'TablePriceCode StartPrice EndPrice ProductID Colour Price RankRETAIL 21-Oct-13 24-Dec-13 Bike15 BLUE 39.00 1PROMOTION 29-Nov-13 01-Dec-13 Bike15 BLUE 31.20 2PROMOTION 12-Dec-13 23-Dec-13 Bike15 BLUE 31.20 3MARKDOWN 25-Dec-13 14-Jan-14 Bike15 BLUE 31.20 4MARKDOWN 15-Jan-14 01-Jan-50 Bike15 BLUE 19.00 5RETAIL 21-Oct-13 24-Dec-13 Seat22 GREEN 39.00 1PROMOTION 29-Nov-13 01-Dec-13 Seat22 GREEN 31.20 2PROMOTION 12-Dec-13 23-Dec-13 Seat22 GREEN 31.20 3MARKDOWN 25-Dec-13 14-Jan-14 Seat22 GREEN 31.20 4PROMOTION 15-Jan-14 01-Jan-50 Seat22 GREEN 19.00 5RETAIL 21-Oct-13 24-Dec-13 Wheel1 BLACK 39.00 1PROMOTION 29-Nov-13 01-Dec-13 Wheel1 BLACK 31.20 2RETAIL 12-Dec-13 23-Dec-13 Wheel1 BLACK 31.20 3MARKDOWN 25-Dec-13 14-Jan-14 Wheel1 BLACK 31.20 4MARKDOWN 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 pricePROMOTION 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.00PROMOTION 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
|
CorrectionI 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 TableIF (SELECT Object_id('tempdb..#PriceTable')) <> 0 BEGIN DROP TABLE #PriceTable END-- Create Temp tablesCREATE 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 #PriceTableVALUES('RETAIL','21-Oct-13','24-Dec-13','Bike15','BLUE','39.00','1');INSERT INTO #PriceTableVALUES('PROMOTION','29-Nov-13','01-Dec-13','Bike15','BLUE','31.20','2');INSERT INTO #PriceTableVALUES('PROMOTION','12-Dec-13','23-Dec-13','Bike15','BLUE','31.20','3');INSERT INTO #PriceTableVALUES('MARKDOWN','25-Dec-13','14-Jan-14','Bike15','BLUE','31.20','4');INSERT INTO #PriceTableVALUES('MARKDOWN','15-Jan-14','01-Jan-50','Bike15','BLUE','19.00','5');INSERT INTO #PriceTableVALUES('RETAIL','21-Oct-13','24-Dec-13','Seat22','GREEN','39.00','1');INSERT INTO #PriceTableVALUES('PROMOTION','29-Nov-13','01-Dec-13','Seat22','GREEN','31.20','2');INSERT INTO #PriceTableVALUES('PROMOTION','12-Dec-13','23-Dec-13','Seat22','GREEN','31.20','3');INSERT INTO #PriceTableVALUES('MARKDOWN','25-Dec-13','14-Jan-14','Seat22','GREEN','31.20','4');INSERT INTO #PriceTableVALUES('PROMOTION','15-Jan-14','01-Jan-50','Seat22','GREEN','19.00','5');INSERT INTO #PriceTableVALUES('RETAIL','21-Oct-13','24-Dec-13','Wheel1','BLACK','39.00','1');INSERT INTO #PriceTableVALUES('PROMOTION','29-Nov-13','01-Dec-13','Wheel1','BLACK','31.20','2');INSERT INTO #PriceTableVALUES('RETAIL','12-Dec-13','23-Dec-13','Wheel1','BLACK','31.20','3');INSERT INTO #PriceTableVALUES('MARKDOWN','25-Dec-13','14-Jan-14','Wheel1','BLACK','31.20','4');INSERT INTO #PriceTableVALUES('MARKDOWN','15-Jan-14','01-Jan-50','Wheel1','BLACK','19.00','5');SELECT * FROM #PriceTableSELECT a.[StartPrice] ,a.[PriceCode] ,a.[ProductID] ,a.[Colour] ,ISNULL(b.[Price],a.[Price]) AS [Price Before] ,a.[Price] AS [Promotion Price]FROM #PriceTable aLEFT OUTER JOIN #PriceTable AS bON 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 PromoOUTER APPLY ( SELECT TOP 1 Price FROM #PriceTable WHERE PriceCode = 'RETAIL' AND Promo.ProductID = ProductID AND Promo.[Rank] > [Rank] ORDER BY [Rank] DESC ) AS RetailWHERE 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. |
|
|
|
|
|
|
|