1800radiatoranalytics of 1-800 Radiator-SQL Codes
4/13/2017 - 6:32 PM

Sales Adjustment Tracking

Sales Adjustment Tracking

SELECT DISTINCT CONVERT(DATE,b.SOMDateTime) SO_Date 
       ,b.clientid CustomerID
       ,c.CompanyName CustomerName
       ,z.whse WarehouseID 
  
       ,b.SOMSo Orig_SO
       ,R1.SOMSOTARGET Adjustment_Credit_SO 
       ,R2.SOMSOTARGET Adjustment_SO
  
       ,b.SOMTotal Orig_SO_Total -- Original Total
       ,B1.SOMTotal Adjustment_Credit_Amount  
       ,B2.SOMTotal Adjustment_Amount
  
       ,b.SOMSalesman Orig_User
       ,B1.SOMSalesman Adjustment_Credit_User
       ,B2.SOMSalesman Adjustment_User
  
FROM gxwizmo_10.dbo.GXSOM b
       JOIN wizmo2005_10.dbo.customers C
              ON c.CustomerID = b.ClientId
       JOIN wizmo2005_10.dbo.zip_routes Z
              ON z.zip = c.Zip
       JOIN misc_10.dbo.LU_DATE D
              ON d.cal_DATE = CONVERT(DATE,b.SOMDateTime)
      
       -- Adjustment Credit
       LEFT JOIN gxWizmo_10.dbo.SOMRELATION R1
              ON R1.SOMSOSOURCE = b.somso
              AND R1.SOMRELATIONTYPE = 8 
              
       LEFT JOIN gxWizmo_10.dbo.GXSOM B1
			ON R1.SOMSOTARGET = B1.SOMso
 
  
       -- Adjustments
       LEFT JOIN gxWizmo_10.dbo.SOMRELATION R2
              ON r2.SOMSOSOURCE = b.somso
              AND R2.SOMRELATIONTYPE = 6 
              
       LEFT JOIN gxWizmo_10.dbo.GXSOM B2
			ON R2.SOMSOTARGET = B2.SOMso

       
WHERE z.whse = 3
	 AND ( r1.SOMSOSOURCE IS NOT NULL OR r2.SOMSOSOURCE IS NOT NULL ) -- Limit results to rows with adjustments 

      AND cal_date > '2016-05-01'


	-- and b.ClientId = 100478952