Consulta de SQL y campos fecha

[bendark23]

tengo una consulta

Código SQL:

SELECT * 
FROM EVENTS 
WHERE event_type = 270 AND eventdate >= DateAdd(YEAR,DateDiff(YEAR,0,Getdate())-2,0)
 

Editado: Código de programacion no permitido en foros de BBDD.

me arroja estos resultados.

Código:

event_id	site_id	identity_id	parity	event_type	eventdate	
383325	20	17045	NULL	270	2012-06-05 00:00:00.000	
383327	20	17045	NULL	270	2012-06-05 00:00:00.000	
383329	20	17045	NULL	270	2012-06-06 00:00:00.000	
383338	20	17045	NULL	270	2012-10-26 00:00:00.000	
383339	20	17045	NULL	270	2012-10-26 00:00:00.000	
383341	20	17045	NULL	270	2012-10-27 00:00:00.000	
383343	20	17050	NULL	270	2012-04-29 00:00:00.000	
383345	20	17050	NULL	270	2012-04-29 00:00:00.000	
383346	20	17050	NULL	270	2012-04-30 00:00:00.000	
383354	20	17053	NULL	270	2012-05-02 00:00:00.000	
383356	20	17053	NULL	270	2012-05-02 00:00:00.000	
383358	20	17053	NULL	270	2012-05-03 00:00:00.000	
383365	20	17053	NULL	270	2012-09-22 00:00:00.000	
383367	20	17053	NULL	270	2012-09-22 00:00:00.000	
383369	20	17053	NULL	270	2012-09-23 00:00:00.000	
383373	20	17060	NULL	270	2012-05-01 00:00:00.000	
383374	20	17060	NULL	270	2012-05-01 00:00:00.000	
383376	20	17060	NULL	270	2012-05-02 00:00:00.000	
383382	20	17060	NULL	270	2012-09-23 00:00:00.000	
383384	20	17060	NULL	270	2012-09-23 00:00:00.000	
383385	20	17060	NULL	270	2012-09-24 00:00:00.000	
383387	20	17060	NULL	270	2012-10-13 00:00:00.000	
383388	20	17060	NULL	270	2012-10-13 00:00:00.000	
383390	20	17060	NULL	270	2012-10-14 00:00:00.000	
383394	20	17066	NULL	270	2012-05-10 00:00:00.000	
383396	20	17066	NULL	270	2012-05-10 00:00:00.000	
383397	20	17066	NULL	270	2012-05-11 00:00:00.000	
383404	20	17066	NULL	270	2012-10-02 00:00:00.000	
383405	20	17066	NULL	270	2012-10-02 00:00:00.000	
383407	20	17066	NULL	270	2012-10-03 00:00:00.000

hasta aqui todo bien. mi duda es esta. como podran ver este evento se conoce como monta. por eso el campo identity_id se repite constantemente, pues bien lo que me gustaría obtener es el grupo de montas como se hace eso? pues se considiera grupo de montas a lo que seria un servicio

por ejemplo las primeras filas de la consulta

Código:

383325	20	17045	NULL	270	2012-06-05 00:00:00.000	
383327	20	17045	NULL	270	2012-06-05 00:00:00.000	
383329	20	17045	NULL	270	2012-06-06 00:00:00.000	
383338	20	17045	NULL	270	2012-10-26 00:00:00.000	
383339	20	17045	NULL	270	2012-10-26 00:00:00.000	
383341	20	17045	NULL	270	2012-10-27 00:00:00.000

se considera un servicio a aquel evento que pasa 6 dias despues del primero por ejemplo el identity_id 17045 ocurrio por primera vez el 2012-06-05 por segunda vez el mismo dia y tercera vez el 2012-0606 como ocurrio en los primeros 6 dias es considerado un servicio.
y el segundo servicio(grupo de montas) ocurrio en las fechas 2012- 10 -26 al 2012-10-27.

como podria expresar eso con la consulta que ya tengo soy primerizo y no see como armar esa consulta espero puedan ayudarme de antemano muchas gracias.

[Libras]

explicate mejor, no se te entiende que es lo que quieres........porque asi lo que entiendo es que quieres agrupar los que tienen el mismo id y 6 dias de diferencia(otra cosa no expliques tu logica con terminos que tu entiendes montas y demas) es mas facil que pongas tengo esto y quiero algo como esto....

[bendark23]

Iniciado por Libras

explicate mejor, no se te entiende que es lo que quieres........porque asi lo que entiendo es que quieres agrupar los que tienen el mismo id y 6 dias de diferencia(otra cosa no expliques tu logica con terminos que tu entiendes montas y demas) es mas facil que pongas tengo esto y quiero algo como esto....

gracias por tu respuesta.. en efecto es asi como lo explicas.. agrupar el identity_id dentro de los primeros 6 dias. siento haberme explayado con tanta explicacion esta tabla pertenece a una granja y el evento 270 se conoce como montas, que es cuando inseminan artificialmente a las hembras de la granja. y un servicio es aquel grupo de montas entonces se considera servicio al grupo de montas que ocurren en los primeros 6 dias.

y en efecto es agrupar los eventos del identity_id que ocurran en los primeros 6 dias.

gracias de antemano.

[bendark23]

intente con esto,

Código SQL:

SELECT *
FROM EVENTS WHERE event_type = 270 AND
        eventdate >= DateAdd(YEAR,DateDiff(YEAR,0,Getdate())-1,0) 
GROUP BY  identity_id,DATEADD(DAY,6,eventdate)
 

pero me sale este error

Column 'BH_EVENTS.event_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

[gnzsoloyo]

No puedes agrupar de esa forma en SQL Server. Eso sólo funciona en MySQL.
En SQL Server debes indicar todas las columnas que no estén afectadas por una función agregada (MAX, MIN, AVG, COUNT, etc.), cosa que no puedes hacer si usas asterisco (*).

[bendark23]

Iniciado por gnzsoloyo

No puedes agrupar de esa forma en SQL Server. Eso sólo funciona en MySQL.
En SQL Server debes indicar todas las columnas que no estén afectadas por una función agregada (MAX, MIN, AVG, COUNT, etc.), cosa que no puedes hacer si usas asterisco (*).

gracias por tu respuesta.. alguna idea de como quedaría esto? ya me hice bolas...

[gnzsoloyo]

Me parece que tu problema es que estás confundiendo ordenar con agrupar.
En BBDD son cosas diferentes.
Un GROUP BY es una operación donde se trata de obtener subtotales en base a un conjunto de datos que tomados en grupo, se diferencian entre un registro y otro.

datos como:

1, 33, 34, 6, 7, 9
1, 32, 34, 6, 7, 9

tomados como grupo son registros distintos, por más que sólo se diferencien en un único valor, cuando aplicas el GROUP BY.

Lo que no puedes hacer es agrupar sin especificar qué agrupas, es decir, cuáles campos se diferenciarán y cuales no. Es una restricción obligatoria en SQL Server, PostgreSQL, Oracle, DB2 y otros DBMS (no en MySQL).

Si lo que quieres es obtener los registros correspondientes a los 6 últimos días (los 6 primeros son otra cosa), la consulta que haces, sin el GROUP BY te lo devolvería.
Podríamos intentarlo así:

Código SQL:

SELECT site_id, identity_id, parity, event_type, eventdate
FROM events
WHERE 
    event_type = 270 
    AND eventdate BETWEEN  DATEADD(DAY, -6, Getdate()) AND GetDate()
ORDER BY eventdate, site_id, identity_id, parity, event_type
 

O bien lo hacemos agrupando:

Código SQL:

SELECT site_id, identity_id, parity, event_type, MAX(eventdate) eventdate
FROM events
WHERE 
    event_type = 270 
    AND eventdate BETWEEN  DATEADD(DAY, -6, Getdate()) AND GetDate()
GROUP BY site_id, identity_id, parity, event_type
 

Como sea, lo primero que debes definir es qué información quieres obtener. Eso es lo que no está muy claro. Y por "qué información", estamos hablando de qué nivel de consolidación (sumas, promedios, maximos, mínimos), y sobre qué campos necesitas.

[bendark23]

Iniciado por bendark23

tengo una consulta

Código SQL:

SELECT * 
FROM EVENTS 
WHERE event_type = 270 AND eventdate >= DateAdd(YEAR,DateDiff(YEAR,0,Getdate())-2,0)
 

me arroja estos resultados.

Código:

event_id	site_id	identity_id	parity	event_type	eventdate	
383325	20	17045	NULL	270	2012-06-05 00:00:00.000	
383327	20	17045	NULL	270	2012-06-05 00:00:00.000	
383329	20	17045	NULL	270	2012-06-06 00:00:00.000	
383338	20	17045	NULL	270	2012-10-26 00:00:00.000	
383339	20	17045	NULL	270	2012-10-26 00:00:00.000	
383341	20	17045	NULL	270	2012-10-27 00:00:00.000	
383343	20	17050	NULL	270	2012-04-29 00:00:00.000	
383345	20	17050	NULL	270	2012-04-29 00:00:00.000	
383346	20	17050	NULL	270	2012-04-30 00:00:00.000	
383354	20	17053	NULL	270	2012-05-02 00:00:00.000	
383356	20	17053	NULL	270	2012-05-02 00:00:00.000	
383358	20	17053	NULL	270	2012-05-03 00:00:00.000	
383365	20	17053	NULL	270	2012-09-22 00:00:00.000	
383367	20	17053	NULL	270	2012-09-22 00:00:00.000	
383369	20	17053	NULL	270	2012-09-23 00:00:00.000	
383373	20	17060	NULL	270	2012-05-01 00:00:00.000	
383374	20	17060	NULL	270	2012-05-01 00:00:00.000	
383376	20	17060	NULL	270	2012-05-02 00:00:00.000	
383382	20	17060	NULL	270	2012-09-23 00:00:00.000	
383384	20	17060	NULL	270	2012-09-23 00:00:00.000	
383385	20	17060	NULL	270	2012-09-24 00:00:00.000	
383387	20	17060	NULL	270	2012-10-13 00:00:00.000	
383388	20	17060	NULL	270	2012-10-13 00:00:00.000	
383390	20	17060	NULL	270	2012-10-14 00:00:00.000	
383394	20	17066	NULL	270	2012-05-10 00:00:00.000	
383396	20	17066	NULL	270	2012-05-10 00:00:00.000	
383397	20	17066	NULL	270	2012-05-11 00:00:00.000	
383404	20	17066	NULL	270	2012-10-02 00:00:00.000	
383405	20	17066	NULL	270	2012-10-02 00:00:00.000	
383407	20	17066	NULL	270	2012-10-03 00:00:00.000

hasta aqui todo bien. mi duda es esta. como podran ver este evento se conoce como monta. por eso el campo identity_id se repite constantemente, pues bien lo que me gustaría obtener es el grupo de montas como se hace eso? pues se considiera grupo de montas a lo que seria un servicio

por ejemplo las primeras filas de la consulta

Código:

383325	20	17045	NULL	270	2012-06-05 00:00:00.000	
383327	20	17045	NULL	270	2012-06-05 00:00:00.000	
383329	20	17045	NULL	270	2012-06-06 00:00:00.000	
383338	20	17045	NULL	270	2012-10-26 00:00:00.000	
383339	20	17045	NULL	270	2012-10-26 00:00:00.000	
383341	20	17045	NULL	270	2012-10-27 00:00:00.000

se considera un servicio a aquel evento que pasa 6 dias despues del primero por ejemplo el identity_id 17045 ocurrio por primera vez el 2012-06-05 por segunda vez el mismo dia y tercera vez el 2012-0606 como ocurrio en los primeros 6 dias es considerado un servicio.
y el segundo servicio(grupo de montas) ocurrio en las fechas 2012- 10 -26 al 2012-10-27.

como podria expresar eso con la consulta que ya tengo soy primerizo y no see como armar esa consulta espero puedan ayudarme de antemano muchas gracias.

gracias por tu respuesta pero no es lo que busco, con esa consulta me arroja los resultados de los 6 dias en cuestion. quizas me explique mal..

con la primera consulta obtengo los datos que se muestran en la primerta tabla, bien el evento 270 del identity_id se repite contantemente como se puede ver se condiera una monta (inseminacion cada vez) hasta ahi todo bien. lo que ocupo es sacar el numero se servicios que han ocurrido en una fecha determinada. se condiera un servicio a un grupo de montas. por ejemplo

si el identity_id 17555 tuvo las siguientes montas
2013-12- 15
2013-12-14
2013-12-12
2013-12-01
2013-11-30
2013-11-29


se considera un serivcio al grupo de montas que tivieron lugar los primeros 6 días a partir del primer evento (2013-11-29) según el ejemplo, esta hembra tuvo 2 servicios

1- primera monta 2013-11-29
segunda monta 2013-11-30
tercera monta 2013-12-01

13 dias despues..

2 - primera monta 2013-12-12
segunda monta 2013-12-14
tercera monta 2013-12- 15


eso es lo que intento saber... pero ya me hice bolas.

[gnzsoloyo]

eso es lo que intento saber... pero ya me hice bolas.

...Es que no es una consulta sencilla de SELECT * FROM... WHERE... lo que necesitas para responder esto.

Espera un poco a que alguno de los más expertos de SQL Server te conteste, porque necesita algo más de práctica en SQL Server de la que yo tengo (si fuera Oracle o MySQL sería distinto :D).

[bendark23]

explicame para mysql.

lo que intento es que tome en cuenta el primer evento del indentity_id , y descarte los siguientes que ocurran dentro de los 6 días siguientes para que esto sea un servicio.

explicamelo para mysql por favor.. te lo agradeceria muchisimo.

[gnzsoloyo]

explicame para mysql.

La solución en MySQL es inaplicable para SQL Server u Oracle.
Ciertas capacidades que cada DBMS le pone a su versión de SQL hacen que no se puedan migrar de uno a otro las soluciones.
Al menos no todas.

Por caso, en MySQL tu primera consulta con uso de GROUP BY hubiese funcionado, pero si lo intentas en SQL Server, dispara un error insalvable.

[bendark23]

la primera consulta la uso para extraer información de SQL Server para no trabajar directamente en una BD de un sistema PRO. con esa informacion lleno una tabla en mysql. de ahí mi interés...

si la pedía en sql era para extraer de una sola vez la información ya lista.

[gnzsoloyo]

Iniciado por bendark23

la primera consulta la uso para extraer información de SQL Server para no trabajar directamente en una BD de un sistema PRO. con esa informacion lleno una tabla en mysql. de ahí mi interés...

si la pedía en sql era para extraer de una sola vez la información ya lista.

Explicame algo: ¿Trabajas con SQL Server o con MySQL?
¿O la fuente de datos es SQL Server, y la base destino es MySQL?

Eso es información MUY importante, porque no es lo mismo hacerlo entre esas dos, que hacerlo todo con una sola...

[bendark23]

Iniciado por gnzsoloyo

Explicame algo: ¿Trabajas con SQL Server o con MySQL?
¿O la fuente de datos es SQL Server, y la base destino es MySQL?

Eso es información MUY importante, porque no es lo mismo hacerlo entre esas dos, que hacerlo todo con una sola...

la primera consulta del post es desde php que extrae resultados de una tabala de sql server. los resultados que arroja los guarde en una tabla de mysql. ahora lo que intento es depurar esos datos como ya lo mencione antes. haciendo que me tome en cuenta el primer evento del identity_id... y si ocurren mas dentro de los 6 dias posteriores al primer evento los descarte.. si ocurre uno al 7 dia es tomando en cuenta como un servicio mas.

[gnzsoloyo]

Trata de atenerte a lo que te pregunto. No te explayes sobre lo que ya dijiste, porque eso ya lo entendí.

Te repito: No te servirá de nada que te diga cómo obtener los datos en MySQL, porque no podrás usar la misma sintaxis en SQL Server, que es de donde debes sacarlos.
No mezcles las cosas.

La query:

Código MySQL:

SELECT *
FROM EVENTS 
WHERE event_type = 270 AND eventdate >= date_sub(CURDATE(), INTERVAL 6 DAY) 
GROUP BY  identity_id, DATE(eventdate)
 

Funcionaría en MySQL, pero no funciona en SQL Server, porque éste no reconocerá la sintaxis, ni tampoco el agrupamiento que indica.

¿Te queda claro eso?

MySQL admite agrupar por columnas no indicadas en el SELECT, por parte de las columnas, e incluso por columnas virtuales. Pero nada de eso es admitido por SQL Server. No son iguales.

Ten paciencia y veremos si uno de los expertos en SQL Server puede darte mejores soluciones.

[Libras]

Código SQL:

CREATE TABLE #temp
(
eventid INT,
site_id INT,
identity_id INT,
parity INT,
event_type INT,
eventdate datetime
)
 
INSERT INTO #temp VALUES (383325,   20  ,17045, NULL    ,270,'2012-06-05 00:00:00.000') 
INSERT INTO #temp VALUES (383327,   20  ,17045, NULL    ,270,'2012-06-05 00:00:00.000') 
INSERT INTO #temp VALUES (383329,   20  ,17045, NULL    ,270,'2012-06-06 00:00:00.000') 
INSERT INTO #temp VALUES (383338,   20  ,17045, NULL    ,270,'2012-10-26 00:00:00.000') 
INSERT INTO #temp VALUES (383339,   20  ,17045, NULL    ,270,'2012-10-26 00:00:00.000') 
INSERT INTO #temp VALUES (383341,   20  ,17045, NULL    ,270,'2012-10-27 00:00:00.000') 
INSERT INTO #temp VALUES (383343,   20  ,17050, NULL    ,270,'2012-04-29 00:00:00.000') 
INSERT INTO #temp VALUES (383345,   20  ,17050, NULL    ,270,'2012-04-29 00:00:00.000') 
INSERT INTO #temp VALUES (383346,   20  ,17050,NULL ,270,'2012-04-30 00:00:00.000') 
INSERT INTO #temp VALUES (383354,   20  ,17053, NULL    ,270,'2012-05-02 00:00:00.000') 
INSERT INTO #temp VALUES (383356,   20  ,17053, NULL    ,270,'2012-05-02 00:00:00.000')
INSERT INTO #temp VALUES (383358,   20  ,17053, NULL    ,270,'2012-05-03 00:00:00.000') 
INSERT INTO #temp VALUES (383365,   20  ,17053, NULL    ,270,'2012-09-22 00:00:00.000')
INSERT INTO #temp VALUES (383367,   20  ,17053, NULL    ,270,'2012-09-22 00:00:00.000') 
INSERT INTO #temp VALUES (383369,   20  ,17053, NULL    ,270,'2012-09-23 00:00:00.000') 
INSERT INTO #temp VALUES (383373,   20  ,17060, NULL    ,270,'2012-05-01 00:00:00.000') 
INSERT INTO #temp VALUES (383374,   20  ,17060, NULL    ,270,'2012-05-01 00:00:00.000') 
INSERT INTO #temp VALUES (383376,   20  ,17060, NULL    ,270,'2012-05-02 00:00:00.000') 
INSERT INTO #temp VALUES (383382,   20  ,17060, NULL    ,270,'2012-09-23 00:00:00.000') 
INSERT INTO #temp VALUES (383384,   20  ,17060, NULL    ,270,'2012-09-23 00:00:00.000') 
INSERT INTO #temp VALUES (383385,   20  ,17060, NULL    ,270,'2012-09-24 00:00:00.000') 
INSERT INTO #temp VALUES (383387,   20  ,17060, NULL    ,270,'2012-10-13 00:00:00.000') 
INSERT INTO #temp VALUES (383388,   20  ,17060, NULL    ,270,'2012-10-13 00:00:00.000') 
INSERT INTO #temp VALUES (383390,   20  ,17060, NULL    ,270,'2012-10-14 00:00:00.000') 
INSERT INTO #temp VALUES (383394,   20  ,17066, NULL    ,270,'2012-05-10 00:00:00.000') 
INSERT INTO #temp VALUES (383396,   20  ,17066, NULL    ,270,'2012-05-10 00:00:00.000') 
INSERT INTO #temp VALUES (383397,   20  ,17066, NULL    ,270,'2012-05-11 00:00:00.000') 
INSERT INTO #temp VALUES (383404,   20, 17066,  NULL    ,270,'2012-10-02 00:00:00.000') 
INSERT INTO #temp VALUES (383405,   20, 17066,  NULL    ,270,'2012-10-02 00:00:00.000') 
 
 
CREATE TABLE #resultado
(
comentario VARCHAR(20),
identity_id INT,
fecha datetime
)
 
SELECT DISTINCT identity_id,IDENTITY(INT,1,1) AS rn INTO #temp2 FROM #temp 
DECLARE @x INT
DECLARE @y INT
DECLARE @minimo datetime
DECLARE @IDENTITY INT
DECLARE @dias INT
DECLARE @contador INT
DECLARE @contador1 INT
SET @x=1
SET @contador=1
while @x<=(SELECT COUNT(*) FROM #temp2)
BEGIN
SELECT @IDENTITY=identity_id FROM #temp2 WHERE rn=@x
SELECT *,IDENTITY(INT,1,1) AS rn INTO #temp3 FROM #temp WHERE identity_id=@IDENTITY
    SET @y=1
    SET @contador=1
    SET @contador1=1
    SELECT @minimo=MIN(eventdate) FROM #temp3
    INSERT INTO #resultado
    SELECT CONVERT(VARCHAR(20),@contador) + ' Evento', identity_id,NULL FROM #temp2 WHERE rn=@x     
    while @y<=(SELECT COUNT(*) FROM #temp3)
    BEGIN
     SELECT @dias=datediff(dd,@minimo,eventdate) FROM #temp3 WHERE rn=@y
     IF @dias>6
       BEGIN    
         SET @contador=@contador+1 
         SET @contador1=1
         INSERT INTO #resultado
         SELECT CONVERT(VARCHAR(20),@contador) + ' Evento', identity_id,NULL FROM #temp3 WHERE rn=@y
         SELECT @minimo=MIN(eventdate) FROM #temp3 WHERE rn=@y
         INSERT INTO #resultado
         SELECT CONVERT(VARCHAR(20),@contador1) + ' monta', identity_id,eventdate FROM #temp3 WHERE rn=@y  
         SET @contador1=@contador1+1
       END
     ELSE
        BEGIN
          INSERT INTO #resultado
          SELECT CONVERT(VARCHAR(20),@contador1) + ' monta', identity_id,eventdate FROM #temp3 WHERE rn=@y  
          SET @contador1=@contador1+1
        END
     SET @y=@y+1
    END
DROP TABLE #temp3
SET @x=@x+1
END
 
SELECT * FROM #resultado
 
DROP TABLE #temp2
DELETE FROM #resultado
 

el resutado es:

comentario identity_id fecha
-------------------- ----------- -----------------------
1 Evento 17045 NULL
1 monta 17045 2012-06-05 00:00:00.000
2 monta 17045 2012-06-05 00:00:00.000
3 monta 17045 2012-06-06 00:00:00.000
2 Evento 17045 NULL
1 monta 17045 2012-10-26 00:00:00.000
2 monta 17045 2012-10-26 00:00:00.000
3 monta 17045 2012-10-27 00:00:00.000
1 Evento 17050 NULL
1 monta 17050 2012-04-29 00:00:00.000
2 monta 17050 2012-04-29 00:00:00.000
3 monta 17050 2012-04-30 00:00:00.000
1 Evento 17053 NULL
1 monta 17053 2012-05-02 00:00:00.000
2 monta 17053 2012-05-02 00:00:00.000
3 monta 17053 2012-05-03 00:00:00.000
2 Evento 17053 NULL
1 monta 17053 2012-09-22 00:00:00.000
2 monta 17053 2012-09-22 00:00:00.000
3 monta 17053 2012-09-23 00:00:00.000
1 Evento 17060 NULL
1 monta 17060 2012-05-01 00:00:00.000
2 monta 17060 2012-05-01 00:00:00.000
3 monta 17060 2012-05-02 00:00:00.000
2 Evento 17060 NULL
1 monta 17060 2012-09-23 00:00:00.000
2 monta 17060 2012-09-23 00:00:00.000
3 monta 17060 2012-09-24 00:00:00.000
3 Evento 17060 NULL
1 monta 17060 2012-10-13 00:00:00.000
2 monta 17060 2012-10-13 00:00:00.000
3 monta 17060 2012-10-14 00:00:00.000
1 Evento 17066 NULL
1 monta 17066 2012-05-10 00:00:00.000
2 monta 17066 2012-05-10 00:00:00.000
3 monta 17066 2012-05-11 00:00:00.000
2 Evento 17066 NULL
1 monta 17066 2012-10-02 00:00:00.000
2 monta 17066 2012-10-02 00:00:00.000


saludos!!