Cost Per Click Quick Report


  select Sales_Doc_Type,
  Doc_Date,
  Customer_Name,
  Customer_Num,
  Sales_Doc_Num
         Sales_Doc_Num,
         xCampaignMedium,
         Subtotal,
         Prev_Sales_Doc_Num,
         Gross_Margin = (Total-(select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type))/(Total+.001),
         Profit = Total - (select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type),
         Cost = (select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type),
         Total,
         Sales_Person_ID,
         Source
  
  
   from spv3SalesDocument as sd
      WHERE xCampaignMedium = 'CPC' and (Sales_Doc_Type = 'QUOTE' or Sales_Doc_Type = 'ORDER')
   
   UNION 
   
     select Sales_Doc_Type,
  Doc_Date,
  Customer_Name,
  Customer_Num,
  Sales_Doc_Num
         Sales_Doc_Num,
         xCampaignMedium,
         Subtotal,
         Prev_Sales_Doc_Num,
               Gross_Margin = (Total-(select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type))/(Total+.001),
         Profit = Total - (select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type),
         Cost = (select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type),
         Total,
         Sales_Person_ID,
         Source
  
  
   from spv3SalesDocumentHistory as sd
   
   
   WHERE xCampaignMedium = 'CPC'  and Sales_Doc_Type = 'ORDER'
   
   

Leave a Reply