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.

Answer 1

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