quote:

---

1.|--------------|
2............|-----------------|
3......|-----------------|

In this case i want start date from 1 and end date from 3... this is easy

---

quote:

---

1.|------------|
2....|------------|
3....................|-------------------|

In this i want all dates from 3....

---

CREATE TABLE #Table
(
Customer int,
[Service] int,
StartDate datetime,
EndDate datetime
) 
INSERT #Table
SELECT 1, 1, '20000101'  ,'20010101'  UNION ALL
SELECT 1, 2, '20010102', '20091210' UNION ALL
SELECT 1, 3, '20100101', '20100201' UNION ALL
SELECT 2, 1, '20000101', '20030101' UNION ALL
SELECT 2, 2, '20051001', '20091231' UNION ALL
SELECT 2, 3, '20090101', '20090430' UNION ALL
SELECT 3, 1, '20050101', '20060101' UNION ALL
SELECT 3, 2, '20061001', '20091231' UNION ALL
SELECT 3, 3, '20090101', '20100310' UNION ALL
SELECT 4, 1, '20050101', '20061201' UNION ALL
SELECT 4, 2, '20061001', '20071231' UNION ALL
SELECT 4, 3, '20080101', '20090210' UNION ALL
SELECT 4, 4, '20080501', '20090101' UNION ALL
SELECT 4, 5, '20081001', '20091231' UNION ALL
SELECT 4, 6, '20090101', '20090310' UNION ALL
SELECT 4, 7, '20100101', '20100125' UNION ALL
SELECT 4, 8, '20100120', '20100130' UNION ALL
SELECT 4, 9, '20100112', '20100310'



;With CTE 
AS
(
SELECT t.Customer,
CASE WHEN t1.MinDate IS NOT NULL AND t1.MinDate<t.Startdate THEN t1.MinDate ELSE t.StartDate END AS StartDate,
CASE WHEN t1.MaxDate IS NOT NULL AND t1.MaxDate>t.Enddate THEN t1.MaxDate ELSE t.EndDate END AS EndDate,
DENSE_RANK() OVER (PARTITION BY Customer ORDER BY CASE WHEN t1.MinDate IS NOT NULL AND t1.MinDate<t.Startdate THEN t1.MinDate ELSE t.StartDate END,
                    CASE WHEN t1.MaxDate IS NOT NULL AND t1.MaxDate>t.Enddate THEN t1.MaxDate ELSE t.EndDate END) 
                    AS Overlapped
FROM #TAble t
OUTER APPLY (SELECT MIN(StartDate) AS MinDate,MAX(EndDate) AS MaxDate,COUNT(*) AS cnt
             FROM #TAble
             WHERE Customer=t.Customer
             AND [Service] <> t.[Service]
             AND (t.EndDate BETWEEN StartDate AND EndDate
             OR  t.StartDate BETWEEN StartDate AND EndDate)
             )t1
            
)

SELECT c1.Customer,StartDate,EndDate
FROM CTE c1
INNER JOIN (SELECT Customer,MAX(Overlapped) AS MaxOverlap
            FROM CTE 
            GROUP BY Customer)c2
ON c2.Customer=c1.Customer
AND c2.MaxOverlap=c1.Overlapped
GROUP BY c1.Customer,StartDate,EndDate

DROP TABLE #Table

output
--------------------------------------
entered data 
Customer Service	StartDate	EndDate
1	 1	2000-01-01 00:00:00.000	2001-01-01 00:00:00.000
1	 2	2001-01-02 00:00:00.000	2009-12-10 00:00:00.000
1	 3	2010-01-01 00:00:00.000	2010-02-01 00:00:00.000
2	 1	2000-01-01 00:00:00.000	2003-01-01 00:00:00.000
2	 2	2005-10-01 00:00:00.000	2009-12-31 00:00:00.000
2	 3	2009-01-01 00:00:00.000	2009-04-30 00:00:00.000
3	 1	2005-01-01 00:00:00.000	2006-01-01 00:00:00.000
3	 2	2006-10-01 00:00:00.000	2009-12-31 00:00:00.000
3	 3	2009-01-01 00:00:00.000	2010-03-10 00:00:00.000
4	 1	2005-01-01 00:00:00.000	2006-12-01 00:00:00.000
4	 2	2006-10-01 00:00:00.000	2007-12-31 00:00:00.000
4	 3	2008-01-01 00:00:00.000	2009-02-10 00:00:00.000
4	 4	2008-05-01 00:00:00.000	2009-01-01 00:00:00.000
4	 5	2008-10-01 00:00:00.000	2009-12-31 00:00:00.000
4	 6	2009-01-01 00:00:00.000	2009-03-10 00:00:00.000
4	 7	2010-01-01 00:00:00.000	2010-01-25 00:00:00.000
4	 8	2010-01-20 00:00:00.000	2010-01-30 00:00:00.000
4	 9	2010-01-12 00:00:00.000	2010-03-10 00:00:00.000


output data
Customer	StartDate	EndDate
1	2010-01-01 00:00:00.000	2010-02-01 00:00:00.000
2	2005-10-01 00:00:00.000	2009-12-31 00:00:00.000
3	2006-10-01 00:00:00.000	2010-03-10 00:00:00.000
4	2010-01-01 00:00:00.000	2010-03-10 00:00:00.000

quote:

---

Originally posted by djpirra

Hey there,

I have tried it but somehow its not working fast...
My table have like 15M rows... and i need to flatten the time for all customers. 3H running and nothing returned...
If i run for a single customer it seems fast.

What todo?

Regards

---