need help with select - 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

General SQL Server Forums

New to SQL Server Programming

need help with select

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2012-10-19 : 13:29:46

Hi All.

I have Customer table:
CustomerId
StatusId
CustomerName
Account#
ServiceDate

Status table
StatusId
Status
StatusDate
PaidAmount

Status has value R and RD

I would like to create select to display:
CustomerName
Account#
ServiceDate
Status(R)
Status(RD)
StatusDate(R)
StatusDate(RD)
PaidAmount

How to get such select? Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-19 : 13:46:36

I'm not sure I understand what you want.. You didn't tell us anything about the cardinality between the tables. No definition of the keys. No data. No Expected output. Please see the following links for how to provide the proper information so we can help you:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-19 : 22:44:13

If I understand you correctly, it would be something like


SELECT CustomerName,
[Account#],
[ServiceDate],
MAX(CASE WHEN Status = 'R' THEN Status END) AS [Status(R)],
MAX(CASE WHEN Status = 'RD' THEN Status END) AS [Status(RD)],
MAX(CASE WHEN Status = 'R' THEN StatusDate END) AS [StatusDate(R)],
MAX(CASE WHEN Status = 'RD' THEN StatusDate END) AS [StatusDate(RD)],
SUM(PaidAmount)
FROM Customer c
INNER JOIN Status s
ON s.StatusId = c.StatusId
GROUP BY CustomerName,
[Account#],
[ServiceDate]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources