How to add multiple Store procedure in one query

ANS:-

Cerate procedure AllGrossProfit
alter procedure AllGrossProfit
as
begin

create table #tblGrossProfit (
    Formula nvarchar(100),
    Numerator19_20 numeric(19,6),
    Denominator19_20 numeric(19,6),
    RatioPC19_20 numeric(19,6),
    Numerator18_19 numeric(19,6),
    Denominator18_19 numeric(19,6),
    RatioPC18_19 numeric(19,6)
)
insert into #tblGrossProfit exec Netprofitmargin
insert into #tblGrossProfit exec financedashboard6
insert into #tblGrossProfit exec financedashboard4
insert into #tblGrossProfit exec grossprofitratio
insert into #tblGrossProfit exec financedashboard5
insert into #tblGrossProfit exec InventoryTurnoverRatio
insert into #tblGrossProfit exec financedashboard

select * from #tblGrossProfit

drop table #tblGrossProfit
end

exec AllGrossProfit

CurrentRatio+DebtEquityRatio+ReturnOnNetWorth

Q1.CurrentRatio+DebtEquityRatio+ReturnOnNetWorth

USE [Live]
GO
/****** Object:  StoredProcedure [dbo].[financedashboard6]    Script Date: 2/7/2020 2:54:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[financedashboard6]
(@fromdate date='2019-04-01' ,@todate date='2020-03-31')
as
begin

declare @prevfromdate as date='2018-04-01',@prevtodate as date='2019-03-31'
declare @equity decimal(20,3),@equityprev decimal(20,3),@revenue decimal(20,3),@revenueprev decimal(20,3),@expenditure decimal(20,3)
,@expenditureprev decimal(20,3)

set @equity= (select abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where GroupMask=3)
 and t1.refdate<=@todate)

set @equityprev= (select abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where GroupMask=3)
 and t1.refdate<=@prevtodate )

 set @revenue= (select abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where GroupMask=4)
 and t1.refdate<=@todate)

set @revenueprev= (select abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where GroupMask=4)
 and t1.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')

set @expenditure= (select abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where GroupMask=5)
 and t1.refdate<=@todate)

set @expenditureprev= (select abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where GroupMask=5)
 and t1.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')


select 'Current Ratio' as 'Formula', (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where FatherNum in ('125001','133012','142011','143041','153001','170001','170003','170002',
'160001','173003','1730001','173008','173002','999999','110521','111501','112531','113501','125002','131001','173005','173006','173009','173007'))
 and t1.refdate<=@todate) as 'Numerator(19-20)',
(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where FatherNum in ('202051','207111','209003','201041','207117','207115','201021',
'207116','173010','173011','203061','241031'))
 and t1.refdate<=@todate) as 'Denominator(19-20)',
((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where FatherNum in ('125001','133012','142011','143041','153001','170001','170003','170002',
'160001','173003','1730001','173008','173002','999999','110521','111501','112531','113501','125002','131001','173005','173006','173009','173007'))
 and t1.refdate<=@todate)/(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where FatherNum in ('202051','207111','209003','201041','207117','207115','201021',
'207116','173010','173011','203061','241031'))
 and t1.refdate<=@todate)) as 'Ratio %age(19-20)',



(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where FatherNum in ('125001','133012','142011','143041','153001','170001','170003','170002',
'160001','173003','1730001','173008','173002','999999','110521','111501','112531','113501','125002','131001','173005','173006','173009','173007'))
  and t1.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19') as 'Numerator(18-19)',
(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where FatherNum in ('202051','207111','209003','201041','207117','207115','201021',
'207116','173010','173011','203061','241031'))
 and t1.refdate<=@prevtodate ) as 'Denominator(18-19)',
 ((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where FatherNum in ('125001','133012','142011','143041','153001','170001','170003','170002',
'160001','173003','1730001','173008','173002','999999','110521','111501','112531','113501','125002','131001','173005','173006','173009','173007'))
  and t1.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')/(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where FatherNum in ('202051','207111','209003','201041','207117','207115','201021',
'207116','173010','173011','203061','241031'))
 and t1.refdate<=@prevtodate )) as 'Ratio %age(18-19)'

 union all

 select 'Debt Equity Ratio' as 'Formula', (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where FatherNum in ('200004','221041','200005'))
 and t1.refdate<=@todate) as 'Numerator(19-20)',
@equity as 'Denominator(19-20)',
((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where FatherNum in ('200004','221041','200005'))
 and t1.refdate<=@todate)/@equity)
 as 'Ratio %age(19-20)',

(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where FatherNum in ('200004','221041','200005'))
 and t1.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19') as 'Numerator(18-19)',
@equityprev as 'Denominator(18-19)',
((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where FatherNum in ('200004','221041','200005'))
 and t1.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')/@equityprev)
 as 'Ratio %age(18-19)'

union all
 select 'Return On Net Worth' as 'Formula',(@revenue-@expenditure) as 'Numerator(19-20)',@equity as 'Denominator(19-20)',((@revenue-@expenditure)/@equity)*100
 as 'Ratio %age(19-20)' ,
(@revenueprev-@expenditureprev) as 'Numerator(18-19)',@equityprev as 'Denominator(18-19)',((@revenueprev-@expenditureprev)/@equityprev)*100
 as 'Ratio %age(18-19)'

end

SalesOnTotalAssets+TotalDebttoTotalAsset

Q1.SalesOnTotalAssets+TotalDebttoTotalAsset

 USE [Live]
GO
/****** Object:  StoredProcedure [dbo].[financedashboard5]    Script Date: 2/7/2020 2:52:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  procedure [dbo].[financedashboard5]
(@fromdate date='2019-04-01' ,@todate date='2020-03-31')
as
begin

declare @prevfromdate as date='2018-04-01',@prevtodate as date='2019-03-31'
declare @totalassets as decimal(20,5),@totalassetsprev as decimal(20,5)

set @totalassets = (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where GroupMask=1)
 and  t0.refdate<=@todate)

 set @totalassetsprev = (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where GroupMask=1)
  and t0.refdate<=@prevtodate )

select 'Sales On Total Assets' as 'Formula',

(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400191','400193','410000','430000')
and t0.refdate>=@fromdate and t0.refdate<=@todate)
 as 'Numerator(19-20)',@totalassets as 'Denominator(19-20)',

 ((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400191','400193','410000','430000')
and t0.refdate>=@fromdate and t0.refdate<=@todate)/@totalassets)  as 'Ratio %age(19-20)',


(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400191','400193','410000','430000')
and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19') as 'Numerator(18-19)',
@totalassetsprev as 'Denominator(18-19)',
((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400191','400193','410000','430000')
and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')/@totalassetsprev)  as 'Ratio %age(18-19)'

 union all

 select 'Total Debt to Total Asset' as 'Formula',

(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where groupmask=2 and FatherNum in ('200004','221041','200005','209002'))
 and t0.refdate<=@todate)
 as 'Numerator(19-20)',@totalassets as 'Denominator(19-20)',

 ((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where groupmask=2 and FatherNum in ('200004','221041','200005','209002'))
 and t0.refdate<=@todate)/@totalassets) * 100 as 'Ratio %age(19-20)',


(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where groupmask=2 and FatherNum in ('200004','221041','200005','209002'))
 and t0.refdate<=@prevtodate ) as 'Numerator(18-19)',
@totalassetsprev as 'Denominator(18-19)',
((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where groupmask=2 and FatherNum in ('200004','221041','200005','209002'))
 and t0.refdate<=@prevtodate )/@totalassetsprev) * 100 as 'Ratio %age(18-19)'

 end

Debtors Receivable Days+Creditors Payable Days+Debtors Turnover Ratio

 Q 1.

Debtors Receivable Days+Creditors Payable Days+Debtors Turnover Ratio

USE [ Live]
GO
/****** Object:  StoredProcedure [dbo].[financedashboard4]    Script Date: 2/7/2020 2:50:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[financedashboard4]
(@fromdate date='2019-04-01' ,@todate date='2019-12-31')
as
begin

declare @prevfromdate as date='2018-04-01',@prevtodate as date='2019-03-31'
declare @revenuefromoperations as decimal(20,5),@revenuefromoperationsprev as decimal(20,5)

set @revenuefromoperations = (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400193','410000','430000','400190','400010','430001','700022')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)

 set @revenuefromoperationsprev = (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400193','410000','430000','400190','400010','430001','700022')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')

select 'Debtor Receivable Days' as 'Formula',

(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('140000','140010','140030','140060','143021','143030','143031','143033','143034','143035',
'143037','143045','143046','144060','144061','144070','144080')
 and t0.refdate<=@todate)
 as 'Numerator(19-20)',@revenuefromoperations as 'Denominator(19-20)',

 ((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('140000','140010','140030','140060','143021','143030','143031','143033','143034','143035',
'143037','143045','143046','144060','144061','144070','144080')
 and t0.refdate<=@todate)/@revenuefromoperations) * 365 as 'Ratio %age(19-20)',


(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('140000','140010','140030','140060','143021','143030','143031','143033','143034','143035',
'143037','143045','143046','144060','144061','144070','144080')
and  t0.refdate<=@prevtodate ) as 'Numerator(18-19)',
@revenuefromoperationsprev as 'Denominator(18-19)',
((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('140000','140010','140030','140060','143021','143030','143031','143033','143034','143035',
'143037','143045','143046','144060','144061','144070','144080')
 and t0.refdate<=@prevtodate )/@revenuefromoperationsprev) * 365 as 'Ratio %age(18-19)'

 union all

 select 'Creditor Payable Days' as 'Formula',

(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('203000','203030')
 and t0.refdate<=@todate)
 as 'Numerator(19-20)',(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500050','500180','500500','500510','505001','505003','505018','600000','700010')
and  t0.refdate>=@fromdate and t0.refdate<=@todate) as 'Denominator(19-20)',

 ((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('203000','203030')
 and t0.refdate<=@todate)/(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500050','500180','500500','500510','505001','505003','505018','600000','700010')
and  t0.refdate>=@fromdate and t0.refdate<=@todate)) * 365 as 'Ratio %age(19-20)',


(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('203000','203030')
 and t0.refdate<=@prevtodate ) as 'Numerator(18-19)',
(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500050','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19') as 'Denominator(18-19)',
((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('203000','203030')
 and t0.refdate<=@prevtodate )/(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500050','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')) * 365 as 'Ratio %age(18-19)'

 union all

 select 'Debtors Turnover Ratio' as 'Formula',

@revenuefromoperations as 'Numerator(19-20)',(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('140000','140010','140030','140060','143021','143030','143031','143033','143034','143035',
'143037','143045','143046','144060','144061','144070','144080')
 and t0.refdate<=@todate)
 as 'Denominator(19-20)',

 (@revenuefromoperations/(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('140000','140010','140030','140060','143021','143030','143031','143033','143034','143035',
'143037','143045','143046','144060','144061','144070','144080')
 and t0.refdate<=@todate))  as 'Ratio %age(19-20)',

 @revenuefromoperationsprev as 'Numerator(18-19)',(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('140000','140010','140030','140060','143021','143030','143031','143033','143034','143035',
'143037','143045','143046','144060','144061','144070','144080')
and  t0.refdate<=@prevtodate ) as 'Denominator(18-19)',
(@revenuefromoperationsprev/(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('140000','140010','140030','140060','143021','143030','143031','143033','143034','143035',
'143037','143045','143046','144060','144061','144070','144080')
 and t0.refdate<=@prevtodate ))  as 'Ratio %age(18-19)'

end

Inventory Turnover Ratio+Inventory Days

 Q .1 Inventory Turnover Ratio And Inventory Days


USE [Live]
GO
/****** Object:  StoredProcedure [dbo].[InventoryTurnoverRatio]    Script Date: 2/7/2020 2:48:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[InventoryTurnoverRatio]
(@fromdate date='2019-04-01' ,@todate date='2020-03-31')
as
begin

declare @prevfromdate as date='2018-04-01',@prevtodate as date='2019-03-31'
declare @revenuefromoperations as decimal(20,5),@revenuefromoperationsprev as decimal(20,5)


select 'Inventory Days' as 'Formula',
(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where fathernum ='125001')
  and t0.refdate<=@todate) as 'Numerator(19-20)',
(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047') and t0.refdate>=@fromdate and t0.refdate<=@todate)+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050','5700002') 
and t0.refdate>=@fromdate and t0.refdate<=@todate) as 'Denominator(19-20)',

((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where fathernum ='125001')
  and t0.refdate<=@todate)/((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047') and t0.refdate>=@fromdate and t0.refdate<=@todate)+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050','5700002')
and t0.refdate>=@fromdate and t0.refdate<=@todate)))*365 as 'Ratio %age(19-20)',


 (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where fathernum ='125001')
  and t0.refdate<=@prevtodate  ) as 'Numerator(18-19)',

(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047') and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050','5700002')
and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19') as 'Denominator(18-19)',
((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where fathernum ='125001')
 and t0.refdate<=@prevtodate )/((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047') and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050','5700002')
and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')))*365 as 'Ratio %age(18-19)'

union all

select 'Inventory Turnover Ratio' as 'Formula',

(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047') and t0.refdate>=@fromdate and t0.refdate<=@todate)+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050','5700002')
and t0.refdate>=@fromdate and t0.refdate<=@todate) as 'Numerator(19-20)',
(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where fathernum ='125001')
  and t0.refdate<=@todate) as 'Denominator(19-20)',

(((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047') and t0.refdate>=@fromdate and t0.refdate<=@todate)+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050','5700002') 
and t0.refdate>=@fromdate and t0.refdate<=@todate))/(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where fathernum ='125001')
  and t0.refdate<=@todate)) as 'Ratio %age(19-20)',

 (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047') and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050','5700002')
and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19') as 'Numerator(18-19)',

(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where fathernum ='125001')
  and t0.refdate<=@prevtodate  ) as 'Denominator(18-19)',

(((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047') and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050','5700002')
and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19'))/(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where fathernum ='125001')
  and t0.refdate<=@prevtodate )) as 'Ratio %age(18-19)'

end

Operating Profit Margin+Net Profit Margin+PAT Ratio+EBIDTA Ratio+Interest Coverage Ratio

Q. 2 Operating Profit Margin (%)
Q. 3 Net Profit Margin (%)
Q. 4 PAT Ratio

Q. 5EBIDTA Ratio

Q. 6Interest Coverage Ratio

USE [ Live]
GO
/****** Object:  StoredProcedure [dbo].[Netprofitmargin]    Script Date: 2/7/2020 2:45:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Netprofitmargin]
(@fromdate date='2019-04-01' ,@todate date='2020-03-31')
as
begin

declare @prevfromdate as date='2018-04-01',@prevtodate as date='2019-03-31'
declare @revenuefromoperations as decimal(20,5),@revenuefromoperationsprev as decimal(20,5)

set @revenuefromoperations = (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400191','400193','410000','430000','400190','400010','430001','700022')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)

 set @revenuefromoperationsprev = (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400191','400193','410000','430000','400190','400010','430001','700022')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')

select 'EBIDTA Ratio' as 'Formula', (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400191','400193','410000','430000','400190','400010','430001','700022') and t0.refdate>=@fromdate and t0.refdate<=@todate)
-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500050','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047') and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050')
and t0.refdate>=@fromdate and t0.refdate<=@todate) as 'Numerator(19-20)',@revenuefromoperations as 'Denominator(19-20)',
(((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400191','400193','410000','430000','400190','400010','430001','700022')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500050','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050')
 and t0.refdate>=@fromdate and t0.refdate<=@todate))/@revenuefromoperations) * 100 as 'Ratio %age(19-20)',



 (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400191','400193','410000','430000','400190','400010','430001','700022') and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')
-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047') and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050')
and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19') as 'Numerator(18-19)',@revenuefromoperationsprev as 'Denominator(18-19)',
(((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400191','400193','410000','430000','400190','400010','430001','700022')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500050','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19'))/@revenuefromoperationsprev) * 100 as 'Ratio %age(18-19)'

 union all

 select 'Operating Profit Margin (%)' as 'Formula', (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400191','400193','410000','430000','400190','400010','430001','700022') and t0.refdate>=@fromdate and t0.refdate<=@todate)
-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500050','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047') and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050','5700002')
and t0.refdate>=@fromdate and t0.refdate<=@todate) as 'Numerator(19-20)',@revenuefromoperations as 'Denominator(19-20)',
(((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400191','400193','410000','430000','400190','400010','430001','700022')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500050','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050','5700002')
 and t0.refdate>=@fromdate and t0.refdate<=@todate))/@revenuefromoperations) * 100 as 'Ratio %age(19-20)',



 (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400193','410000','430000','400190','400010','430001','700022') and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')
-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500050','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047') and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050','5700002') 
and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19') as 'Numerator(18-19)',@revenuefromoperationsprev as 'Denominator(18-19)',
(((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400191','400193','410000','430000','400190','400010','430001','700022')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500050','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050','5700002')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19'))/@revenuefromoperationsprev) * 100 as 'Ratio %age(18-19)'

 union all

  select 'Net Profit Margin (%)' as 'Formula',((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select a.AcctCode from oact a where a.GroupMask=4)
 and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select a.AcctCode from oact a where a.GroupMask=5)
 and t0.refdate>=@fromdate and t0.refdate<=@todate))+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('800000','800020','800030') and t0.refdate>=@fromdate and t0.refdate<=@todate) as 'Numerator(19-20)',
@revenuefromoperations as 'Denominator(19-20)',
((((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select a.AcctCode from oact a where a.GroupMask=4)
 and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select a.AcctCode from oact a where a.GroupMask=5)
 and t0.refdate>=@fromdate and t0.refdate<=@todate))+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('800000','800020','800030') and t0.refdate>=@fromdate and t0.refdate<=@todate))/@revenuefromoperations)*100 as 'Ratio %age(19-20)',

 ((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select a.AcctCode from oact a where a.GroupMask=4)
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select a.AcctCode from oact a where a.GroupMask=5)
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19'))+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('800000','800020','800030') and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19') as 'Numerator(18-19)',@revenuefromoperationsprev as 'Denominator(18-19)',
((((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select a.AcctCode from oact a where a.GroupMask=4)
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select a.AcctCode from oact a where a.GroupMask=5)
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19'))+(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('800000','800020','800030') and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19'))/@revenuefromoperationsprev)*100 as 'Ratio %age(18-19)'

 union all

select 'PAT Ratio' as 'Formula',(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select a.AcctCode from oact a where a.GroupMask=4)
 and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select a.AcctCode from oact a where a.GroupMask=5)
 and t0.refdate>=@fromdate and t0.refdate<=@todate) as 'Numerator(19-20)',@revenuefromoperations as 'Denominator(19-20)',
(((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select a.AcctCode from oact a where a.GroupMask=4)
 and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select a.AcctCode from oact a where a.GroupMask=5)
 and t0.refdate>=@fromdate and t0.refdate<=@todate))/@revenuefromoperations)*100 as 'Ratio %age(19-20)',

 (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select a.AcctCode from oact a where a.GroupMask=4)
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select a.AcctCode from oact a where a.GroupMask=5)
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19') as 'Numerator(18-19)',@revenuefromoperationsprev as 'Denominator(18-19)',
(((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select a.AcctCode from oact a where a.GroupMask=4)
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select a.AcctCode from oact a where a.GroupMask=5)
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19'))/@revenuefromoperationsprev)*100 as 'Ratio %age(18-19)'

 union all

  select 'Interest Coverage Ratio' as 'Formula', (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400191','400193','410000','430000','400190','400010','430001','700022') and t0.refdate>=@fromdate and t0.refdate<=@todate)
-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500050','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047') and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050','5700002')
and t0.refdate>=@fromdate and t0.refdate<=@todate) as 'Numerator(19-20)',(select abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where FatherNum in ('5610001','5620001'))
 and t1.refdate>=@fromdate and t1.refdate<=@todate) as 'Denominator(19-20)',
(((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400191','400193','410000','430000','400190','400010','430001','700022')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500050','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047')
 and t0.refdate>=@fromdate and t0.refdate<=@todate)-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050','5700002')
 and t0.refdate>=@fromdate and t0.refdate<=@todate))/(select abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where FatherNum in ('5610001','5620001'))
 and t1.refdate>=@fromdate and t1.refdate<=@todate))  as 'Ratio %age(19-20)',



 (SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400193','410000','430000','400190','400010','430001','700022') and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')
-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500050','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047') and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050','5700002')
and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19') as 'Numerator(18-19)',(select abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where FatherNum in ('5610001','5620001'))
 and t1.refdate>=@prevfromdate and t1.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19') as 'Denominator(18-19)',
(((SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400191','400193','410000','430000','400190','400010','430001','700022')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500050','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('5110001','5110002','5110003','5110004','5110006','5110007','5110008','5110009','5110010','5110011','5110012',
'5110013','5110014','5110015','5110016','5110017','5110018','5110019','5110020','5110021','5110022','5110023',
'5110024','5110025','5110026','5110027','5110029','5110030','5110031','5110032','5110033','5110034','5110035','5110036','5110037','5110049','5110046',
'5110047','5310002','5210001','5210002','5210003','5210004','5210007','5210008','5210009','5210010','5210011',
'5210012','5210013','5210014','5210016','5210017','5310003','5310004','5410001','5510001','5510002','5510003',
'5510004','5510005','5510006','5510008','7270001','71770006','511047')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')-(SELECT abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('6111004','6111005','400040','6210001','6210002','6210003','6310002','6310003','6310004','6310005','6310008',
'6410001','6510002','6610001','6610002','7111001','7111002','7111003','7111004','7111005','7111012','7111009','7111011','7120002','7120003','7131000',
'7131001','7140001','7160002','7170001','7170002','7170003','7170004','7170005','7170007','7180002','7190001','640000','7210001','7220002','7220003',
'7220004','7230001','7240001','655030','7250001','7260001','7270002','7270004','7270005','7270006','7270009','7270007','7270008','655040',
'7290001','7320001','420004','7280001','7300001','7300002','7300003','7310001','600020','7330001','7340001','500050','5700002')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19'))/(select abs(sum(abs(T0.[Debit]))-sum(abs(T0.[Credit]))) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in (select acctcode from oact where FatherNum in ('5610001','5620001'))
 and t1.refdate>=@prevfromdate and t1.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19'))  as 'Ratio %age(18-19)'

end

Raw Material Consumption Ratio

 Q1. Raw Material Consumption Ratio


USE [ Live]
GO
/****** Object:  StoredProcedure [dbo].[financedashboard]    Script Date: 2/7/2020 2:42:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[financedashboard]
(@fromdate date='2019-04-01' ,@todate date='2020-01-31')
as
begin
declare @prevfromdate as date='2018-04-01',@prevtodate as date='2019-03-31'
select 'Raw Material Consumption Ratio' as 'Formula',

(SELECT sum(T0.[Debit])-sum(T0.[Credit]) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@fromdate and t0.refdate<=@todate) as 'Numerator(19-20)',

 (SELECT sum(T0.[Debit])-sum(T0.[Credit]) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400193','410000','430000')
 and t0.refdate>=@fromdate and t0.refdate<=@todate) as 'Denominator(19-20)',

 ((SELECT sum(T0.[Debit])-sum(T0.[Credit]) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@fromdate and t0.refdate<=@todate )/(SELECT sum(T0.[Debit])-sum(T0.[Credit]) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400193','410000','430000')
 and t0.refdate>=@fromdate and t0.refdate<=@todate ))*
 100 as 'Ratio %age(19-20)',

 (SELECT sum(T0.[Debit])-sum(T0.[Credit]) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19') as 'Numerator(18-19)',

 (SELECT sum(T0.[Debit])-sum(T0.[Credit]) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400193','410000','430000')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19') as 'Denominator(18-19)',

 ((SELECT sum(T0.[Debit])-sum(T0.[Credit]) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('500005','500025','500180','500500','500510','505001','505003','505018','600000','700010')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19')/(SELECT sum(T0.[Debit])-sum(T0.[Credit]) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400193','410000','430000')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19'))* 100 as 'Ratio %age(18-19)'


end

Accounting Ration Query in sap B1 with formula and Sql Query