CODEWITHSUNDEEP
teradata
viji
viji

Reputation: 477

Teradata- To display only when two row conditions are satisfied

Suppose I pull this data from a table:

Category    Product    Year_Quarter    Sales_Quantity...
Fruit       Apple      2016Q4          300
Fruit       Apple      2017Q1          400
Fruit       Apple      2017Q2          450
Fruit       Orange     2017Q1          45
Fruit       Orange     2017Q2          70
Vegetable   Okra       2016Q4          67
Vegetable   Okra       2017Q1          50
Vegetable   Okra       2017Q2          78....

This is the code I wrote till now to pull in this format

Select Category,Product 
   ,EXTRACT(YEAR from  Date) || 'Q' || TRIM(((CAST(EXTRACT(MONTH FROM Date) AS BYTEINT)-1)/3)+1) as  YEAR_QUARTER
   ,COUNT(DISTINCT Sales) as Sales_Quantity 
from table
where YEAR_QUARTER like any ('%2016Q4','%2017Q1','%2017Q2') 
group by Category,Product,YEAR_QUARTER
order by Category, Product, YEAR_QUARTER

I want the product to be displayed only when there are all three values 2016Q4,2017Q1, 2017Q2 with some sales_quantity and also Sales_quantity is increasing with the year_quarter. So in this case only apple values will be displayed as it satisfies both the conditions. My desired output is just these rows as for orange product year_quarter for 2016Q4 there was no sales_quantity and for Product 'okra' for year quarter 2017Q1 the sales_quantity(50) is less than sales_quantity 67 for 2016Q4. Thus I want to display only when these two conditions are met. Thus it should display only these.

Category    Product    Year_Quarter    Sales_Quantity...
Fruit       Apple      2016Q4          300
Fruit       Apple      2017Q1          400
Fruit       Apple      2017Q2          450

How do I add these conditions in my current code?

Upvotes: 0

Views: 106

Answers (1)

dnoeth
dnoeth

Reputation: 60482

SELECT *
FROM
 (
   SELECT Category
      ,Product
        -- simplified quarter calculation
      ,To_Char(datecol, 'yyyy"Q"q') AS YEAR_QUARTER
        -- do you really need DISTINCT?
      ,Count(DISTINCT Sales) AS Sales_Quantity 
      ,CASE-- check if previous row is lower (or the 1st row)
         WHEN 
           Min(Sales_Quantity)
           Over (PARTITION BY Category,Product
                 ORDER BY YEAR_QUARTER
                 ROWS BETWEEN 1 Preceding AND 1 Preceding) > Sales_Quantity
         THEN 1 --higher
         ELSE 0 --lower or 1st row
       END AS flag
   FROM tab
     -- simplified
   WHERE datecol BETWEEN DATE '2016-10-01' AND DATE '2017-06-30' 
   GROUP BY Category,Product,YEAR_QUARTER
     only those where all 3 quarters exist
   QUALIFY Count(*) Over (PARTITION BY Category,Product) = 3
 ) AS dt
  -- only those rows with increasing sales
QUALIFY Max(flag)
        Over (PARTITION BY Category,Product) = 0

You should avoid using the year/quarter in WHERE, better calculate the begin and end range. If you want to do this dynamically based on today you get the last three quarters using

BETWEEN Add_Months(Trunc(Current_Date, 'Q'),-9)
    AND Trunc(Current_Date, 'Q') -1

Upvotes: 2

Related Questions