merging records with particular similarities

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

merging records with particular similarities

Author

Topic

travis.manning1
Starting Member

2 Posts

Posted - 2010-07-20 : 09:47:19

I have an interesting problem to solve. I am working on merging (adding to some extent) rows in a table which records jobs histories. It has an auto-number id field, a company name, a month/year field, and then the various field to be added (hours, amount paid, fringe amounts...). This is being done with a server daemon, so a series of queries is not out of the question. I've solved a similar problem before, in which i had to delete duplicate entries with a particular card number, and it was easily solved with a subquery having a group by clause. This one is a bit more obtuse. Any help would be appreciated :)

travis.manning1
Starting Member

2 Posts

Posted - 2010-07-20 : 10:16:46

On further reading, it would certainly benefit me to include some sample data, so...

ID Company YearMonth Hours Fringes
1 Microsoft 01/09 10 100
2 Microsoft 01/09 15 120
3 Apple 01/09 10 90
4 Microsoft 02/09 15 160
5 Apple 02/09 10 80
6 Apple 02/09 1 5
7 Apple 02/09 16 100
8 Dick's 03/09 4 40

As you can see, the first two rows can be merged, as can rows 5,6,7. The final table should look like this.

ID Company YearMonth Hours Fringes
1 Microsoft 01/09 25 220
3 Apple 01/09 10 90
4 Microsoft 02/09 15 160
5 Apple 02/09 27 185
8 Dick's 03/09 4 40

So far, I've gone with more use of a subquery with a group by, but to no avail. The goal here is to pick out rows with the same company name and year month, and then add their hours and fringes (among other) fields into a single row.

SD_Monkey
Starting Member

38 Posts

Posted - 2010-07-20 : 10:27:18

you aggregate function
try this one...


SELECT MIN(ID) AS [IDno],Company,YearMonth,
sum(Hours) AS [SumHours],
sum(Fringes) AS [SumFringes]
FROM #tbl 
GROUP BY Company,YearMonth

A maze make you much more better

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources