Ndtyjky

How to modify the below sql script to calculate following condition

• 
  Total_Inc_Tax will be qty x unit_price with 5% when the Issue_date between 1-Jan-2018 and 31-Mar-2018 if LPO NULL or not


• 
  

  After 31-Mar-2018 when LPO not NULL shouldn't add 5 % with qty x unit_price other wise add 5%.

  
  
  

  How it works with nested case or suggest any other way.

  
  

My_Table:

+------------+-----+------------+---------------+

| Issue_date | qty | unit_price |      LPO      |

+------------+-----+------------+---------------+

| 10-Jan-18  |   1 |         42 | 1-2018-001166 |

| 12-Jan-18  |   1 |        100 | NULL          |

| 20-Sep-18  |   1 |         25 | NULL          |

| 15-Oct-18  |   2 |         12 | 1-2018-002233 |

| 20-Oct-18  |   1 |        100 | 1-2018-002233 |

+------------+-----+------------+---------------+



SELECT Qty,unit_price,LPO,

    case

    when issue_date <= '2018-03-31'  and issue_date >= '2018-01-01'  

    then (((qty) *(unit_price))  * 1.05 )      

    else    

    (((qty) *(unit_price))  * 1.05 )

    end   as Tot_inc_Tax

    from  My_Table 

Expected Result

+------------+-----+------------+---------------+---------------+

| Issue_date | qty | unit_price |      LPO      | Total_Inc_Tax |

+------------+-----+------------+---------------+---------------+

| 10-Jan-18  |   1 |         42 | 1-2018-001166 |          44.1 |

| 12-Jan-18  |   1 |        100 | NULL          |           105 |

| 20-Sep-18  |   1 |         25 | NULL          |         26.25 |

| 15-Oct-18  |   2 |         12 | 1-2018-002233 |            24 |

| 20-Oct-18  |   1 |        100 | 1-2018-002233 |           100 |

+------------+-----+------------+---------------+---------------+

From your logic you can try this

1. first one use OR




2. second one use AND

And make sure all is match with your logic.

SELECT Issue_date,Qty,unit_price,LPO,

    case

    when (issue_date BETWEEN '2018-01-01' AND '2018-06-30') OR unit_price IS NULL 

      THEN qty *LPO  * 1.05       

    when issue_date > '2018-03-31' AND unit_price IS NOT NULL 

      THEN qty * LPO  END 'Total_Inc_Tax'

from  My_Table 

sqlfiddle

One calculation option would be :

with my_table(Issue_date, qty, unit_price, LPO) as  

(

 select '2018-01-10',1,42 ,'1-2018-001166' union all

 select '2018-01-12',1,100,NULL            union all

 select '2018-09-20',1,25 ,NULL            union all

 select '2018-10-15',2,12 ,'1-2018-002233' union all

 select '2018-10-20',1,100,'1-2018-002233'     

)

select Qty,unit_price,LPO,

       (case

          when issue_date between '2018-01-01' and '2018-03-31'  

            then (((qty) *(unit_price))  * 1.05 )      

          when issue_date > '2018-03-31' then

          ( case when lpo is null then

                 (((qty) *(unit_price))  * 1.05 )

            else

                 (((qty) *(unit_price))) 

            end )

        end ) as Tot_inc_Tax

  from  My_Table;

dbfiddle demo

you can use this :

SELECT Qty,unit_price,LPO,

case

when issue_date between '2018-01-01' and '2018-03-31'  

then (((qty) *(unit_price))  * 1.05 )      

else   when  issue_date > '2018-03-31' and LPO is null

(((qty) *(unit_price))  * 1.05 )

end   as Tot_inc_Tax

from  My_Table