1

As shown in the image:

enter image description here

enter image description here

Here I need to perform cumulative based on a condition:

I have 

   Column 1: Sales Date
   Column 2: Warranty End Date
   Column 3: Qty of Sales
   Column 4: Cumulative Sales Qty

Now for Column 5: Under warranty which gives a wrong value here when the warranty dates complete 365days(expires) I need to subtract the sales happened for that expiry date in the under warranty count column similar the count starts reducing for the following days when the warranty expires.

For eg in the figure: JAN 1,2014 have 246 as cumulative sales when it reaches JAN 1 2015, I need to subtract that '246' from '96502' which is the cumulative sales till JAN 1 2015.So should be 96256

How can we achieve the logic in SQL for 'Under warranty count' column

My Query:

SELECT a."sales_year", 
       a."sales_billing_date", 
       a."warranty_end_date", 
       a."sum_val", 
       a."order_qty", 
       CASE 
         WHEN Sum(Days_between (a."warranty_end_date", a."sales_billing_date")) 
              < 365 
       THEN a."sum_val" 
         ELSE ( ( a."sum_val" ) - ( b."sum_val" ) ) 
       END AS "Under_Warranty" 
FROM   "_SYS_BIC"."pal/cv_pal_tbf_sales" a, 
       "_SYS_BIC"."pal/cv_pal_tbf_sales" b 
GROUP  BY a."sales_year", 
          a."sales_billing_date", 
          a."warranty_end_date", 
          a."order_qty", 
          b."sum_val", 
          a."sum_val";
Sankar
  • 6,908
  • 2
  • 30
  • 53
Neeraja neithyar
  • 27
  • 5

1 Answers1

0

Assuming that SALES_BILLING_DATE AND WARRANTY_END_DATE are 1 year apart throughout the table, you can do a self join by matching above two columns from each instance of the table and doing the subtraction on the UNDER_WARRANTY amount. 

SELECT A.SALES_YEAR, A.SALES_BILLING_DATE, A.WARRANTY_END_DATE, A.SUM_VAL, A.ORDER_QTY,
(A.UNDER_WARRANTY - B.UNDER_WARRANTY) AS UNDER_WARRANTY_NEW
FROM
YOUR_TABLE A
INNER JOIN
YOUR_TABLE B
ON A.SALES_BILLING_DATE = B.WARRANTY_END_DATE;

To retain 2014 entries in the output, try the below query. Changed to left join with the same ON condition and if there is no match then the UNDER_WARRANTY amount remains unchanged. 

SELECT A.SALES_YEAR, A.SALES_BILLING_DATE, A.WARRANTY_END_DATE, A.SUM_VAL, A.ORDER_QTY, 
CASE WHEN B.WARRANTY_END_DATE IS NOT NULL THEN (A.UNDER_WARRANTY - B.UNDER_WARRANTY) ELSE A.UNDER_WARRANTY END AS UNDER_WARRANTY_NEW
FROM
YOUR_TABLE A
LEFT JOIN
YOUR_TABLE B
ON A.SALES_BILLING_DATE = B.WARRANTY_END_DATE;
Vash
  • 1,767
  • 2
  • 12
  • 19
  • How can include 2014 data also becase when inner join happened 2014 disappears,but i should update 2014 data with same a."UNDER_WARRANTY" value. For eg: WHEN (a."Sales_Year" = 2014 ) THEN a."UNDER_WARRANTY" – Neeraja neithyar Feb 02 '18 at 05:56
  • thank a lot. can u please help with one more similar kind of issue https://stackoverflow.com/questions/48577382/cumulative-sum-grouped-by-multiple-parameters – Neeraja neithyar Feb 02 '18 at 08:53