Min/Max values using order by

  • Hi folks,

    I have a product table that needs 4 columns updating using joins to 2 other tables.

    Columns to be updated:

    MaxPrice, MaxPriceDate, MinPrice, MinPriceDate

    The joins are:

    With tblPrice on product, [Month] and [Year]

    and

    With tblDiff on Product, [Month] and [Year] and Location

    Criteria:

    1. The tblPrice and tblDiff ‘Date’ columns must be between the tblProduct.startDate and tblProduct.EndDate range.

    2.tblDiff.Date <= tblPrice.Date

    3.the tblDiff.Date cannot be <= another tblPrice.Date in the tblProduct date range

    4.If 2 values are the same, then the latest date should be used (DESC)

    Something like:

    tblProducts p1 JOIN tblPrice p2

    ON p2.product = p1.Product

    AND p2.[Month] = p1.[Month]

    AND p2.[Year] = p1.[Year]

    JOIN tblDiff d

    ON d.Product = p1.Product

    AND d.[Month] = p1.[Month]

    AND d.[Year] = p1.[Year]

    AND d.Location = p1.Location

    AND d.Date <= p2.Date

    WHERE

    p2.Date BETWEEN p1.StartDate AND p1.EndDate

    AND d.Date BETWEEN p1.StartDate AND p1.EndDate

    ORDER BY d.Date DESC

    The tables are:

    tblProducts

    ProductMonthYearLocationStartDateEndDateMaxPriceMaxPriceDateMinPriceMinPriceDate

    ACBD82013Lon01/11/201231/10/2013

    ACBD92013Lon01/11/201231/10/2013

    ACBD102013Lon01/11/201231/10/2013

    EFGH82013Lon01/11/201231/10/2013

    tblPrice

    DateProductMonthYearPrice

    01/08/2013ACBD92013180

    31/07/2013ACBD92013178

    30/07/2013ACBD92013167

    29/07/2013ACBD92013179

    26/07/2013ACBD92013180

    25/07/2013ACBD92013167

    24/07/2013ACBD92013175

    23/07/2013ACBD92013175

    tblDiff

    DateProductMonthYearLocationPrice

    01/08/2013ACBD92013Lon1

    29/07/2013ACBD92013Lon2

    28/07/2013ACBD92013Lon5

    27/07/2013ACBD92013Lon5

    24/07/2013ACBD92013Lon3

    In my example, these are the results I am expecting where product = ABCD and month = 9 in tblProduct ( PriceDate should be taken as the Price.Date column value).

    MaxPriceMaxPriceDateMinPriceMinPriceDate

    18429/07/201316930/07/2013

    Where

    PriceDatePrice

    01/08/2013181180+1

    31/07/2013180178+2

    30/07/2013169167+2

    29/07/2013184179+5

    26/07/2013183180+3

    25/07/2013170167+3

    24/07/2013178175+3

    23/07/2013N/A

    Any ideas how I can achieve this?

    I am using a cursor and @temp table in my query but it doesn't provide the correct results.

    Thanks in advance,

  • With 146 points it would seem you've been around long enough to know we need some help from you to help you, namely in the form of DDL and sample data.

    CREATE TABLE #tblProducts

    (

    Product VARCHAR(10)

    ,[Month] INT

    ,[Year] INT

    ,Location VARCHAR(5)

    ,StartDate DATE

    ,EndDate DATE

    ,MaxPrice MONEY

    ,MaxPriceDate DATE

    ,MinPrice MONEY

    ,MinPriceDate DATE

    );

    INSERT INTO #tblProducts

    (Product, [Month], [Year], Location, StartDate, EndDate)

    SELECT 'ACBD',8,2013,'Lon','11/01/2012','10/31/2013'

    UNION ALL SELECT 'ACBD',9,2013,'Lon','11/01/2012','10/31/2013'

    UNION ALL SELECT 'ACBD',10,2013,'Lon','11/01/2012','10/31/2013'

    UNION ALL SELECT 'EFGH',8,2013,'Lon','11/01/2012','10/31/2013';

    I must be feeling charitable today, so perhaps this will get you close:

    WITH tblPrice ([Date],Product,[Month],[Year],Price) AS

    (

    SELECT CAST('08/01/2013' AS DATE),'ACBD',9,2013,180

    UNION ALL SELECT '07/31/2013','ACBD',9,2013,178

    UNION ALL SELECT '07/30/2013','ACBD',9,2013,167

    UNION ALL SELECT '07/29/2013','ACBD',9,2013,179

    UNION ALL SELECT '07/26/2013','ACBD',9,2013,180

    UNION ALL SELECT '07/25/2013','ACBD',9,2013,167

    UNION ALL SELECT '07/24/2013','ACBD',9,2013,175

    UNION ALL SELECT '07/23/2013','ACBD',9,2013,175

    )

    ,tblDiff ([Date],Product,[Month],[Year],Location,Price) AS

    (

    SELECT CAST('08/01/2013' AS DATE),'ACBD',9,2013,'Lon',1

    UNION ALL SELECT '07/29/2013','ACBD',9,2013,'Lon',2

    UNION ALL SELECT '07/28/2013','ACBD',9,2013,'Lon',5

    UNION ALL SELECT '07/27/2013','ACBD',9,2013,'Lon',5

    UNION ALL SELECT '07/24/2013','ACBD',9,2013,'Lon',3

    )

    ,Calendar AS

    (

    SELECT [Date]=DATEADD(day, n, (SELECT MIN([Date]) FROM tblPrice))

    FROM

    (

    SELECT 0 UNION ALL

    SELECT TOP (SELECT DATEDIFF(day, MIN([Date]), MAX([Date])) FROM tblPrice)

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns a

    ) a(n)

    ),

    Prices AS

    (

    SELECT c.Product,c.[Month],c.[Year],c.Location

    ,PriceDate=a.[Date]

    ,Price=b.Price + c.Price

    ,rn1=ROW_NUMBER() OVER (PARTITION BY c.Product,c.[Month],c.[Year],c.Location ORDER BY b.Price + c.Price)

    ,rn2=ROW_NUMBER() OVER (PARTITION BY c.Product,c.[Month],c.[Year],c.Location ORDER BY b.Price + c.Price DESC)

    FROM Calendar a

    OUTER APPLY

    (

    SELECT [Date],Product,[Month],[Year],Price

    FROM

    (

    SELECT [Date],Product,[Month],[Year],Price

    ,rn=ROW_NUMBER() OVER (PARTITION BY Product,[Month],[Year] ORDER BY b.[Date] DESC)

    FROM tblPrice b

    ) b

    WHERE rn = 1 AND a.[Date] <= b.[Date]

    ) b

    OUTER APPLY

    (

    SELECT TOP 1 [Date],Product,[Month],[Year],Location,Price

    FROM tblDiff c

    WHERE c.[Date] <= a.[Date] AND

    c.Product = b.Product AND c.[Month] = b.[Month] AND c.[Year] = b.[Year]

    ORDER BY c.[Date] DESC

    ) c

    )

    UPDATE a

    SET MinPrice = b.MinPrice

    ,MinPriceDate = b.MinPriceDate

    ,MaxPrice = b.MaxPrice

    ,MaxPriceDate = b.MaxPriceDate

    FROM #tblProducts a

    JOIN

    (

    SELECT Product,[Month],[Year],Location

    ,MinPrice=MAX(CASE WHEN rn1=1 THEN Price END)

    ,MinPriceDate=MAX(CASE WHEN rn1=1 THEN PriceDate END)

    ,MaxPrice=MAX(CASE WHEN rn2=1 THEN Price END)

    ,MaxPriceDate=MAX(CASE WHEN rn2=1 THEN PriceDate END)

    FROM Prices

    GROUP BY Product,[Month],[Year],Location

    ) b

    ON a.Product = b.Product AND a.[Month] = b.[Month] AND a.[Year] = b.[Year] AND

    a.Location = b.Location;

    SELECT *

    FROM #tblProducts;

    GO

    DROP TABLE #tblProducts;

    Note that I get something slightly different than your expected results, but that may be due to an inconsistency that seems to be in your expected results. Or possibly a minor error in my suggested query or data setup. Either way, it would be incumbent upon you to analyze my method (I believe it is sound) and see if you can make it work for you.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply