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