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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-10-24 : 09:42:30
|
| Bob writes "I have a table that holds records for a quarterly payroll report. Fields include SSN, LastName, FirstName, ReportPeriod, TypeCode among others. TypeCode is either U or W. It is possible for an employee to have both TypeCodes in the same ReportPeriod. If that happens, we want the TypeCode to change to B and only display one row for that employee on the report. I am not sure how to construct a query to find the doubles and keep track of those that only have one or the other. I have considered that it might involve a union somewhere in the process. Any pointers in putting it together would be greatly appreciated." |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-10-24 : 13:42:52
|
| tryselect SSN, LastName, FirstName, ReportPeriod, TypeCode = case when count(*) > 1 then 'B' else max(TypeCode) end from tblgroup by SSN, LastName, FirstName, ReportPeriod==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
tecknowledge1164
Starting Member
25 Posts |
Posted - 2005-10-25 : 21:22:02
|
| Here is another wrinkle on this grouping. There is a field named ClassCode, which contains an alphanumeric code that applies to records with a W in the original TypeCode, and is null if TypeCode is U. Where there are two entries for the period, It looks like I will need to roll this up with the grouping to include this code so that it will be displayed on the report as if it were shown on a 'W' Typecode record. In other words, if TypeCode converts to 'B', I will need to include the value in the ClassCode field.OK, I think I got it. I just used another case in the query and with just a little head scratching it seems to work. Thanks for the help. |
|
|
|
|
|
|
|
|
Grouping records with a condition