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