Return value dependent upon existance

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)

Return value dependent upon existance

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2010-01-19 : 08:36:15

I have two tables

client
------
ClientID (int)
lastpaymentdate (datetime)

historical
----------
ClientID (int)
lastpaymentdate (datetme)

I want to return all the ClientIDs together with the lastpaymentdate field from the client table if it has entry. If it does not have an entry return the lastpaymentdate from the historial table.

How can I do this please?
101

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-19 : 09:24:39

Edit: Sorry this will not work, you obviously will not have a ClientID if there is no entry. You need to left join the historical data and where a.clientID is NULL pull data from table b.

Meeting, so I am sure someone else can assist!

Select ClientID, Coalesce(a.lastpaymentdate, b.lastpaymentdate) as LastPayment from client a Left outer Join Historical b on
a.ClientID = b.ClientID

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 10:18:17

[code]
SELECT ClientID, lastpaymentdate
FROM client
WHERE lastpaymentdate IS NOT NULL
UNION ALL
SELECT ClientID, MAX(lastpaymentdate) AS lastpaymentdate
FROM historical AS H
WHERE lastpaymentdate IS NOT NULL
AND NOT EXISTS
(
SELECT *
FROM client AS C
WHERE lastpaymentdate IS NULL
AND C.ClientID = H.ClientID
)
GROUP BY ClientID
[/code]
Assumptions: one transaction per ClientID in Client table (i.e. ClientID is unique), and multiple transaction per ClientID in Historical table (i.e. ClientID is NOT unique)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources