SQL Outer Join Time Series with Data in Different Times
<p>I was trying to combine multiple Cash Flows table for multiple projects into one table using SQL. The query i was using is listed below,</p>

<pre><code>SELECT  a.DATE1, b.DATE2, a.CF1, b.CF2
FROM a
FULL OUTER JOIN b 
ON b.DATE2= a.DATE1
</code></pre>

<p>and the result table is listed below,</p>

<p><a href="https://i.stack.imgur.com/6RtHe.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6RtHe.png" alt="enter image description here"></a></p>

<p>Since cash flows are taken place in different date for different project, some of them might happen in same day, some might not. I am wondering how can I adjust my code so that the result will looks like this,</p>

<p><a href="https://i.stack.imgur.com/hFytZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hFytZ.png" alt="enter image description here"></a></p>

<p>Where we combine first 2 date columns. Keep the date value if the other one is NULL, and remove duplicated values if cash flows happened on the same day. </p>

<p>Since there are multiple projects I will need to combine, the method should be robust for expansion. </p>

<p>I have tried to use following,</p>

<pre><code>SELECT  ISNULL(a.DATE1, '') + ISNULL(b.DATE2, ''), a.CF1, b.CF2
FROM ...
</code></pre>

<p>However, this only solves the NULL part, and will actually add up duplicated date, and result in a new date. </p>

<p>Any help is appreciated, and thanks a lot in advance!</p>