Hello,
I have the below query which is taking about 50 mins to execute. I am new to sql and not sure if it can be optimized. Using t-sql.
The query is calculating Interest, Principal and Payable for each month starting from Oct - Sept using the Vend_Cal tbl. All the calculated fields along with the fields from the table are required as output. Nov calculations are dependent on Oct, Dec are dependent on Nov and so on until Sept. I am using a cte for each month and pushing down those calculations until Sept. Is there a better way to accomplish this? Any help and guidance is appreciated. Thank you.
with cte as (select * from Vendor_Calc),
Oct as ( select *, 0 as Oct_Interest, case when Check ='I' then Oct_Vend- 0 else 0 end Oct_Principal, round(AT_lb - case when Check ='I' then (Oct_Vend- 0 else 0 end, 2) as Oct_Payable from cte ), Nov as ( select a. *, round(case when Check ='I' then Nov_Vend - Nov_Interest else 0 end,2) as Nov_Principal, round(AT-Lb - case when Check ='I' then Nov_Vend -Nov_Interest else 0 end,2) as Nov_Payable from ( select *, round(case when Nov_Vend>0 then Oct_Payable * Rate else 0 end,2) as Nov_Interest from Oct )a ),
Dec as ( select a. *, round(case when Check ='I' then Dec_Vend - Dec_Interest else 0 end,2) as Dec_Principal, round(AT-Lb - case when Check ='I' then Dec_Vend -Dec_Interest else 0 end,2) as Dec_Payable from ( select *, round(case when Dec_Vend>0 then Nov_Payable * Rate else 0 end,2) as Dec_Interest from Nov )a ),
Jan as ( select a. *, round(case when Check ='I' then Jan_Vend - Jan_Interest else 0 end,2) as Jan_Principal, round(AT-Lb - case when Check ='I' then Jan_Vend -Jan_Interest else 0 end,2) as Jan_Payable from ( select *, round(case when Jan_Vend>0 then Dec_Payable * Rate else 0 end,2) as Jan_Interest from Nov )a )
Feb as ( select a. *, round(case when Check ='I' then Feb_Vend - Feb_Interest else 0 end,2) as Feb_Principal, round(AT-Lb - case when Check ='I' then Feb_Vend -Feb_Interest else 0 end,2) as Feb_Payable from ( select *, round(case when Feb_Vend>0 then Jan_Payable * Rate else 0 end,2) as Feb_Interest from Jan )a ),
Mar as ( select a. *, round(case when Check ='I' then Mar_Vend - Mar_Interest else 0 end,2) as Mar_Principal, round(AT-Lb - case when Check ='I' then Mar_Vend -Mar_Interest else 0 end,2) as Mar_Payable from ( select *, round(case when Mar_Vend>0 then Feb_Payable * Rate else 0 end,2) as Mar_Interest from Feb )a ),
Apr as ( select a. *, round(case when Check ='I' then Apr_Vend - Apr_Interest else 0 end,2) as Apr_Principal, round(AT-Lb - case when Check ='I' then Apr_Vend -Apr_Interest else 0 end,2) as Apr_Payable from ( select *, round(case when Apr_Vend>0 then Mar_Payable * Rate else 0 end,2) as Apr_Interest from Mar )a ),
May as ( select a. *, round(case when Check ='I' then May_Vend - May_Interest else 0 end,2) as May_Principal, round(AT-Lb - case when Check ='I' then May_Vend -May_Interest else 0 end,2) as May_Payable from ( select *, round(case when May_Vend>0 then Apr_Payable * Rate else 0 end,2) as May_Interest from Apr )a ),
Jun as ( select a. *, round(case when Check ='I' then Jun_Vend - Jun_Interest else 0 end,2) as Jun_Principal, round(AT-Lb - case when Check ='I' then Jun_Vend -Jun_Interest else 0 end,2) as Jun_Payable from ( select *, round(case when Jun_Vend>0 then May_Payable * Rate else 0 end,2) as Jun_Interest from May )a ),
Jul as ( select a. *, round(case when Check ='I' then Jul_Vend - Jul_Interest else 0 end,2) as Jul_Principal, round(AT-Lb - case when Check ='I' then Jul_Vend -Jul_Interest else 0 end,2) as Jul_Payable from ( select *, round(case when Jul_Vend>0 then Jun_Payable * Rate else 0 end,2) as Jul_Interest from Jun )a ),
Aug as ( select a. *, round(case when Check ='I' then Aug_Vend - Aug_Interest else 0 end,2) as Aug_Principal, round(AT-Lb - case when Check ='I' then Aug_Vend -Aug_Interest else 0 end,2) as Aug_Payable from ( select *, round(case when Aug_Vend>0 then Jul_Payable * Rate else 0 end,2) as Aug_Interest from Jul )a ),
Sep as ( select a. *, round(case when Check ='I' then Sep_Vend - Sep_Interest else 0 end,2) as Sep_Principal, round(AT-Lb - case when Check ='I' then Sep_Vend -Sep_Interest else 0 end,2) as Sep_Payable from ( select *, round(case when Sep_Vend>0 then Aug_Payable * Rate else 0 end,2) as Sep_Interest from Aug )a )
Select * from Sept;