ecc/InventoryKeyMetrics.sql [88:196]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
      IF(
        StockMonthlySnapshots.MonthEndDate = LAST_DAY(CURRENT_DATE),
        CURRENT_DATE,
        StockMonthlySnapshots.MonthEndDate
      ) AS MonthEndDate,
      StockMonthlySnapshots.MonthEndDate
        BETWEEN -- noqa: disable=L003
          LAST_DAY(DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH))
        AND
          LAST_DAY(DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH))
        AS IsPast12MonthsExcludingCurrent,
      StockMonthlySnapshots.MonthEndDate
        BETWEEN
          LAST_DAY(DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH))
        AND
          LAST_DAY(CURRENT_DATE)
        AS IsPast12MonthsIncludingCurrent,
      ValueAndCost.ValueOfTotalValuatedStock_SALK3, --noqa: enable=all
      ValueAndCost.StandardCost_STPRS,
      ValueAndCost.MovingAveragePrice_VERPR,
      SUM(StockMonthlySnapshots.QuantityMonthlyCumulative) AS QuantityMonthlyCumulative,
      SUM(StockMonthlySnapshots.AmountMonthlyCumulative) AS AmountMonthlyCumulative,
      SUM(StockMonthlySnapshots.StockOnHand) AS StockOnHand,
      SUM(StockMonthlySnapshots.StockOnHandValue) AS StockOnHandValue,
      SUM(StockMonthlySnapshots.QuantityIssuedToDelivery) AS QuantityIssuedToDelivery,
      SUM(StockMonthlySnapshots.TotalConsumptionQuantity) AS TotalConsumptionQuantity
    FROM
      -- TODO: Evaluate if all columns in StockMonthlySnapshots can be moved into ValueAndCost
      -- so we don't need to query the source table twice.
      `{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.StockMonthlySnapshots` AS StockMonthlySnapshots
    LEFT JOIN
      ValueAndCost
      ON
        StockMonthlySnapshots.Client_MANDT = ValueAndCost.Client_MANDT
        AND StockMonthlySnapshots.MaterialNumber_MATNR = ValueAndCost.MaterialNumber_MATNR
        AND StockMonthlySnapshots.Plant_WERKS = ValueAndCost.Plant_WERKS
        AND StockMonthlySnapshots.MonthEndDate = ValueAndCost.MonthEndDate
        AND StockMonthlySnapshots.FiscalPeriod = ValueAndCost.FiscalPeriod
        AND StockMonthlySnapshots.FiscalYear = ValueAndCost.FiscalYear
    LEFT JOIN
      `{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.SlowMovingThreshold` AS SlowMovingThreshold
      ON
        StockMonthlySnapshots.Client_MANDT = SlowMovingThreshold.Client_MANDT
        AND StockMonthlySnapshots.MaterialType_MTART = SlowMovingThreshold.MaterialType_MTART
    GROUP BY
      StockMonthlySnapshots.Client_MANDT,
      StockMonthlySnapshots.MaterialNumber_MATNR,
      StockMonthlySnapshots.Plant_WERKS,
      StockMonthlySnapshots.MaterialType_MTART,
      SlowMovingThreshold.ThresholdValue,
      StockMonthlySnapshots.DescriptionOfMaterialType_MTBEZ,
      StockMonthlySnapshots.MaterialText_MAKTX,
      StockMonthlySnapshots.MaterialGroup_MATKL,
      StockMonthlySnapshots.MaterialGroupName_WGBEZ,
      StockMonthlySnapshots.Plant_Name2_NAME2,
      StockMonthlySnapshots.CompanyCode_BUKRS,
      StockMonthlySnapshots.CompanyText_BUTXT,
      StockMonthlySnapshots.CountryKey_LAND1,
      StockMonthlySnapshots.LanguageKey_SPRAS,
      ValueAndCost.ValueOfTotalValuatedStock_SALK3,
      ValueAndCost.StandardCost_STPRS,
      ValueAndCost.MovingAveragePrice_VERPR,
      StockMonthlySnapshots.BaseUnitOfMeasure_MEINS,
      StockMonthlySnapshots.CurrencyKey_WAERS,
      StockMonthlySnapshots.FiscalYear,
      StockMonthlySnapshots.FiscalPeriod,
      StockMonthlySnapshots.CalYear,
      StockMonthlySnapshots.CalMonth,
      StockMonthlySnapshots.MonthEndDate
  ),
  CalculatedMetrics AS (
    SELECT DISTINCT
      CurrentStock.Client_MANDT,
      CurrentStock.MaterialNumber_MATNR,
      CurrentStock.Plant_WERKS,
      CurrentStock.LanguageKey_SPRAS,
      CurrentStock.MonthEndDate,
      CurrentStock.FiscalPeriod,
      CurrentStock.FiscalYear,
      -- TotalConsumptionQuantity for past 12 months excluding current month
      IF(
        IsPast12MonthsExcludingCurrent,
        SUM(CurrentStock.TotalConsumptionQuantity)
          OVER ( -- noqa: disable=L003
            PARTITION BY
              CurrentStock.Client_MANDT, CurrentStock.MaterialNumber_MATNR,
              CurrentStock.Plant_WERKS, CurrentStock.LanguageKey_SPRAS,
              IsPast12MonthsExcludingCurrent),
        0) AS TotalConsumptionQuantityForPastYear,

      -- TotalConsumptionQuantity For Past 12 Months To Current Date
      IF(
        IsPast12MonthsIncludingCurrent,
        SUM(CurrentStock.TotalConsumptionQuantity)
          OVER (
            PARTITION BY
              CurrentStock.Client_MANDT, CurrentStock.MaterialNumber_MATNR,
              CurrentStock.Plant_WERKS, CurrentStock.LanguageKey_SPRAS,
              IsPast12MonthsIncludingCurrent),
        0) AS TotalConsumptionQuantityForPastYearTillToday,

      --  Demand Per Day For Past 12 Months To Current Date
      IF(
        IsPast12MonthsIncludingCurrent,
        SUM(TotalConsumptionQuantity / (365 + EXTRACT(DAY FROM CURRENT_DATE)))
          OVER (
            PARTITION BY
              CurrentStock.Client_MANDT, CurrentStock.MaterialNumber_MATNR,
              CurrentStock.Plant_WERKS, CurrentStock.LanguageKey_SPRAS,
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



s4/InventoryKeyMetrics.sql [85:194]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
      IF(
        StockMonthlySnapshots.MonthEndDate = LAST_DAY(CURRENT_DATE),
        CURRENT_DATE,
        StockMonthlySnapshots.MonthEndDate
      ) AS MonthEndDate,
      StockMonthlySnapshots.MonthEndDate
        BETWEEN -- noqa: disable=L003
          LAST_DAY(DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH))
        AND
          LAST_DAY(DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH))
        AS IsPast12MonthsExcludingCurrent,
      StockMonthlySnapshots.MonthEndDate
        BETWEEN
          LAST_DAY(DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH))
        AND
          LAST_DAY(CURRENT_DATE)
        AS IsPast12MonthsIncludingCurrent,
      ValueAndCost.ValueOfTotalValuatedStock_SALK3, --noqa: enable=all
      ValueAndCost.StandardCost_STPRS,
      ValueAndCost.MovingAveragePrice_VERPR,
      SUM(StockMonthlySnapshots.QuantityMonthlyCumulative) AS QuantityMonthlyCumulative,
      SUM(StockMonthlySnapshots.AmountMonthlyCumulative) AS AmountMonthlyCumulative,
      SUM(StockMonthlySnapshots.StockOnHand) AS StockOnHand,
      SUM(StockMonthlySnapshots.StockOnHandValue) AS StockOnHandValue,
      SUM(StockMonthlySnapshots.QuantityIssuedToDelivery) AS QuantityIssuedToDelivery,
      SUM(StockMonthlySnapshots.TotalConsumptionQuantity) AS TotalConsumptionQuantity
    FROM
      -- TODO: Evaluate if all columns in StockMonthlySnapshots can be moved into ValueAndCost
      -- so we don't need to query the source table twice.
      `{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.StockMonthlySnapshots` AS StockMonthlySnapshots
    LEFT JOIN
      ValueAndCost
      ON
        StockMonthlySnapshots.Client_MANDT = ValueAndCost.Client_MANDT
        AND StockMonthlySnapshots.MaterialNumber_MATNR = ValueAndCost.MaterialNumber_MATNR
        AND StockMonthlySnapshots.Plant_WERKS = ValueAndCost.Plant_WERKS
        AND StockMonthlySnapshots.MonthEndDate = ValueAndCost.MonthEndDate
        AND StockMonthlySnapshots.FiscalPeriod = ValueAndCost.FiscalPeriod
        AND StockMonthlySnapshots.FiscalYear = ValueAndCost.FiscalYear
    LEFT JOIN
      `{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.SlowMovingThreshold` AS SlowMovingThreshold
      ON
        StockMonthlySnapshots.Client_MANDT = SlowMovingThreshold.Client_MANDT
        AND StockMonthlySnapshots.MaterialType_MTART = SlowMovingThreshold.MaterialType_MTART
    GROUP BY
      StockMonthlySnapshots.Client_MANDT,
      StockMonthlySnapshots.MaterialNumber_MATNR,
      StockMonthlySnapshots.Plant_WERKS,
      StockMonthlySnapshots.MaterialType_MTART,
      SlowMovingThreshold.ThresholdValue,
      StockMonthlySnapshots.DescriptionOfMaterialType_MTBEZ,
      StockMonthlySnapshots.MaterialText_MAKTX,
      StockMonthlySnapshots.MaterialGroup_MATKL,
      StockMonthlySnapshots.MaterialGroupName_WGBEZ,
      StockMonthlySnapshots.Plant_Name2_NAME2,
      StockMonthlySnapshots.CompanyCode_BUKRS,
      StockMonthlySnapshots.CompanyText_BUTXT,
      StockMonthlySnapshots.CountryKey_LAND1,
      StockMonthlySnapshots.LanguageKey_SPRAS,
      ValueAndCost.ValueOfTotalValuatedStock_SALK3,
      ValueAndCost.StandardCost_STPRS,
      ValueAndCost.MovingAveragePrice_VERPR,
      StockMonthlySnapshots.BaseUnitOfMeasure_MEINS,
      StockMonthlySnapshots.CurrencyKey_WAERS,
      StockMonthlySnapshots.FiscalYear,
      StockMonthlySnapshots.FiscalPeriod,
      StockMonthlySnapshots.CalYear,
      StockMonthlySnapshots.CalMonth,
      StockMonthlySnapshots.MonthEndDate
  ),
  CalculatedMetrics AS (
    SELECT DISTINCT
      CurrentStock.Client_MANDT,
      CurrentStock.MaterialNumber_MATNR,
      CurrentStock.Plant_WERKS,
      CurrentStock.LanguageKey_SPRAS,
      CurrentStock.MonthEndDate,
      CurrentStock.FiscalPeriod,
      CurrentStock.FiscalYear,

      -- TotalConsumptionQuantity for past 12 months excluding current month
      IF(
        IsPast12MonthsExcludingCurrent,
        SUM(CurrentStock.TotalConsumptionQuantity)
          OVER ( -- noqa: disable=L003
            PARTITION BY
              CurrentStock.Client_MANDT, CurrentStock.MaterialNumber_MATNR,
              CurrentStock.Plant_WERKS, CurrentStock.LanguageKey_SPRAS,
              IsPast12MonthsExcludingCurrent),
        0) AS TotalConsumptionQuantityForPastYear,

      -- TotalConsumptionQuantity For Past 12 Months To Current Date
      IF(
        IsPast12MonthsIncludingCurrent,
        SUM(CurrentStock.TotalConsumptionQuantity)
          OVER (
            PARTITION BY
              CurrentStock.Client_MANDT, CurrentStock.MaterialNumber_MATNR,
              CurrentStock.Plant_WERKS, CurrentStock.LanguageKey_SPRAS,
              IsPast12MonthsIncludingCurrent),
        0) AS TotalConsumptionQuantityForPastYearTillToday,

      --  Demand Per Day For Past 12 Months To Current Date
      IF(
        IsPast12MonthsIncludingCurrent,
        SUM(TotalConsumptionQuantity / (365 + EXTRACT(DAY FROM CURRENT_DATE)))
          OVER (
            PARTITION BY
              CurrentStock.Client_MANDT, CurrentStock.MaterialNumber_MATNR,
              CurrentStock.Plant_WERKS, CurrentStock.LanguageKey_SPRAS,
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



