How to sum on 3 levels / on 1 level in SQL

• JeremyU

  SSChasing Mays

  Points: 640

  More actions

  March 9, 2022 at 12:54 am

  #3996017

  I am asking and I am not a 100% sure it is achievable but I will appreciate if someone can help.

  I have such a situation I need to calculate this FCST ACCURACY and the formula is

  1-((ABS(FCST-HIST)) / HIST)

  The part ABS(FCST-HIST) should be on DMDUNIT, LOC and STARTDATE level and after that division should be on 1 line level (DMDUNIT).

  My current code calculates everything on one line level but it should be

  The Forecast Accuracy % should always be 1- (sum of all absolute error at the item company chain week level / total actual).

  I am not a 100% sure if it is achievable but maybe there is a way to add for example a subquery that will calculate ABS(SUM(FCST) - SUM(HIST)) on ON f.[DMDUNIT]=a.[DMDUNIT] AND f.[STARTDATE]=a.[DMDPostDate] AND f.[LOC]=a.[LOC] and this subquery will return the sum of ABS(SUM(FCST) - SUM(HIST)) as a separate column with the same value for all DMDUNITS of the same STARTDATE and LOC.

   

  SELECT f.[DMDUNIT]
  ,f.[LOC]
  ,f.[STARTDATE]
  ,getdate() as 'DATE'
  ,a.[HistoryQuantity] AS 'Total History'
  ,f.[TOTFCST] AS 'Total Forecast'
  ,CAST(1 - (ABS( f.[TOTFCST] - a.[HistoryQuantity]) / a.[HistoryQuantity] ) as DECIMAL (18,2)) as "FA%"
  ,CAST(((f.TOTFCST) / (a.HistoryQuantity)) - 1 as DECIMAL (18,2)) AS "Bias"
    FROM [BYIntegration].[SCPOMGR].[FCSTPERFSTATIC] f
    LEFT OUTER JOIN [BYIntegration].[SCPOMGR].[HISTWIDE_CHAIN] a
    ON f.[DMDUNIT]=a.[DMDUNIT] AND f.[STARTDATE]=a.[DMDPostDate] AND f.[LOC]=a.[LOC]
    WHERE (1 - (ABS( f.[TOTFCST] - a.[HistoryQuantity]))) / a.[HistoryQuantity] < 25
    AND f.[STARTDATE] BETWEEN @Last2WeekDATE AND @LWDATE
    and a.[TYPE]='1'
    order by 7 desc

   

  Basically like a sum of Abs Error that we can usually get in Excel by selecting everything

  Abs Error is ABS( f.[TOTFCST] - a.[HistoryQuantity])

   

  

   

  

• Eddie Wuerch

  SSChampion

  Points: 12540

  More actions

  March 9, 2022 at 3:39 am

  #3996061

  You can use the OVER() clause with almost any aggregate function, not just windowed functions like ROW_NUMBER() and RANK().

  https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15

  That allows you to add in the column that SUM()s over a group of rows. Basically, PARTITION BY <column [,column]> is an in-line GROUP BY on the columns specified with PARTITION BY:

  SELECT f.[DMDUNIT]
  ,f.[LOC]
  ,f.[STARTDATE]
  ,getdate() as 'DATE'
  ,a.[HistoryQuantity] AS 'Total History'
  ,f.[TOTFCST] AS 'Total Forecast'
  ,CAST(1 - (ABS( f.[TOTFCST] - a.[HistoryQuantity]) / a.[HistoryQuantity] ) as DECIMAL (18,2)) as "FA%"
  ,CAST(((f.TOTFCST) / (a.HistoryQuantity)) - 1 as DECIMAL (18,2)) AS "Bias"
  
  -- add a column to the query to calculate the sum of (f.TOTFCST) / (a.HistoryQuantity)
  -- across all rows with matching values in the f.[DMDUNIT], f.[LOC], and f.[STARTDATE] columns:
  , SUM(ABS((f.TOTFCST) / (a.HistoryQuantity))) OVER (PARTITION BY f.[DMDUNIT], f.[LOC], f.[STARTDATE]) AS [FA %]
  
    FROM [BYIntegration].[SCPOMGR].[FCSTPERFSTATIC] f
    LEFT OUTER JOIN [BYIntegration].[SCPOMGR].[HISTWIDE_CHAIN] a
    ON f.[DMDUNIT]=a.[DMDUNIT] AND f.[STARTDATE]=a.[DMDPostDate] AND f.[LOC]=a.[LOC]
    WHERE (1 - (ABS( f.[TOTFCST] - a.[HistoryQuantity]))) / a.[HistoryQuantity] < 25
    AND f.[STARTDATE] BETWEEN @Last2WeekDATE AND @LWDATE
    and a.[TYPE]='1'
    order by 7 desc

   

   

  Eddie Wuerch
  MCM: SQL