SQL summing with difference

The SQL Server query I have is:

SELECT
  ep.employeeID, ep.punchdate, rc.creditAmount
FROM
  EmployeePunch ep 
    INNER JOIN
      ResponderCredit rc ON rc.employeeID = ep.employeeID AND 
      rc.punchdate = rc.creditdate
ORDER BY ep.employeeID

and get a result set:

EmployeeID      Date             CreditAmount
-----------    -------          -------------- 
  5             01/01/2007              5
  5             03/01/2007              7
  5             04/22/2007              15
  6             01/01/2007              3
  6             01/12/2007              4

And I want to group by EmployeeID and Credit amount. The catch is that I need the change in the Credit Amount between the max and min date so:

EmployeeID          CreditAmount
-----------         --------------
  5                          10
  6                          1

How can I do this?

From stackoverflow

• for MS SQL, this should do it

  SELECT
    ep.employeeID, rc.creditAmount, 
    datediff(month, max(ep.punchdate), min(ep.punchdate))
  FROM
    EmployeePunch ep 
  INNER JOIN
    ResponderCredit rc ON rc.employeeID = ep.employeeID AND 
      rc.punchdate = rc.creditdate 
  group by
    EmployeeID, Credit
  ORDER BY
    ep.employeeID
  

  Scott W : I think the OP is actually looking for the difference between the min rc.creditAmount and the max rc.creditAmount.

  Cerebrus : Yes, I agree. That's almost certainly a typo on the part of the OP.

  Cade Roux : Edited the question

• In Oracle:

  SELECT  r1.employeeID, amMax - amMin
  FROM    (
      SELECT employeeID, amMin
      FROM  (
       SELECT
        ROW_NUMBER() OVER (PARTITION BY ep.EmployeeID ORDER BY rc.creditdate) AS rnMin,
        ep.employeeID, rc.creditAmount AS amMin
       FROM
        EmployeePunch ep, ResponderCredit rc
       WHERE
        rc.employeeID = ep.employeeID
        AND rc.punchdate = rc.creditdate
       )
      WHERE rn_min = 1
      ) r1,
      (
      SELECT employeeID, amMin
      FROM  (
       SELECT
        ROW_NUMBER() OVER (PARTITION BY ep.EmployeeID ORDER BY rc.creditdate DESC) AS rnMax,
        ep.employeeID, rc.creditAmount AS amMax
       FROM
        EmployeePunch ep, ResponderCredit rc
       WHERE
        rc.employeeID = ep.employeeID
        AND rc.punchdate = rc.creditdate
       )
      WHERE rn_max = 1
      ) r2
  WHERE   r1.employeeID = r2.employeeID
  

  casperOne : Great use of ROW_NUMBER.

• Here is a solution for MSSQL.

  It will show a negative value if the credit has gone down over the time period.

  It's got a few inner joins and can probably be optimized, but probably not by much

  SELECT maxCredit.employeeID, maxCredit.creditAmount - minCredit.creditAmount
  FROM
  (
  SELECT
      ep.employeeID, rc.creditAmount
  FROM
      EmployeePunch ep 
  INNER JOIN
      ResponderCredit rc
  ON rc.employeeID = ep.employeeID AND rc.punchdate = rc.creditdate
  INNER JOIN
      (
      SELECT ep2.employeeID, MAX(ep2.punchdate) AS maxCreditdate
      FROM EmployeePunch ep2 GROUP BY ep2.employeeID
      ) innermax 
  ON innermax.employeeID = ep.employeeID AND ep.punchdate = innermax.maxcreditDate
  ) maxCredit
  INNER JOIN
  (
  SELECT
      ep.employeeID, rc.creditAmount
  FROM
      EmployeePunch ep 
  INNER JOIN
      ResponderCredit rc
  ON rc.employeeID = ep.employeeID AND rc.punchdate = rc.creditdate
  INNER JOIN
      (
      SELECT ep3.employeeID, MIN(ep3.punchdate) AS minCreditdate
      FROM EmployeePunch ep3 GROUP BY ep3.employeeID
      ) innermin
  ON innermin.employeeID = ep.employeeID AND ep.punchdate = innermin.mincreditDate
  ) minCredit
  ON maxCredit.employeeID = minCredit.employeeID
  

  Eppz : You forgot the date...

  John : I was just doing it as per the result required

  John : The result required is confusing as the min and max credit amounts for each employee are also the min and max dates. I've got it right this time though.

• This is what you're looking for I believe....

  SELECT
    ep.employeeID, Max(datediff(day, ep.punchdate, rc.creditDate)) As [# of Days], rc.creditAmount
  FROM
    EmployeePunch ep 
  INNER JOIN
    ResponderCredit rc 
  ON 
      rc.employeeID = ep.employeeID AND rc.punchdate = rc.creditdate
  group by ep.employeeID, rc.creditAmount
  ORDER BY ep.employeeID
  

  Will yield results:

  EmployeeID    # of Days    CreditAmount    
  5                 0             5    
  5                 2             7    
  5                 111           15    
  6                 0             3    
  6                 334           4
  

• select
      ep.employeeid, 
      sum
      (
          case 
          when ep.punchdate = x.mindate then -rc.creditamount 
          when ep.punchdate = x.maxdate then rc.creditamount 
          end
      ) as credit_amount
  
  from 
  employeepunch ep 
  join 
  (
      select employeeid, min(punchdate) as mindate, max(punchdate) as maxdate
      from employeepunch 
      group by employeeid
  ) as x 
  on ep.employeeid = x.employeeid 
  and (ep.punchdate = x.mindate or ep.punchdate = x.maxdate)
  
  join respondercredit rc on rc.employeeid = ep.employeeid 
  and rc.punchdate = rc.creditdate
  group by ep.employeeid