Finding employment history with weekly snapshot
<p>Say I have a table that captures employment status on a monthly basis.
I have a scenario where an employee started off as an intern, went back to school and came back full-time.</p>

<p>Ideally, I would want an output along lines of, which is pretty much a history of his/her employment history.</p>

<pre><code>date_hire    date_termination    employee_id    employee_type
2012_01_01   2012_03_01          10002          intern
2012_05_01   null                10002          full-time
</code></pre>

<p>Data looks like</p>

<pre><code>date_snapshot    date_hire    date_termination    employee_id    employee_type
2012_01_01       2012_01_01   null                10002          intern
2012_02_01       2012_01_01   null                10002          intern
2012_03_01       2012_01_01   2012_03_01          10002          intern
2012_04_01       2012_01_01   2012_03_01          10002          intern
2012_05_01       2012_05_01   null                10002          full-time
2012_06_01       2012_05_01   null                10002          full-time
2012_07_01       2012_05_01   null                10002          full-time
......
</code></pre>

<p>I tried doing this using GROUP BY technique</p>

<pre><code>SELECT
    date_hire,
    date_termination,
    employee_id,
    employee_type
FROM
    employees
GROUP BY
    date_hire,
    date_termination,
    employee_id,
    employee_type
</code></pre>

<p>returns me, note that I have one unwanted record</p>

<pre><code>date_hire    date_termination    employee_id    employee_type
2012_01_01   null                10002          intern
2012_01_01   2012_03_01          10002          intern
2012_05_01   null                10002          full-time
</code></pre>