0

enter image description here

The Sales Accumulative column is wrong, I need the sales Accumulative to add the previous days deficit

my code: 

SELECT
    a.Sales_Date,
    a.Sales,
    b.Sales_Target,
    (b.Sales_Target - a.Sales) Sales_Deficit,
    CASE
        WHEN DATEPART(D, Sales_Date) = 1 THEN b.sales_target
        ELSE (b.Sales_Target + (b.Sales_Target - a.Sales))
    END AS Sales_Accumulative,
    a.Port,
    b.Port_target,
    (b.Port_Target - a.Port) Port_Deficit,
    CASE
        WHEN DATEPART(D, Sales_Date) = 1 THEN b.Port_Target
        ELSE (b.Port_Target + (b.Port_Target - a.Port))
    END AS Port_Accumulative,
    a.FNB_Hybrid,
    a.Postpaid,
    a.Prepaid
FROM #MTDSales_Summary a
LEFT JOIN Sales_Port_Target b
    ON a.Sales_Date = b.Date;
James Z
  • 12,209
  • 10
  • 24
  • 44
Andy chen
  • 1
  • This link is some what usefull --------- http://stackoverflow.com/questions/11619494/sql-subtract-two-columns – Chanukya Mar 11 '16 at 10:53
  • So you're just calculating a running total with the exception that deficit of current day is not added. Most likely you need to do a cross apply / select back to the same table since 2008 doesn't support sum + over. See examples from here: http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server – James Z Mar 12 '16 at 13:51

0 Answers0