Create Proc SP_CustomerWiseSummary    
--DECLARE                                                
@searchString VARCHAR(500)='1=1',                          
@FromDate DATETIME='2019-05-01',                          
@ToDate DATETIME='2019-05-01',                          
@Franchisee VARCHAR(max)='ALL'                          
AS    
    
BEGIn    
CREATE TABLE #tblCustomerType(CustomerType varchar(50))    
CREATE TABLE #tblCustomer(CustomerType varchar(50),CustomerCode varchar(50),CustomerName varchar(50))    
CREATE TABLE #tblColumn(ColumnValue varchar(100),Value Numeric(18,5))    
INSERT INTO #tblColumn(ColumnValue,Value)    
SELECT 'Units',12.00    
UNION ALL    
SELECT 'Liters',128.00    
UNION ALL    
SELECT 'Liters %',3421.90    
UNION ALL    
SELECT 'Sales $',4362.0092    
UNION ALL    
SELECT 'Commission $',4529.90    
UNION ALL    
SELECT 'Fees $',429.90    
UNION ALL    
SELECT 'Income $',452.90    
UNION ALL    
SELECT 'Cost $',4909.90    
UNION ALL    
SELECT 'Rebate $',1003.432    
UNION ALL    
SELECT 'Credit $',5022    
UNION ALL    
SELECT 'Margin $',30993    
UNION ALL    
SELECT 'Margin %',9043    
UNION ALL    
SELECT 'Margin % Per Cust',7021.43    
UNION ALL    
SELECT 'Margin Per Del Day',6032.90    
   
    
    
INSERT INTO #tblCustomerType(CustomerType)    
    
SELECT 'DCOM Customer'     
UNION ALL    
SELECT 'Franchisee Customer'   
  
    
INSERT INTO #tblCustomer(CustomerType,CustomerCode,CustomerName)    
SELECT 'DCOM Customer' ,'13000314','Countdown Andersons Bay'    
UNION ALL    
SELECT 'DCOM Customer' ,'13000361','Countdown Dunedin Central'    
UNION ALL    
SELECT 'DCOM Customer' ,'13032314','Countdown Dunedin South'     
UNION ALL    
SELECT 'Franchisee Customer' ,'13000655','New World Mosgiel'    
UNION ALL    
SELECT 'Franchisee Customer' ,'14017644','New World Northside'    
UNION ALL    
SELECT 'Franchisee Customer' ,'13001424','New World Gardens'   
UNION ALL    
SELECT 'Franchisee Customer' ,'13001424','New World Northside'    
    
SELECT DISTINCt * FROm #tblColumn    
SELECT DISTINCT *,'' CustomerCode,'' CustomerName from #tblCustomerType --WHERE (RouteCode=@Route Or ISNULL(@Route,'')='All' or ISNULL(@Route,'')='')    
  
SELECT DISTINCT * from #tblCustomerType Ct INNER JOIN #tblCustomer C ON CT.CustomerType=C.CustomerType  
   
SELECT DISTINCT * from #tblCustomerType R INNER JOIN #tblCustomer PC ON 1=1      
INNER JOIn #tblColumn C ON 1=1    
--WHERE (R.RouteCode=@Route Or ISNULL(@Route,'')='All' or ISNULL(@Route,'')='')    
    
    
DROP TABLE #tblCustomerType    
DROP TABLE #tblCustomer    
DROP TABLE #tblColumn    
    
END



GO



CREATE proc SP_GetHeaderDetails  
--Declare  
@StartDate varchar(100)='',  
@EndDate varchar(100)='',  
@Franchisee varchar(max)=''  
AS   
BEGIn  
  
CREATE Table #tblHeader (Id int identity(1,1), HeaderName Varchar(100))  
INSERT INTO #tblHeader(HeaderName)  
SELECT 'Units'  
UNION ALL  
SELECT 'Liters'  
UNION ALL  
SELECT 'Liters Var%'  
  
CREATE Table #tblProductCategory (Id int identity(1,1), ProductCategoryCode Varchar(100),ProductCategoryName Varchar(100))  
INSERT INTO  #tblProductCategory(ProductCategoryCode,ProductCategoryName)  
SELECT 'Butter and Spreads','Butter and Spreads'  
UNION ALL  
SELECT 'Cheese','Cheese'  
UNION ALL  
SELECT 'Chilled Desserts','Chilled Desserts'  
UNION ALL  
SELECT 'Cream - Anchor','Cream - Anchor'  
UNION ALL  
SELECT 'Cream - Other','Cream - Other'  
UNION ALL  
SELECT 'Cultured','Cultured'  
UNION ALL  
SELECT 'Flavoured Milk','Flavoured Milk'  
UNION ALL  
SELECT 'Fonterra Milk for Schools','Fonterra Milk for Schools'  
UNION ALL  
SELECT 'Juices','Juices'  
UNION ALL  
SELECT 'Specialty Cheese','Specialty Cheese'  
UNION ALL  
SELECT 'Cultured','Cultured'  
UNION ALL  
SELECT 'Flavoured Milk','Flavoured Milk'  
UNION ALL  
SELECT 'Third Party DCOM Product','Third Party DCOM Product'  
UNION ALL  
SELECT 'Third Party Product','Third Party Product'  
UNION ALL  
SELECT 'White Milk - Anchor','White Milk - Anchor'  
UNION ALL  
SELECT 'Total','Total'  
  
CREATE TABLE #tblHeaderDetails(ThisYear Numeric(18,5),LastYear Numeric(18,5),VarPercentage Numeric(18,5))  
INSERT INTO #tblHeaderDetails(ThisYear,LastYear,VarPercentage)  
SELECT 352.12,231.12,32.9  
UNION ALL  
SELECT 32.12,21.12,3  
UNION ALL  
SELECT 230.12,210.12,30  
UNION ALL  
SELECT 302.12,109.12,41  
UNION ALL  
SELECT 231.12,111.12,91  
UNION ALL  
SELECT 201.12,21.12,9  
UNION ALL  
SELECT 30.12,19.12,41  
  
  
SELECT DISTINCT * FROM #tblHeader Order By Id  
SELECT DISTINCT * FROM #tblProductCategory Order By Id  
SELECT * FROM #tblHeader H INNER JOIN #tblProductCategory PC ON 1=1  
INNER JOIN #tblHeaderDetails HD ON 1=1  
  
DROP TABLE #tblHeader  
DROP TABLE #tblProductCategory  
DROP TABLE #tblHeaderDetails  
  
END

GO

Create proc SP_GetHeaderDetails1  
--Declare  
@StartDate varchar(100)='',  
@EndDate varchar(100)='',  
@Franchisee varchar(max)=''  
AS   
BEGIn  
  
CREATE Table #tblHeader (Id int identity(1,1), HeaderName Varchar(100))  
INSERT INTO #tblHeader(HeaderName)  
SELECT 'FR Sales $'  
UNION ALL  
SELECT 'OB Commission $'  
UNION ALL  
SELECT 'DCOM Income $'  
  
CREATE Table #tblProductCategory (Id int identity(1,1), ProductCategoryCode Varchar(100),ProductCategoryName Varchar(100))  
INSERT INTO  #tblProductCategory(ProductCategoryCode,ProductCategoryName)  
SELECT 'Butter and Spreads','Butter and Spreads'  
UNION ALL  
SELECT 'Cheese','Cheese'  
UNION ALL  
SELECT 'Chilled Desserts','Chilled Desserts'  
UNION ALL  
SELECT 'Cream - Anchor','Cream - Anchor'  
UNION ALL  
SELECT 'Cream - Other','Cream - Other'  
UNION ALL  
SELECT 'Cultured','Cultured'  
UNION ALL  
SELECT 'Flavoured Milk','Flavoured Milk'  
UNION ALL  
SELECT 'Fonterra Milk for Schools','Fonterra Milk for Schools'  
UNION ALL  
SELECT 'Juices','Juices'  
UNION ALL  
SELECT 'Specialty Cheese','Specialty Cheese'  
UNION ALL  
SELECT 'Cultured','Cultured'  
UNION ALL  
SELECT 'Flavoured Milk','Flavoured Milk'  
UNION ALL  
SELECT 'Third Party DCOM Product','Third Party DCOM Product'  
UNION ALL  
SELECT 'Third Party Product','Third Party Product'  
UNION ALL  
SELECT 'White Milk - Anchor','White Milk - Anchor'  
UNION ALL  
SELECT 'Total','Total'  
  
CREATE TABLE #tblHeaderDetails(ThisYear Numeric(18,5),LastYear Numeric(18,5),VarPercentage Numeric(18,5))  
INSERT INTO #tblHeaderDetails(ThisYear,LastYear,VarPercentage)  
SELECT 352.12,231.12,32.9  
UNION ALL  
SELECT 32.12,21.12,3  
UNION ALL  
SELECT 230.12,210.12,30  
UNION ALL  
SELECT 302.12,109.12,41  
UNION ALL  
SELECT 231.12,111.12,91  
UNION ALL  
SELECT 201.12,21.12,9  
UNION ALL  
SELECT 30.12,19.12,41  
  
  
SELECT DISTINCT * FROM #tblHeader Order By Id  
SELECT DISTINCT * FROM #tblProductCategory Order By Id  
SELECT * FROM #tblHeader H INNER JOIN #tblProductCategory PC ON 1=1  
INNER JOIN #tblHeaderDetails HD ON 1=1  
  
DROP TABLE #tblHeader  
DROP TABLE #tblProductCategory  
DROP TABLE #tblHeaderDetails  
  
END

GO

Create proc SP_GetHeaderDetails2  
--Declare  
@StartDate varchar(100)='',  
@EndDate varchar(100)='',  
@Franchisee varchar(max)=''  
AS   
BEGIn  
  
CREATE Table #tblHeader (Id int identity(1,1), HeaderName Varchar(100))  
INSERT INTO #tblHeader(HeaderName)  
SELECT 'Income $'  
UNION ALL  
SELECT 'Cost $'  
UNION ALL  
SELECT 'Margin $'  
UNION ALL  
SELECT 'Margin %'  
  
CREATE Table #tblProductCategory (Id int identity(1,1), ProductCategoryCode Varchar(100),ProductCategoryName Varchar(100))  
INSERT INTO  #tblProductCategory(ProductCategoryCode,ProductCategoryName)  
SELECT 'Butter and Spreads','Butter and Spreads'  
UNION ALL  
SELECT 'Cheese','Cheese'  
UNION ALL  
SELECT 'Chilled Desserts','Chilled Desserts'  
UNION ALL  
SELECT 'Cream - Anchor','Cream - Anchor'  
UNION ALL  
SELECT 'Cream - Other','Cream - Other'  
UNION ALL  
SELECT 'Cultured','Cultured'  
UNION ALL  
SELECT 'Flavoured Milk','Flavoured Milk'  
UNION ALL  
SELECT 'Fonterra Milk for Schools','Fonterra Milk for Schools'  
UNION ALL  
SELECT 'Juices','Juices'  
UNION ALL  
SELECT 'Specialty Cheese','Specialty Cheese'  
UNION ALL  
SELECT 'Cultured','Cultured'  
UNION ALL  
SELECT 'Flavoured Milk','Flavoured Milk'  
UNION ALL  
SELECT 'Third Party DCOM Product','Third Party DCOM Product'  
UNION ALL  
SELECT 'Third Party Product','Third Party Product'  
UNION ALL  
SELECT 'White Milk - Anchor','White Milk - Anchor'  
UNION ALL  
SELECT 'Total','Total'  
  
CREATE TABLE #tblHeaderDetails(ThisYear Numeric(18,5),LastYear Numeric(18,5),VarPercentage Numeric(18,5))  
INSERT INTO #tblHeaderDetails(ThisYear,LastYear,VarPercentage)  
SELECT 352.12,231.12,32.9  
UNION ALL  
SELECT 32.12,21.12,3  
UNION ALL  
SELECT 230.12,210.12,30  
UNION ALL  
SELECT 302.12,109.12,41  
UNION ALL  
SELECT 231.12,111.12,91  
UNION ALL  
SELECT 201.12,21.12,9  
UNION ALL  
SELECT 30.12,19.12,41  
  
  
SELECT DISTINCT * FROM #tblHeader Order By Id  
SELECT DISTINCT * FROM #tblProductCategory Order By Id  
SELECT * FROM #tblHeader H INNER JOIN #tblProductCategory PC ON 1=1  
INNER JOIN #tblHeaderDetails HD ON 1=1  
  
DROP TABLE #tblHeader  
DROP TABLE #tblProductCategory  
DROP TABLE #tblHeaderDetails  
  
END

GO

Create Proc SP_RouteWiseSummary  
--DECLARE                       
@SortExpression VARCHAR(50)='UserName ASC',                         
@maximumRows INT=50,                        
@startRowIndex INT=1,                        
@searchString VARCHAR(500)='1=1',                        
@FromDate DATETIME='2019-05-01',                        
@ToDate DATETIME='2019-05-01',                        
@Route VARCHAR(max)='DunWeek',                        
@BrandName VARCHAR(MAX)='1301'   
AS  
  
BEGIn  
CREATE TABLE #tblRoute(RouteCode varchar(50),RouteName varchar(50))  
CREATE TABLE #tblProductCategory(ProductCategoryCode varchar(50),ProductCategoryName varchar(50))  
CREATE TABLE #tblColumn(ColumnValue varchar(100),Value Numeric(18,5))  
INSERT INTO #tblColumn(ColumnValue,Value)  
SELECT 'Units',12.00  
UNION ALL  
SELECT 'FR Units',128.00  
UNION ALL  
SELECT 'OB Units',3421.90  
UNION ALL  
SELECT 'DCOM Units',4362.0092  
UNION ALL  
SELECT 'Liters',4529.90  
UNION ALL  
SELECT 'FR Liters',429.90  
UNION ALL  
SELECT 'OB Liters',452.90  
UNION ALL  
SELECT 'DCOM Liters',4909.90  
UNION ALL  
SELECT 'Liters %',1003.432  
UNION ALL  
SELECT 'FR Income $',5022  
UNION ALL  
SELECT 'OB Income $',30993  
UNION ALL  
SELECT 'DCOM Income $',9043  
UNION ALL  
SELECT 'Total Income $',7021.43  
UNION ALL  
SELECT 'Cost $',6032.90  
UNION ALL  
SELECT 'Credits $',23092.09  
UNION ALL  
SELECT 'Margin $',4309.892  
UNION ALL  
SELECT 'Margin %',432.932  
  
  
INSERT INTO #tblRoute(RouteCode,RouteName)  
  
SELECT 'All Run' ,'All Run'  
UNION ALL  
SELECT 'Dual Run','Dual Run'  
UNION ALL  
SELECT 'DanSatrun','DanSatrun'  
UNION ALL  
SELECT 'DunWeek','DunWeek'  
  
INSERT INTO #tblProductCategory(ProductCategoryCode,ProductCategoryName)  
  
SELECT 'Butter and Spreads' ,'Butter and Spreads'  
UNION ALL  
SELECT 'Cheese','Cheese'  
UNION ALL  
SELECT 'Cream - Anchor','Cream - Anchor'  
UNION ALL  
SELECT 'Cultured','Cultured'  
UNION ALL  
SELECT 'Flavoured Milk','Flavoured Milk'  
UNION ALL  
SELECT 'Fonterra Milk for Schools','Fonterra Milk for Schools'  
UNION ALL  
SELECT 'Specialty Cheese','Specialty Cheese'  
  
SELECT DISTINCt * FROm #tblColumn  
SELECT DISTINCT * from #tblRoute WHERE (RouteCode=@Route Or ISNULL(@Route,'')='All' or ISNULL(@Route,'')='')  
SELECT DISTINCT * from #tblRoute R INNER JOIN #tblProductCategory PC ON 1=1  
WHERE  (R.RouteCode=@Route Or ISNULL(@Route,'')='All' or ISNULL(@Route,'')='')  
SELECT DISTINCT * from #tblRoute R INNER JOIN #tblProductCategory PC ON 1=1   
AND  (R.RouteCode=@Route Or ISNULL(@Route,'')='All' or ISNULL(@Route,'')='')  
INNER JOIn #tblColumn C ON 1=1  
WHERE (R.RouteCode=@Route Or ISNULL(@Route,'')='All' or ISNULL(@Route,'')='')  
  
  
DROP TABLE #tblRoute  
DROP TABLE #tblProductCategory  
DROP TABLE #tblColumn  
  
END

GO

CREATE Proc SP_TotalUnitSold         
--Declare          
@StartDate DATE='Dec 3, 2018',          
@EndDate DATE='Dec 15, 2018',          
@UserCode VARCHAR(MAX)='admin',          
@SearchString varchar(100)='1=1'          
AS   
BEGIN  
Create table #Result(Date Datetime,UnitSoldTY Numeric(18,5),UnitSoldLY Numeric(18,5),)   
  
INSERT INTO   #Result(Date, UnitSoldTY,   UnitSoldLY)  
SELECT CONVERT (DATETIME,'2020-05-12' ),123.04,1423.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-13'),1255.04,14373.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-14'),1220.04,3219.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-15'),103.04,123.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-16'),12.04,13.89  
  
          
Select UnitSoldTY Value,UnitSoldLY Value2,Date [key] from #Result         
          
Drop table #Result   
  
END

GO

Create Proc  SP_TotalLiterSold         
--Declare          
@StartDate DATE='Dec 3, 2018',          
@EndDate DATE='Dec 15, 2018',          
@UserCode VARCHAR(MAX)='admin',          
@SearchString varchar(100)='1=1'          
AS   
BEGIN  
Create table #Result(Date Datetime,LitersTY Numeric(18,5),LitersLY Numeric(18,5),)   
  
INSERT INTO   #Result(Date, LitersTY,   LitersLY)  
SELECT CONVERT (DATETIME,'2020-05-12' ),12300.04,1423.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-13'),1215.04,1473.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-14'),1290.04,319.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-15'),1034.04,123.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-16'),1211.04,1433.89  
  
          
Select LitersLY Value,LitersTY Value2,Date [key] from #Result         
          
Drop table #Result   
  
END         

GO

Create Proc  SP_TotalOBCommission         
--Declare          
@StartDate DATE='Dec 3, 2018',          
@EndDate DATE='Dec 15, 2018',          
@UserCode VARCHAR(MAX)='admin',          
@SearchString varchar(100)='1=1'          
AS   
BEGIN  
Create table #Result(Date Datetime,OBCommissionTY Numeric(18,5),OBCommissionLY Numeric(18,5),)   
  
INSERT INTO   #Result(Date, OBCommissionTY,   OBCommissionLY)  
SELECT CONVERT (DATETIME,'2020-05-12' ),12300.04,1423.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-13'),1215.04,1473.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-14'),1290.04,319.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-15'),1034.04,123.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-16'),1211.04,1433.89  
  
          
Select OBCommissionTY Value,OBCommissionLY Value2,Date [key] from #Result         
          
Drop table #Result   
  
END         

GO
Create Proc  SP_TotalFRSales         
--Declare          
@StartDate DATE='Dec 3, 2018',          
@EndDate DATE='Dec 15, 2018',          
@UserCode VARCHAR(MAX)='admin',          
@SearchString varchar(100)='1=1'          
AS   
BEGIN  
Create table #Result(Date Datetime,FRSalesTY Numeric(18,5),FRSalesLY Numeric(18,5),)   
  
INSERT INTO   #Result(Date, FRSalesTY,   FRSalesLY)  
SELECT CONVERT (DATETIME,'2020-05-12' ),12300.04,1423.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-13'),1215.04,1473.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-14'),1290.04,319.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-15'),1034.04,123.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-16'),1211.04,1433.89  
  
          
Select FRSalesTY Value,FRSalesLY Value2,Date [key] from #Result         
          
Drop table #Result   
  
END         



Go

Create Proc  SP_TotalInCome         
--Declare          
@StartDate DATE='Dec 3, 2018',          
@EndDate DATE='Dec 15, 2018',          
@UserCode VARCHAR(MAX)='admin',          
@SearchString varchar(100)='1=1'          
AS   
BEGIN  
Create table #Result(Date Datetime,InComeTY Numeric(18,5),InComeLY Numeric(18,5),)   
  
INSERT INTO   #Result(Date, InComeTY,   InComeLY)  
SELECT CONVERT (DATETIME,'2020-05-12' ),12300.04,1423.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-13'),1215.04,1473.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-14'),1290.04,319.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-15'),1034.04,123.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-16'),1211.04,1433.89  
  
          
Select InComeTY Value,InComeLY Value2,Date [key] from #Result         
          
Drop table #Result   
  
END         

GO

Create Proc  SP_DcomIncome         
--Declare          
@StartDate DATE='Dec 3, 2018',          
@EndDate DATE='Dec 15, 2018',          
@UserCode VARCHAR(MAX)='admin',          
@SearchString varchar(100)='1=1'          
AS   
BEGIN  
Create table #Result(Date Datetime,DCOMComeTY Numeric(18,5),DCOMInComeLY Numeric(18,5),)   
  
INSERT INTO   #Result(Date, DCOMComeTY,   DCOMInComeLY)  
SELECT CONVERT (DATETIME,'2020-05-12' ),12300.04,1423.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-13'),1215.04,1473.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-14'),1290.04,319.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-15'),1034.04,123.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-16'),1211.04,1433.89  
  
          
Select DCOMComeTY Value,DCOMInComeLY Value2,Date [key] from #Result         
          
Drop table #Result   
  
END         

GO

Create Proc  SP_FRVSOBUnits         
--Declare          
@StartDate DATE='Dec 3, 2018',          
@EndDate DATE='Dec 15, 2018',          
@UserCode VARCHAR(MAX)='admin',          
@SearchString varchar(100)='1=1'          
AS   
BEGIN  
Create table #Result(Date Datetime,FRUnitsTY Numeric(18,5),FRUnitsLY Numeric(18,5),OBUnitsTY Numeric(18,5),OBUnitsLY Numeric(18,5))   
  
INSERT INTO   #Result(Date, FRUnitsTY,   FRUnitsLY,OBUnitsTY,OBUnitsLY)  
SELECT CONVERT (DATETIME,'2020-05-12' ),12300.04,1423.89,4002.21,4510.90  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-13'),1215.04,1473.89,421.21,4010.90  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-14'),1290.04,319.89,412.21,510.90  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-15'),1034.04,123.89,402.21,4510.90  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-16'),1211.04,1433.89,4002.21,450.90  
  
          
Select FRUnitsTY Value,FRUnitsLY Value2,OBUnitsTY Value3,OBUnitsLY Value4 ,Date [key] from #Result         
          
Drop table #Result   
  
END         

Go
Create Proc  SP_FRVSOBLiters         
--Declare          
@StartDate DATE='Dec 3, 2018',          
@EndDate DATE='Dec 15, 2018',          
@UserCode VARCHAR(MAX)='admin',          
@SearchString varchar(100)='1=1'          
AS   
BEGIN  
Create table #Result(Date Datetime,FRLitersTY Numeric(18,5),FRLitersLY Numeric(18,5),OBLitersTY Numeric(18,5),OBLitersLY Numeric(18,5))   
  
INSERT INTO   #Result(Date, FRLitersTY,   FRLitersLY,OBLitersTY,OBLitersLY)  
SELECT CONVERT (DATETIME,'2020-05-12' ),12300.04,1423.89,4002.21,4510.90  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-13'),1215.04,1473.89,421.21,4010.90  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-14'),1290.04,319.89,412.21,510.90  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-15'),1034.04,123.89,402.21,4510.90  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-16'),1211.04,1433.89,4002.21,450.90  
  
          
Select FRLitersTY Value,FRLitersLY Value2,OBLitersTY Value3,OBLitersLY Value4 ,Date [key] from #Result         
          
Drop table #Result   
  
END   

GO

Create Proc  SP_DCOMUnitsAndLiters         
--Declare          
@StartDate DATE='Dec 3, 2018',          
@EndDate DATE='Dec 15, 2018',          
@UserCode VARCHAR(MAX)='admin',          
@SearchString varchar(100)='1=1'          
AS   
BEGIN  
Create table #Result(Date Datetime,DCOMUnitsTY Numeric(18,5),DCOMUnitsLY Numeric(18,5),DCOMLitersTY Numeric(18,5),DCOMLitersLY Numeric(18,5))   
  
INSERT INTO   #Result(Date, DCOMUnitsTY,   DCOMUnitsLY,DCOMLitersTY,DCOMLitersLY)  
SELECT CONVERT (DATETIME,'2020-05-12' ),12300.04,1423.89,4002.21,4510.90  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-13'),1215.04,1473.89,421.21,4010.90  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-14'),1290.04,319.89,412.21,510.90  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-15'),1034.04,123.89,402.21,4510.90  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-16'),1211.04,1433.89,4002.21,450.90  
  
          
Select DCOMUnitsTY Value,DCOMUnitsLY Value2,DCOMLitersTY Value3,DCOMLitersLY Value4 ,Date [key] from #Result         
          
Drop table #Result   
  
END   

GO

Create Proc  SP_FRMargin         
--Declare          
@StartDate DATE='Dec 3, 2018',          
@EndDate DATE='Dec 15, 2018',          
@UserCode VARCHAR(MAX)='admin',          
@SearchString varchar(100)='1=1'          
AS   
BEGIN  
Create table #Result(Date Datetime,MarginTY Numeric(18,5),MarginLY Numeric(18,5))   
  
INSERT INTO   #Result(Date, MarginTY,   MarginLY)  
SELECT CONVERT (DATETIME,'2020-05-12' ),12300.04,1423.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-13'),1215.04,1473.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-14'),1290.04,319.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-15'),1034.04,123.89  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-16'),1211.04,1433.89  
  
          
Select MarginTY Value,MarginLY Value2 ,Date [key] from #Result         
          
Drop table #Result   
  
END         

GO

Create Proc  SP_MarginDollarVsPercentage         
--Declare          
@StartDate DATE='Dec 3, 2018',          
@EndDate DATE='Dec 15, 2018',          
@UserCode VARCHAR(MAX)='admin',          
@SearchString varchar(100)='1=1'          
AS   
BEGIN  
Create table #Result(Date Datetime,MarginTY Numeric(18,5),MarginLY Numeric(18,5),MarginPerTY int,MarginPerLY int,)   
  
INSERT INTO   #Result(Date, MarginTY,   MarginLY,MarginPerTY,MarginPerLY)  
SELECT CONVERT (DATETIME,'2020-05-12' ),12300.04,1423.89,45,20  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-13'),1215.04,1473.89,4,15  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-14'),1290.04,319.89,28,72  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-15'),1034.04,123.89,45,39  
UNION ALL  
SELECT CONVERT (DATETIME,'2020-05-16'),1211.04,1433.89,61,56  
  
          
Select MarginTY Value,MarginLY Value2 ,MarginPerTY Value3,MarginPerLY Value4,Date [key] from #Result         
          
Drop table #Result   
  
END