Raw Material Consumption Ratio

 Q1. Raw Material Consumption Ratio


USE [ Live]
GO
/****** Object:  StoredProcedure [dbo].[financedashboard]    Script Date: 2/7/2020 2:42:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[financedashboard]
(@fromdate date='2019-04-01' ,@todate date='2020-01-31')
as
begin
declare @prevfromdate as date='2018-04-01',@prevtodate as date='2019-03-31'
select 'Raw Material Consumption Ratio' as 'Formula',

(SELECT sum(T0.[Debit])-sum(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) as 'Numerator(19-20)',

 (SELECT sum(T0.[Debit])-sum(T0.[Credit]) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400193','410000','430000')
 and t0.refdate>=@fromdate and t0.refdate<=@todate) as 'Denominator(19-20)',

 ((SELECT sum(T0.[Debit])-sum(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 sum(T0.[Debit])-sum(T0.[Credit]) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400193','410000','430000')
 and t0.refdate>=@fromdate and t0.refdate<=@todate ))*
 100 as 'Ratio %age(19-20)',

 (SELECT sum(T0.[Debit])-sum(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') as 'Numerator(18-19)',

 (SELECT sum(T0.[Debit])-sum(T0.[Credit]) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400193','410000','430000')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19') as 'Denominator(18-19)',

 ((SELECT sum(T0.[Debit])-sum(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 sum(T0.[Debit])-sum(T0.[Credit]) FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId=t1.TransId
WHERE T0.[Account] in ('7150001','655041','400000','400193','410000','430000')
 and t0.refdate>=@prevfromdate and t0.refdate<=@prevtodate and t1.memo <> 'For Closing Period 2018-19'))* 100 as 'Ratio %age(18-19)'


end