SQL summing with difference

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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