SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Plz..Urgnet Sql Query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vision.v1
Yak Posting Veteran

71 Posts

Posted - 10/26/2012 :  09:44:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 10/26/2012 :  10:00:41  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

vision.v1
Yak Posting Veteran

71 Posts

Posted - 10/26/2012 :  10:03:02  Show Profile  Reply with Quote
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.

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 10/26/2012 :  13:37:51  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/26/2012 :  15:12:40  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000