Código SQL:
Ver originalCREATE 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!!