Summary Record 2

[MMund]

I need a way to summarize employee records into one totalled record by SSNo where any given employee may have more than one employee number. This is because they may have quit and been rehired during the year. Each employee number record has numeric field1 field2 field 3 field 4 and we have to create an output file where only one record is created with all the numeric fields added up in it. How do I do this in T-SQL?

TIA,
MMund

 

[philhege]

Post the table schema and some sample data.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[rdandrade]

Use Group By to summarize, and add up all of the numeric fields. Use ISNULL in case any of the numerics are NULL, it doesn't skew the results. Something like this.

SELECT SSN, SUM(ISNULL(field1, 0) + ISNULL(field2, 0) + ISNULL(field3, 0)) AS Total FROM Employees GROUP BY SSN

I don't know what the numeric fields are, but it doesn't sound normalized if they are all similar.

Ray D'Andrade
Bright Network - Financial Database Programmer

 

[MMund]

We want the Active record to be the survivng one and all amounts totalled in that record.

Empnum Status ssno current MTD QTD YTD
ABC Active 111223333 10 10 10 10
DEF Leave 111223333 20 20 20 20
GHI Terminated 111223333 30 30 30 30

 

[philhege]

So, you want the result to look like

Empnum Status ssno current MTD QTD YTD
ABC Active 111223333 60 60 60 60

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[MMund]

Yes, that is what we want.

 

[rdandrade]

Try using a derived table like below. Should still work fast.

SELECT EmpActive.EmpNum, EmpActive.Status, EmpActive.ssno,
    SUM(EmpAll.current) AS current,
    Sum(EmpAll.MTD) AS MTD,
    SUM(EmpAll.QTD) AS QTD,
    SUM(EmpAll.YTD) AS YTD
FROM Employee EmpAll INNER JOIN
     (SELECT EmpNum, Status, ssno FROM Employee WHERE Status='Active') EmpActive
ON EmpAll.ssno=EmpActive.ssno
GROUP BY EmpActive.EmpNum, EmpActive.Status, EmpActive.ssno

Ray D'Andrade
Access Programmer