| 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
No comments :
Post a Comment