SELECT needs to merge two columns into one

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)

SELECT needs to merge two columns into one

Author

Topic

JAG7777777
Starting Member

25 Posts

Posted - 2010-01-19 : 10:00:21

Hi,

I have a query that results in one table after joining two grouped tables together. The two grouped tables represent aggreagte order and delivery data. They are joined, using a full outer join, on their date fields. A full join is used as there are days when there are no deliveries but there are orders and vice versa.
This data comes from a transactional processing DB that only captures a delivery or an order (but not the days in between where there are neither).

My resulting table therefore has some null date fields (either the Order date or the Delivery date).

I need to be able to SELECT * from my resulting (joined) table and at the same time, create a new column called Date, which represents the delivery date if it is not null, otherwise the order date. Both cannot be null for obvious reasons, so the logic should be sound.
Does anyone know the syntax I can use in the SELECT clause that can create this new column by comparing these two date fields in some way.....

Many thanks for your help.

JAG7777777

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-19 : 10:20:33

select
col1,
isnull(deliverydate,orderdate) as newdatecolumn
from
(your_select)dt

Also look for coalesce() instead of isnull():
http://msdn.microsoft.com/en-us/library/ms190349(SQL.90).aspx

No, you're never too old to Yak'n'Roll if you're too young to die.