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