CODEWITHSUNDEEP
sql
Mark Kadlec
Mark Kadlec

Reputation: 8460

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?

Upvotes: 1

Views: 238

Answers (4)

John
John

Reputation: 5834

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

Upvotes: 0

Eppz
Eppz

Reputation: 3226

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

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425673

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

Upvotes: 1

Rob
Rob

Reputation: 2110

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

Upvotes: 0

Related Questions