Can you help me do this stored procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Can you help me do this stored procedure?

Author

Topic

waterding
Starting Member

6 Posts

Posted - 2008-06-19 : 04:50:59

How can I write a stored procedure (SQL 2000) to return the data table below?

InfluencerId FromHCPName FromHCPId ToHCPName ToHCPId
1 Lee Robert 1 Amy Bob 2
2 Kelly David 3 Lee Robert 1
3 Ying Mark 0 Lee Robert 1
4 Amy Bob 2 Rebecca Lucy 0

The database schema is as below:

HCP (Id, FirstName, LastName)

Data:

(1, Lee, Robert)
(2, Amy, Bob)
(3, Kelly, David)

Influencer (Id, FromHCPId, ToHCPId, FromTempHCPName, ToTempHCPName)

Data:
( 1, 1, 2, NULL, NULL )
( 2, 3, 1, NULL, NULL )
( 3, 0, 1, Ying Mark NULL )
(4, 2, 0, NULL, Rebecca Lucy )

ps: If FromHCPId=0, it should return FromTempHCPName for 'From HCP Name' column, else return HCP.FirstName and HCP.LastName

Thanks in advance!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 08:20:11

[code]SELECT i.Id,
COALESCE(COALESCE(h1.FirstName + ' ','') + COALESCE(h1.LastName,''),i.FromTempHCPName) AS FromHCPName,
i.FromTempHCPId as FromHCPId,
COALESCE(COALESCE(h2.FirstName + ' ','') + COALESCE(h2.LastName,''),ToTempHCPName) AS ToHCPName,
i.ToTempHCPId AS ToHCPId
FROM Influencer i
LEFT JOIN HCP h1
ON h1.Id=i.FromHCPId
LEFT JOIN HCP h2
ON h2.Id=i.ToHCPId[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources