Sales Report


 USE [MTHND]
GO

/****** Object:  StoredProcedure [dbo].[cp_SalesReport]    Script Date: 09/18/2015 15:36:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 
ALTER procedure [dbo].[cp_SalesReport]
--@sp char(15)=NULL,
--@Sales_Doc_Type char(30)=NULL,
@startdate datetime='11/01/2014',
@enddate datetime='1/1/2099'
 
as
DECLARE @spCostGrossMargin TABLE(
       Sales_Doc_Num char(25),
       Sales_Doc_Type char(25),
       Return_Cft numeric(19,5),
       Source char(25),
       Sales_Person_ID char(25),
       Doc_Date datetime,
       Customer_Num char(30),
       Customer_Name char(65),
          ADDRESS1 char (61),
          ADDRESS2 char (61),
          CITY char(35),
          STATE char(29),
          ZIPCODE char(11),
       Email char (131),
          Cost numeric(19,5),
       Total numeric(19,5),
       Profit numeric(19,5),
       Gross_Margin numeric(19,5),
          FreightOut numeric(19,5),
          Installers numeric(19,5),
          USETAX numeric(19,5) );
     
insert into @spCostGrossMargin
       (Sales_Doc_Num,
       Sales_Doc_Type,
       Source,
       Sales_Person_ID,
       Doc_Date,
       Customer_Num,
       Customer_Name,
          ADDRESS1,
          ADDRESS2,
          CITY,
          STATE,
          ZIPCODE,
       Email,
       Cost,
       Total,
       Profit,
       Gross_Margin,
          FreightOut,
          Installers,
          USETAX)
     
select
       sd.Sales_Doc_Num,
       Sales_Doc_Type,
       Source,
       Sales_Person_ID,
       Doc_Date,
       Customer_Num,
       Customer_Name,
          Address_Line_1,
          Address_Line_2,
          CITY,
          STATE,
          ZIP,
       Email,
     
       Cost = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
       Total=Total,
       Profit = Total - ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
       --Gross_Margin = ISNULL(((Total-(select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)-Tax)/(Total+.001)), 0),
       ISNULL(case Total
              when 0 then 0
              else (case (select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)
                       when 0 then 1
                       else (Total - (select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type))/(Total)
              end)
       end, 0) as GM,
          FreightOut = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'FREIGHT OUT'), 0),
          Installers = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'INSTALLERS'), 0),
          USETAX = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'USETAX'), 0)
from
       spv3SalesDocument as sd
where (Doc_Date between @startdate and @enddate)
 
insert into @spCostGrossMargin
       (Sales_Doc_Num,
       Sales_Doc_Type,
       Source,
       Sales_Person_ID,
       Doc_Date,
       Customer_Num,
       Customer_Name,
          Address1,
          Address2,
          CITY,
          STATE,
          ZIPCODE,
       Email,
       Cost,
       Total,
       Profit,
       Gross_Margin,
          FreightOut,
          Installers,
          USETAX)
     
select
       sd.Sales_Doc_Num,
       Sales_Doc_Type,
       Source,
       Sales_Person_ID,
       Doc_Date,
       Customer_Num,
       Customer_Name,
          Address_Line_1,
          Address_Line_2,
          CITY,
          STATE,
          ZIP,
       Email,
       Cost = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
       Total=Total,
       Profit = Total - ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
       --Gross_Margin = ISNULL(((Total-(select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)-Tax)/(Total+.001)), 0),
       ISNULL(case Total
              when 0 then 0
              else (case (select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)
                       when 0 then 1
                       else (Total -(select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type))/(Total)
              end)
       end, 0) as GM,
          FreightOut = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'FREIGHT OUT'), 0),
          Installers = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'INSTALLERS'), 0),
          USETAX = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'USETAX'), 0)
from
       spv3SalesDocumentHistory as sd
where (Doc_Date between @startdate and @enddate)
 
update @spCostGrossMargin set Return_Cft = 1 where Sales_Doc_Type <> 'RETURN'
update @spCostGrossMargin set Return_Cft = -1 where Sales_Doc_Type = 'RETURN'
 
select Sales_Doc_Num, Sales_Doc_Type, Source, Sales_Person_ID, Doc_Date, Customer_Num,  Customer_Name, ADDRESS1, ADDRESS2, STATE, ZIPCODE, Email, FreightOut, Installers, USETAX, "Total Price"=Total, "Total Cost"=Cost, Profit, Gross_Margin
from
(select Sales_Doc_Num, Sales_Doc_Type, Source, Sales_Person_ID, Doc_Date, Customer_Num, Customer_Name, ADDRESS1, ADDRESS2, STATE, ZIPCODE, Email,
Cost=Cost*Return_Cft,
Total=Total*Return_Cft,
Profit=Profit*Return_Cft,
Gross_Margin=Gross_Margin*Return_Cft,
FreightOut=FreightOut*Return_Cft,
Installers=Installers*Return_Cft,
USETAX=USETAX*Return_Cft
from @spCostGrossMargin) as a
 
 
SET ANSI_NULLS OFF

GO


Leave a Reply