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