Código SQL:
Ver originalCREATE TABLE #temp(
Num INT,
START datetime,
[END] datetime,
[State] INT,
Duration INT)
INSERT INTO #temp VALUES (1545782,'02.20.2015 06:00','02.20.2015 06:02', 2 ,3)
INSERT INTO #temp VALUES (1545782,'02.20.2015 07:11','02.20.2015 07:13',2, 1)
INSERT INTO #temp VALUES (1545782,'02.20.2015 07:13','02.20.2015 07:15',2, 2)
INSERT INTO #temp VALUES (1545782,'02.20.2015 07:23','02.20.2015 07:27',20, 4)
INSERT INTO #temp VALUES (1545782,'02.20.2015 07:28','02.20.2015 07:41',20, 12)
INSERT INTO #temp VALUES (1545782,'02.20.2015 08:27','02.20.2015 08:36',20, 10)
INSERT INTO #temp VALUES (1545782,'02.20.2015 08:48','02.20.2015 08:59',20, 11)
INSERT INTO #temp VALUES (1546788,'02.20.2015 21:33','02.20.2015 21:34',2, 1)
INSERT INTO #temp VALUES (1546788,'02.20.2015 21:34','02.20.2015 21:34',2, 20)
INSERT INTO #temp VALUES (1545788,'02.19.2015 18:51','02.19.2015 18:57',20, 6)
INSERT INTO #temp VALUES (1547912,'02.19.2015 19:35','02.19.2015 19:38',2, 4)
INSERT INTO #temp VALUES (1547912,'02.19.2015 19:38','02.19.2015 19:41',2, 10)
SELECT num, MIN(START) AS [START], MAX([END]) AS [END], state, SUM(duration) AS duration FROM #temp WHERE state=2 GROUP BY num,state
UNION
SELECT num, START, [END],state, duration FROM #temp WHERE state=20
una sola consulta, el resultado buscado:
num start end state duration
1545782 2015-02-20 06:00:00.000 2015-02-20 07:15:00.000 2 6
1546788 2015-02-20 21:33:00.000 2015-02-20 21:34:00.000 2 21
1547912 2015-02-19 19:35:00.000 2015-02-19 19:41:00.000 2 14
1545782 2015-02-20 07:23:00.000 2015-02-20 07:27:00.000 20 4
1545782 2015-02-20 07:28:00.000 2015-02-20 07:41:00.000 20 12
1545782 2015-02-20 08:27:00.000 2015-02-20 08:36:00.000 20 10
1545782 2015-02-20 08:48:00.000 2015-02-20 08:59:00.000 20 11
1545788 2015-02-19 18:51:00.000 2015-02-19 18:57:00.000 20 6
P.D: Demasiado pedir para sql server??? Yo cambiaria el titulo a "Demasiado pedir para Mi" :P