Demasiado pedir para SQL?

Facto · #1 (**permalink**) 24/02/2015, 13:17

Hola a todos,

quería pediros vuestra opinión sobre si la siguiente operación se puede hacer en sql con una sentencia sql sobre una única tabla, o directamente necesito programación:

1.- Partimos de esta tabla

Num Start End State Duration
1545782 02.20.2015 06:00 02.20.2015 06:02 2 3
1545782 02.20.2015 07:11 02.20.2015 07:13 2 1
1545782 02.20.2015 07:13 02.20.2015 07:15 2 2
1545782 02.20.2015 07:23 02.20.2015 07:27 20 4
1545782 02.20.2015 07:28 02.20.2015 07:41 20 12
1545782 02.20.2015 08:27 02.20.2015 08:36 20 10
1545782 02.20.2015 08:48 02.20.2015 08:59 20 11
1546788 02.20.2015 21:33 02.20.2015 21:34 2 1
1546788 02.20.2015 21:34 02.20.2015 21:34 2 20
1545788 02.19.2015 18:51 02.19.2015 18:57 20 6
1547912 02.19.2015 19:35 02.19.2015 19:38 2 4
1547912 02.19.2015 19:38 02.19.2015 19:41 2 10

2.- Debemos llegar a obtener esta otra tabla:

Num Start End State Duration
1545782 02.20.2015 06:00 02.20.2015 07:15 2 6
1545782 02.20.2015 07:23 02.20.2015 07:27 20 4
1545782 02.20.2015 07:28 02.20.2015 07:41 20 12
1545782 02.20.2015 08:27 02.20.2015 08:36 20 10
1545782 02.20.2015 08:48 02.20.2015 08:59 20 11
1546788 02.20.2015 21:33 02.20.2015 21:34 2 21
1545788 02.19.2015 18:51 02.19.2015 18:57 20 6
1547912 02.19.2015 19:35 02.19.2015 19:41 2 14

3.- Esto se consigue aplicando las siguientes reglas:

Si las filas tienen igual Num y State = 2……………….muestras 1 fila con Num, Start (el más pequeño), End (el más grande), State, SUM(Duration de todos ellos).

Si las filas tienen igual Num y State = 20…………….muestras todas las filas con Num, Start, End, State, Duration.

Problema:
El Group by con condiciones se me escapa, combinar con HAVING y una Select anidada tampoco me ha dado resultados, se os ocurre algo?

Gracias de antemano!

Facto · #2 (**permalink**) 24/02/2015, 13:23

Perdón, las tablas casi no se ven y no puedo subir una imagen, los campos son:

Número identificador - Fecha y hora de empiece - Fecha y hora de fin - Estado - Duracion

Perdón!

Libras · #3 (**permalink**) 24/02/2015, 14:52

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

Facto · #4 (**permalink**) 25/02/2015, 11:37

Sí, según veo me voy a tener que tragar absolutamente mis palabras......... :)

Y nada complejo, la verdad. Estoy deseando llegar a casa y probarlo.

Luego te digo...

Facto · #5 (**permalink**) 03/03/2015, 05:05

Funciona absolutamente perfecto. Te doy las gracias Libras.

Una última cosa, cómo hago para ordenar la tabla final después del UNION? es decir, imagina que quiero ordenar por fecha Start en asc y también por state en desc...

Libras · #6 (**permalink**) 03/03/2015, 08:55

basandonos en el ejemplo anterior:

Código SQL:

Ver originalSELECT * FROM(
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
) AS completa ORDER BY campo1,campo2 ASC/DESC

Facto · #7 (**permalink**) 03/03/2015, 09:23

Joder....ok. Funciona bien.

tenía entendido que si en el último select del union se hace el order by todo se ordenaba, pero...no!

Gracias de nuevo Libras

Facto · #8 (**permalink**) 09/03/2015, 12:57

Libras, te planteo otro caso sobre el mismo ejemplo por si me puedes ayudar, me he hecho un lio con cómo realizar INSERT + INNER JOIN + SELECT:

1.- Partimos de la famosa TABLA A (que ya conoces) (a la cual ya le he añadido un campo con un ID autonumérico secuencial y no repetitivo que quiero usar como identificador para hacer búsquedas en la tabla):

Workcenter | Num | ID
10 | Cliente A | 1
10 | Cliente B | 2
10 | Cliente C | 3
10 | Cliente D | 4
20 | Cliente A | 5
20 | Cliente B | 6
20 | Cliente C | 7

2.- Buscamos insertar o crear una nueva tabla con los campos Workcenter, Num y ID (exactamente iguales que en la tabla A) y además los campos NumInyectado y IDInyectado. Y toda la información debe salir de la tabla A: Para cada "Workcenter" los clientes están relacionados de forma que el número 1 es soportado por el cliente número 2. El cliente 2 es inyectado por el cliente 3, el cliente 3 por el cliente 4, y el cliente 4 no es inyectado por ninguno porque ya no hay más clientes para ese "Workcenter". Con lo que la tabla queda:

Workcenter | Num | ID | NumInyectado | IDInyectado
10 | Cliente A | 1 | Cliente B | 2
10 | Cliente B | 2 | Cliente C | 3
10 | Cliente C | 3 | Cliente D | 4
10 | Cliente D | 4 | - | -
20 | Cliente A | 5 | Cliente B | 6
20 | Cliente B | 6 | Cliente C | 7
20 | Cliente C | 7 | - | -

Cómo relaciono todo Libras? no sé gestionar ese INSERT, SELECT y INNER JOIN con tanto acceso a la tabla A.

Un saludo

Libras · #9 (**permalink**) 09/03/2015, 14:54

creo que entiendo que quieres, pero tengo una pregunta:

la tabla que esta en el paso 1 es la tablaB??

la tablaA(que se supone ya conozco) cual es? que estructura tiene?? de nuevo un ejemplo de tus datos(ambas tablas) que quieres obtener y cuales son las condiciones ayudaria mucho :)

Facto · #10 (**permalink**) 09/03/2015, 15:52

Libras, la tabla del paso 1 es la tabla A (tiene muchos más campos, pero solo estos son importantes (digo que ya la conoces porque es una derivación de la tabla con la que empezamos en este post)).

TABLA A:
Workcenter | Num | ID
10 | Cliente A | 1
10 | Cliente B | 2
10 | Cliente C | 3
10 | Cliente D | 4
20 | Cliente A | 5
20 | Cliente B | 6
20 | Cliente C | 7

La tabla final que queremos obtener es la tabla B. La tabla B es muy parecida a la A y usa solo datos de la tabla A: combinándolos a través del identificador

TABLA B:
Workcenter | Num | ID | NumInyectado | IDInyectado
10 | Cliente A | 1 | Cliente B | 2
10 | Cliente B | 2 | Cliente C | 3
10 | Cliente C | 3 | Cliente D | 4
10 | Cliente D | 4 | - | -
20 | Cliente A | 5 | Cliente B | 6
20 | Cliente B | 6 | Cliente C | 7
20 | Cliente C | 7 | - | -

No sé si me expreso bien....

Libras · #11 (**permalink**) 09/03/2015, 16:25

Código SQL:

Ver originalCREATE TABLE #temp
(
id INT,
cliente VARCHAR(20),
rn INT IDENTITY(1,1)
)
 
INSERT INTO #temp VALUES (10,'Cliente A')
INSERT INTO #temp VALUES (10,'Cliente B')
INSERT INTO #temp VALUES (10,'Cliente C')
INSERT INTO #temp VALUES (10,'Cliente D')
INSERT INTO #temp VALUES (20,'Cliente A')
INSERT INTO #temp VALUES (20,'Cliente B')
INSERT INTO #temp VALUES (20,'Cliente C')
 
SELECT t1.id AS workcenter,t1.cliente,t1.rn AS id, t2.cliente AS inyectado, t2.rn AS idinyectado FROM #temp AS t1
LEFT JOIN #temp AS t2 ON (t1.rn+1=t2.rn AND t1.id=t2.id)

Resultado:

workcenter cliente id inyectado idinyectado
10 Cliente A 1 Cliente B 2
10 Cliente B 2 Cliente C 3
10 Cliente C 3 Cliente D 4
10 Cliente D 4 NULL NULL
20 Cliente A 5 Cliente B 6
20 Cliente B 6 Cliente C 7
20 Cliente C 7 NULL NULL

Libras · #12 (**permalink**) 10/03/2015, 08:45

y era lo que buscabas??? Creo que si, si no ya hubiera otro post diciendo que no jejejejej :P

Facto · #13 (**permalink**) 10/03/2015, 09:40

Estoy con ello ahora mismo!

En cualquier caso, muchas gracias por anticipado Libras. Cuando lo acople todo te digo.

Muy bueno el IDENTITY...

Facto · #14 (**permalink**) 14/03/2015, 04:05

Funciona perfecto Libras. Y aunque la tabla es enorme, la query la completa muy rápido. Muchas gracias por las soluciones!

Ahora estoy trabado con otras queries, a ver si las puedo sacar....