title: Difference of MAX() and MIN() of a table returns Aggregate difference instead of unique difference over a selected period of time
body: <p>I want to monitor the expense of 3 students with their respective IDs every day for 15 days. I came up with this code, but it gives the total expense for 15 days, not the day by day expense.</p>
  <p>for example: if student <code>101</code> spends 10$, 15$, 32$ on days 1,2, and 3 respectively, it shows 57$ on all days as supposed to 10$ on day 1, 15$ on day 2, and 32$ on day 3.</p>
  <pre><code>SELECT
      Student_id
      , Expense
      , Cal_date
  FROM
      (
          SELECT
              MAX(Expenditure * 1.0) OVER (PARTITION BY Student_id) - MIN(Expenditure * 1.0) OVER (PARTITION BY Student_id) AS &quot;Expense&quot;
              , CAST(DATEADD(mi, 330, date_time) AS DATE)                   AS Cal_date
              , Student_id                                                  AS &quot;Student_id&quot;
          FROM
              Table1
          WHERE
              Student_id IN (101, 102, 103)
              AND DATEADD(mi, 330, date_time) &gt; DATEADD(DAY, -14, GETDATE())
      ) A
  GROUP BY
      Student_id
      , Expense
      , Cal_date
  ORDER BY
      3, 2, 1;
  </code></pre>