Plz..Urgnet Sql Query help - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Plz..Urgnet Sql Query help

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2012-10-26 : 09:44:50

Hi,

i have one table in which i have multiple records data for single employeeid in need to show that employee result in single record

IF OBJECT_ID('tempdb.dbo.#tempEmployeeEarningsSummary') IS NOT NULL
DROP TABLE #tempEmployeeEarningsSummary
CREATE TABLE #tempEmployeeEarningsSummary
(
employeeID INT NULL,
clientName VARCHAR(50),
clientCode VARCHAR(3) NULL,
ADPBonusCode VARCHAR(100) NULL,
bonusAmount MONEY
)

INSERT #tempEmployeeEarningsSummary VALUES('2308','gg','650','INCENTIVES','100.00')
INSERT #tempEmployeeEarningsSummary VALUES('2310','gg','650','BONUS','35.99')
INSERT #tempEmployeeEarningsSummary VALUES('2310','gg','650','INCENTIVES','35.66')

select * from #tempEmployeeEarningsSummary

I need below output

employeeID clientName clientCode ADPBonusCode bonusAmount ADPBonusCode bonusAmount
2310 gg 650 BONUS 35.99 INCENTIVES 35.66

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-26 : 10:00:41

Why do you take the incentives 35.66 rather than 100?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2012-10-26 : 10:03:02

quote:
Originally posted by nigelrivett

Why do you take the incentives 35.66 rather than 100?

both are different employeeids
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-26 : 13:37:51

Since you didn't say, it appears that you either want a random row or the one with the smallest bonus. Here is a a guess:

SELECT TOP 1 *
FROM #tempEmployeeEarningsSummary
WHERE EmployeeID = 2310
ORDER BY BonusAmount ASC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-26 : 15:12:40

seems like the below to me


SELECT employeeID,
clientName,
clientCode,
MAX(CASE WHEN ADPBonusCode = 'BONUS' THEN ADPBonusCode END) AS ADPBonusCode1,
MAX(CASE WHEN ADPBonusCode = 'BONUS' THEN bonusAmount END) AS bonusAmount1, 
MAX(CASE WHEN ADPBonusCode = 'INCENTIVES' THEN ADPBonusCode END) AS ADPBonusCode2,
MAX(CASE WHEN ADPBonusCode = 'INCENTIVES' THEN bonusAmount END) AS bonusAmount2 
FROM Table
GROUP BY employeeID,
clientName,
clientCode
HAVING COUNT(DISTINCT ADPBonusCode) >1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources