SQL query for Pricing analysis

Question

Facing issue to find the Min and Max pricing status on the column YearMonth,

Below is my table data

   YearMonth    STATE   ProductGroup    LocaProdname    Price
    201407      MH      AIRTEL          AIRTEL-3G       10,000
    201208      GJ      IDEA            IDEA-3G         1,200
    201406      WB      AIRCEL          AIRCEL PERPAID  5,866
    201407      DL      TATA DOCOMA     TATA LANDLINE   8,955
    201207      KAR     VODAFONE        VODAFONE-3G     7,899
    201312      MH      AIRTEL          AIRTEL-3G       15,000
    201408      GJ      IDEA            IDEA-3G         25,000

I require below output:

YearMonth   STATE   ProductGroup    LocaProdname    Price   Indictor-YEAR
201407      MH      AIRTEL          AIRTEL-3G       10,000  MAX
201312      MH      AIRTEL          AIRTEL-3G       15,000  MIN
201408      GJ      IDEA            IDEA-3G         25,000  MAX
201208      GJ      IDEA            IDEA-3G         1,200   MIN

I need the Max yearmonth and min Year values values.

possible duplicate of [Get values from first and last row per group](http://stackoverflow.com/questions/25170215/get-values-from-first-and-last-row-per-group) — Bulat, Sep 15 '14 at 15:30
http://stackoverflow.com/questions/tagged/greatest-n-per-group+sql-server — Bulat, Sep 15 '14 at 15:39

Gordon Linoff · Answer 1 · 2014-09-15T15:49:00.610

If I understand correctly, you can do this with row_number():

select YearMonth, STATE, ProductGroup, LocaProdname, Price,
       (case when seqnum_asc = 1 then 'MIN' else 'MAX' end) as Indicator
from (select d.*,
             row_number() over (partition by state, productgroup, localprodname
                                order by price asc) as seqnum_asc,
             row_number() over (partition by state, productgroup, localprodname
                                order by pricedesc) as seqnum_desc
      from data
     ) d
where seqnum_asc = 1 or seqnum_desc = 1;

EDIT:

Does this do what you want?

select YearMonth, STATE, ProductGroup, LocaProdname, Price,
       (case when seqnum_asc = 1 then 'MIN' else 'MAX' end) as Indicator
from (select d.*,
             row_number() over (partition by YearMonth
                                order by price asc) as seqnum_asc,
             row_number() over (partition by YearMOnth
                                order by pricedesc) as seqnum_desc
      from data
     ) d
where seqnum_asc = 1 or seqnum_desc = 1;

Not getting what exacly user requires , I need a out put where user can see the Max value of the YearMonth with the Price of that respective row. — Ajit Kadam, Sep 15 '14 at 15:31

anothershrubery · Answer 2 · 2014-09-15T15:49:30.657

This can be done by finding the MAX/MIN values associated with the LocaProdname,ProductGroup and State then joining in on the table where everything matches. See below, or view the fiddle at http://sqlfiddle.com/#!3/4d6bd/2

NOTE: I've added in HAVING COUNT(*) > 1 as you seem to only want ones which have changed price. (Ie. Have more than 1 entry)

SELECT p.YearMonth
    ,p.State
    ,p.ProductGroup
    ,p.LocaProdname
    ,p.Price
    ,CASE 
        WHEN p.Price = a.MaxPrice
            THEN 'MAX'
        WHEN p.Price = a.MinPrice
            THEN 'MIN'
        END AS [Indicator-YEAR]
FROM PricingTest p
INNER JOIN (
    SELECT LocaProdname
        ,ProductGroup
        ,State
        ,MAX(Price) AS MaxPrice
        ,MIN(Price) AS MinPrice
    FROM pricingTest
    GROUP BY LocaProdname
        ,ProductGroup
        ,State
    HAVING COUNT(*) > 1
    ) a ON a.LocaProdname = p.LocaProdname
    AND a.ProductGroup = p.ProductGroup
    AND a.State= p.State
    AND (
        a.MaxPrice = p.Price
        OR a.MinPrice = p.Price
        )
ORDER BY LocaProdname

EDIT: Or I just noticed it's the max/min YearMonth the user might be looking, if this is the case check out http://sqlfiddle.com/#!3/4d6bd/4 It is basically just replacing all references to Price to YearMonth.

I have edit as YearMonth wise but I need two records on for the single Product, That is min YearMonth with all values and Max YearMonth, now it showing all records for one product. — Ajit Kadam, Sep 15 '14 at 16:36
Did you check the sqlfiddle? It DOES show 2 lines for each product. — anothershrubery, Sep 16 '14 at 13:07

score 0 · Answer 3 · answered Sep 15 '14 at 15:49

Once you get the last and first record you can UNION results:

SELECT t.*, 'MIN' AS Indicator
FROM 
  myTable t LEFT JOIN 
  myTable t2 ON t.YearMonth = t2.YearMonth AND t2.price < t.price
WHERE t2.YearMonth IS NULL
UNION
SELECT t.*, 'MAX' AS Indicator
FROM 
  myTable t LEFT JOIN 
  myTable t2 ON t.YearMonth = t2.YearMonth AND t2.price > t.price
WHERE t2.YearMonth IS NULL

If you have several records with same highest price, above query will return all of them. Also if you only have one record in a month, it will be returned twice as both MIN and MAX.

score 0 · Accepted Answer · answered Sep 15 '14 at 17:58

Please use Row_number with partition BY and remove unwanted code as per your need,

SELECT yearmonth,state,productgroup,locaprodname,price,operation
    FROM   (
    SELECT * FROM   (SELECT p.yearmonth,p.state,p.productgroup,p.locaprodname,p.price,'MAX' AS Operation,
                           Row_number() OVER( partition BY p.productgroup, p.locaprodname
                               ORDER BY p.price DESC) AS Row
                    FROM   pricingtest p) AS Maxx
            WHERE  Maxx.row = 1

    UNION ALL

    SELECT * FROM   (SELECT p.yearmonth,p.state,p.productgroup,p.locaprodname,p.price,'MIN' AS Operation,
                           Row_number() OVER( partition BY p.productgroup, p.locaprodname
                               ORDER BY p.price ASC) AS Row
                    FROM   pricingtest p) AS Minn
            WHERE  Minn.row = 1

    ) AS whole
    ORDER  BY yearmonth,productgroup

SQL query for Pricing analysis

4 Answers4