2

Here is the scenario: If the employee transaction is continuous display as-is. if the employee got any gap display as a new row.

Below is the sample table

Emid    Fromdate    Todate
-----   --------    ------
A   1/1/2012    12/31/2012
A   12/31/2012  12/31/2013
A   12/31/2013  12/31/2014
A   2/2/2015    12/31/2099

Expected output:

emid    Fromdate    Todate
======  ========    ========
A   1/1/2012    12/31/2014
A   2/2/2015    12/31/2099
1

You could try this:

[edit] fixed a bug .. comment on line fixed [/edit]

with w_data as (
        select 'A' emid, to_date('01/01/2012','mm/dd/yyyy') fromdate, to_date('12/31/2012','mm/dd/yyyy') todate from dual union all
        select 'A' emid, to_date('12/31/2012','mm/dd/yyyy') fromdate, to_date('12/31/2013','mm/dd/yyyy') todate from dual union all
        select 'A' emid, to_date('12/31/2013','mm/dd/yyyy') fromdate, to_date('12/31/2014','mm/dd/yyyy') todate from dual union all
        select 'A' emid, to_date('02/02/2015','mm/dd/yyyy') fromdate, to_date('12/31/2099','mm/dd/yyyy') todate from dual union all
        select 'B' emid, to_date('05/12/2013','mm/dd/yyyy') fromdate, to_date('11/23/2013','mm/dd/yyyy') todate from dual union all
        select 'B' emid, to_date('11/23/2013','mm/dd/yyyy') fromdate, to_date('12/30/2013','mm/dd/yyyy') todate from dual union all
        select 'B' emid, to_date('01/10/2014','mm/dd/yyyy') fromdate, to_date('03/22/2014','mm/dd/yyyy') todate from dual union all
        select 'B' emid, to_date('04/03/2014','mm/dd/yyyy') fromdate, to_date('12/31/2099','mm/dd/yyyy') todate from dual union all
        select 'C' emid, to_date('05/12/2013','mm/dd/yyyy') fromdate, to_date('11/23/2013','mm/dd/yyyy') todate from dual union all
        select 'C' emid, to_date('11/23/2013','mm/dd/yyyy') fromdate, to_date('01/10/2014','mm/dd/yyyy') todate from dual union all
        select 'C' emid, to_date('01/10/2014','mm/dd/yyyy') fromdate, to_date('04/03/2014','mm/dd/yyyy') todate from dual union all
        select 'C' emid, to_date('04/03/2014','mm/dd/yyyy') fromdate, to_date('12/31/2099','mm/dd/yyyy') todate from dual  
        ),
  w_sub as (
     select emid, fromdate, todate,
              sum(grp) over (partition by emid order by fromdate) grp_id  -- added partition by .. forgot to group it by employee
       from (
              select emid, fromdate, todate,
                     case when fromdate = lag(todate) over (partition by emid order by fromdate) 
                       then 0 else 1 end  grp
                from w_data
             )
        )
select emid, min(fromdate) fromdate, max(todate) todate
  from w_sub
 group by emid, grp_id
 order by emid, grp_id, fromdate
/

E FROMDATE    TODATE
- ----------- -----------
A 01-jan-2012 31-dec-2014
A 02-feb-2015 31-dec-2099
B 12-may-2013 30-dec-2013
B 10-jan-2014 22-mar-2014
B 03-apr-2014 31-dec-2099
C 12-may-2013 31-dec-2099

6 rows selected.
  • I will accept this will work for this sample. But if we have a lot of employees then how it will work. – Babu Aug 19 '15 at 6:46
  • it'll work just fine .. (once I fix the bug I have in there :) I'll fix my original post .. give me a second :) – Ditto Aug 21 '15 at 12:43
  • @ ditto. So manually we have to give the employees in with clause function right. – Babu Aug 26 '15 at 6:37
  • 1
    No, the with clause w_data is just to mimick your source table. you can remove that and change the reference to w_data in the main query to your driving table. – Ditto Aug 26 '15 at 13:14
0
SET NOCOUNT ON

DECLARE @T TABLE(ID INT,FromDate DATETIME, ToDate DATETIME)

INSERT INTO @T(ID,FromDate,ToDate)
SELECT 1,'20090801','20090803' UNION ALL
SELECT 2,'20090802','20090809' UNION ALL
SELECT 3,'20090805','20090806' UNION ALL
SELECT 4,'20090812','20090813' UNION ALL
SELECT 5,'20090811','20090812' UNION ALL
SELECT 6,'20090802','20090802'


SELECT ROW_NUMBER() OVER(ORDER BY s1.FromDate) AS ID,
       s1.FromDate, 
       MIN(t1.ToDate) AS ToDate 
FROM @T s1 
INNER JOIN @T t1 ON s1.FromDate <= t1.ToDate 
  AND NOT EXISTS(SELECT * FROM @T t2 
                 WHERE t1.ToDate >= t2.FromDate
                   AND t1.ToDate < t2.ToDate) 
WHERE NOT EXISTS(SELECT * FROM @T s2 
                 WHERE s1.FromDate > s2.FromDate
                   AND s1.FromDate <= s2.ToDate) 
GROUP BY s1.FromDate 
ORDER BY s1.FromDate

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.