title: SQL SUM a CASE statement with an OVER clause
body: <p>I have the following SQL which works just fine:</p>
  <pre><code>-- Supply for each week
  SUM(CASE
      WHEN ReceiptORIssueIndicator = '+'
          THEN NetQuantity
      ELSE 0
  END) AS SupplyQuantity,
  </code></pre>
  <p>Basically, all receipts of materials are a '+' while consumption of materials are a '-'. This is why I need the case statement to sum only '+'s. The problem is when I try making this a running total. In this instance, each week the suppliers will commit to how much they will supply us for each week. It is a bit confusing but basically, each week they tell us what they are committing to supply for every remaining week of the year. but for this example, lets pretend SnapshotWeek is another group by column like MaterialNumber and the WeekSort column is the one we want to use for a running total.</p>
  <pre><code>-- Supply Running Total
  SUM(CASE
      WHEN ReceiptORIssueIndicator = '+'
          THEN SUM(NetQuantity)
      ELSE 0
  END) OVER (PARTITION BY SnapshotWeek, Plant, MaterialNumber ORDER BY Calendar.WeekSort) AS CumulativeSupplyQuantity,
  </code></pre>
  <p>The data is private otherwise I would provide a sample. I am hoping someone will be able to point out my issue just by looking at the code.</p>
  <p>Full Query provided below:</p>
  <pre><code>/*
      get mrpdata snapshot for each week of the current year.
  
      We add a few pre filters:
          Plant: all indianapolis manufacturing plants
          MRPc: exclude indy MAKE mrp codes(G*)
          ProcurementType: only buy parts
  */
  SELECT
      MRPDATA.*,
      Calendar.WeekSort AS SnapshotWeek
  INTO
      #weekly_mrpdata_snapshots
  FROM
      ValueStreamMetrics.MRPDATA
  INNER JOIN (
      SELECT
          metadata_id,
          extracted
      FROM
          ValueStreamMetrics.MRPDATA_metadata
      WHERE
          CAST(extracted AS DATE) IN (
              /* the earliest extracted date for each week */
              SELECT
                  MIN(CAST(extracted AS DATE))
              FROM ValueStreamMetrics.MRPDATA_metadata
              LEFT JOIN ValueStreamMetrics.Calendar ON
                  CAST(extracted AS DATE) = Calendar.[Date]
              WHERE
                  Calendar.WeekSort IN (
                      /* all weeks in the current year */
                      SELECT DISTINCT WeekSort
                      FROM ValueStreamMetrics.Calendar
                      WHERE DATEPART(YEAR, [Date]) = DATEPART(YEAR, GETDATE())
                  )
              GROUP BY WeekSort
          )
  ) AS mrpdata_metadata ON
      MRPDATA.metadata_id = mrpdata_metadata.metadata_id
  LEFT JOIN ValueStreamMetrics.Calendar ON
      CAST(extracted AS DATE) = Calendar.[Date]
  LEFT JOIN (
      SELECT DISTINCT Plant, MaterialNumber, ProcurementType
      FROM ValueStreamMetrics.GMATDATA
  ) AS gmat ON
      MRPDATA.Plant = gmat.Plant
      AND MRPDATA.MaterialNumber = gmat.MaterialNumber
  WHERE
      MRPDATA.Plant IN ('Plant_1', 'Plant_2', 'Plant_3', 'Plant_4', 'Plant_5')
      AND Mrpc NOT LIKE 'G__'
      AND gmat.ProcurementType = 'F';
  
  SELECT
      base_query.CommitWeek,
      base_query.SnapshotWeek,
      base_query.Plant,
      base_query.MaterialNumber,
      weekly_commits.SupplyQuantity,
      weekly_commits.DemandQuantity
  -- Build a list with all join columns(material, plant, etc) for each week of this year
  FROM (
      SELECT DISTINCT
          MaterialNumber,
          Plant,
          snapshot_week_list.WeekSort AS SnapshotWeek,
          commit_week_list.WeekSort AS CommitWeek
      FROM #weekly_mrpdata_snapshots
      CROSS JOIN (
          SELECT DISTINCT WeekSort
          FROM ValueStreamMetrics.Calendar
          WHERE DATEPART(YEAR, [Date]) = DATEPART(YEAR, GETDATE())
      ) AS snapshot_week_list
      -- yay, recursion
      CROSS JOIN (
          SELECT DISTINCT WeekSort
          FROM ValueStreamMetrics.Calendar
          WHERE DATEPART(YEAR, [Date]) = DATEPART(YEAR, GETDATE())
      ) AS commit_week_list
  ) AS base_query
  /*
      Join in Supply and Demand Commits
  */
  LEFT JOIN (
      SELECT
          Plant,
          MaterialNumber,
          -- Supply for each week
          SUM(CASE
              WHEN ReceiptORIssueIndicator = '+'
                  THEN NetQuantity
              ELSE 0
          END) AS SupplyQuantity,
          /*-- Supply Running Total
          SUM(CASE
              WHEN ReceiptORIssueIndicator = '+'
                  THEN SUM(NetQuantity)
              ELSE 0
          END) OVER (PARTITION BY SnapshotWeek, Plant, MaterialNumber, ReceiptORIssueIndicator ORDER BY Calendar.WeekSort) AS CumulativeSupplyQuantity,*/ 
          -- Demand for each week
          SUM(CASE
              WHEN ReceiptORIssueIndicator = '-'
                  THEN ABS(NetQuantity)
              ELSE 0
          END) AS DemandQuantity,
          Calendar.WeekSort AS CommitWeek,
          SnapshotWeek
      FROM
          #weekly_mrpdata_snapshots
      LEFT JOIN ValueStreamMetrics.Calendar ON
          ScheduledReceiptDate = Calendar.[Date]
      WHERE
          ReceiptORIssueIndicator IN ('+', '-')
          -- This makes sure the line doesn't try to start from before
              -- the snapshot was taken
          AND Calendar.WeekSort &gt;= SnapshotWeek
          AND ABS(NetQuantity) &gt; 0
      GROUP BY Plant, MaterialNumber, Calendar.WeekSort, SnapshotWeek
  ) AS weekly_commits ON
      base_query.SnapshotWeek = weekly_commits.SnapshotWeek
      AND base_query.CommitWeek = weekly_commits.CommitWeek
      AND base_query.MaterialNumber = weekly_commits.MaterialNumber
      AND base_query.Plant = weekly_commits.Plant
      -- make sure we actually pulled a value for something
  WHERE
      (weekly_commits.SupplyQuantity IS NOT NULL
      OR weekly_commits.DemandQuantity IS NOT NULL
      )
  ORDER BY
      Plant,
      MaterialNumber,
      SnapshotWeek,
      CommitWeek;
  </code></pre>