SQL Server - ayuda con una consulta muy dificil

Hola a todos: tengo la siguiente tabla Visitas,que guarda las visitas realizadas a varios sitios

tabla:Visitas

id ip inicio fin url
1 100 10/08/2010 09:10:00 juego/index.asp
2 100 10/08/2010 09:15:00 musica/index.asp
3 100 10/08/2010 09:17:00 video/index.asp
4 100 10/08/2010 10:05:00 musica/buscar.asp
5 100 10/08/2010 10:11:00 fotos/index.asp
6 100 10/08/2010 10:12:00 musica/index.asp
7 100 10/08/2010 10:30:00 musica/index.asp
8 100 10/08/2010 10:45:00 video/index.asp
9 100 10/08/2010 10:47:00 fotos/ver.asp
10 100 10/08/2010 11:00:00 fotos/index.asp
11 100 10/08/2010 11:10:00 juego/listado.asp

y necesito realizar una consulta en sql server que me arroje el siguiente resultado :duracion en minutos entre que entra a una pagina y hasta que sale, pudiendo entrar y salir mas de una vez al mismo sitio y por supuesto que tambien puede haber otros ip que realicen las consultas a los sitios.

ip duracion url
100 120 juego
100 50 musica
100 88 video
100 36 fotos
100 18 musica

Espero que la explicacion de lo que quiero haya sido clara, habra alguna forma de hacerlo o como debería almacenar los datos? en definitiva quiero saber que que sitio se consultaron, quien y cuanto duró .gracias.

Sera un tanto complicado si no tienes identificado el registro de cuando es ENTRADA y cuando SALIDA, ya que solo almacenas una fecha.

¿Se asume que el primer registro es ENTRADA y el siguiente inmediato es SALIDA, agrupado por IP y SITIO?

id ip --entrada------------------salida-----------------------url
1 100 10/08/2010 09:10:00 -----------------------------juego/index.asp
2 100 10/08/2010 09:15:00 -----------------------------musica/index.asp
3 100 10/08/2010 09:17:00 -----------------------------video/index.asp
4 100 ---------------------------10/08/2010 10:05:00-- musica/buscar.asp
5 100 10/08/2010 10:11:00 -----------------------------fotos/index.asp
6 100 10/08/2010 10:12:00 -----------------------------musica/index.asp
7 100 ---------------------------10/08/2010 10:30:00-- musica/index.asp
8 100 ---------------------------10/08/2010 10:45:00 --video/index.asp
9 100 ---------------------------10/08/2010 10:47:00---fotos/ver.asp
10 100 --------------------------10/08/2010 11:00:00--juego/buscar.asp
11 100 -------------------------10/08/2010 11:10:00---juego/listado.asp
ahora espero que se entienda, lo que quiero hacer es registrar el ingreso de un usuario a un sitio, y el tiempo que permanece, entonces cuando ingresa (siempre por la pag index de un sitio) guarda fecha y hora y lo mismo cuando sale, quisiera hacer una consulta que me diga por ip a que sitios entró y cuanto tiempo, desde ya muchas gracias

Buenos días Kriss, Entiendo muy bien lo que quieres hacer, pero llevar este tipo de estadísticas puede ser muy complicado. Lo ideal sería lo siguiente:

Supongamos que una ip 127.0.0.1 entra en la página juegos/index.asp a las 12:00:00, en ese caso deberíamos tener algo como esto

id|ip|inicio|fin|url
1|127.0.0.1|12:00:00|null|juegos/index.asp

Ahora supongamos que a las 12:05 la misma IP entra an la página música/index.asp. En este momento suceden dos cosas, primero se está saliendo de la página juegos/index.asp y se está ingresando a una nueva página, por lo que la tabla de visitas entonces debería quedas así:

id|ip|inicio|fin|url
1|127.0.0.1|12:00:00|12:05:00|juegos/index.asp
2|127.0.0.1|12:05:00|null|musica/index.asp

Es decir, se busca en la tabla de visitas aquel registro con la ip y la página de la que se está saliendo y se registra la hora de salida, enseguida se genera un nuevo registro de inicio para la nueva página. La hora de salida de una es la hora de entrada a la otra.

Hasta aquí creo que no debe haber problema, pero ahora viene lo complicado: imagina que a las 12:07:00 el usuario de la ip 127.0.0.1 cierra sin más su explorador. el registro marcado con el id 2 ya no va a tener hora de salida. por lo que no se podría conciderar a la hora de realizar tus estadísticas.

Otra complicación: Imagina que la ip 127.0.0.1 ingresa a la 01:00:00 a la página musica/index.asp, la tabla de visitas entonces quedaría así:

id|ip|inicio|fin|url
1|127.0.0.1|12:00:00|12:05:00|juegos/index.asp
2|127.0.0.1|12:05:00|null|musica/index.asp
3|127.0.0.1|01:00:00|null|musica/index.asp

A las 01:10:00 sale de la página de musica y entra a la de juegos, en este caso existen dos registros (id = 2 y id = 3) en donde pudieras colocar la hora de salida, pero lo correcto es colocarlo en el registro 3, debiendo quedar tu tabla así

id|ip|inicio|fin|url
1|127.0.0.1|12:00:00|12:05:00|juegos/index.asp
2|127.0.0.1|12:05:00|null|musica/index.asp
3|127.0.0.1|01:00:00|01:10:00|musica/index.asp
4|127.0.0.1|01:10:00|null|juegos/index.asp

Ahora si, con los datos de la tabla, sólo sería necesario hacer una diferencia entre la hora de inicio y fin para obtener el tiempo que permaneció en la tabla

Select ip, convert(varchar, fin - inicio, 108) as duracion, url from visitas
where fin is not null

con lo que obtendrías algo como esto

ip|duracion|url
127.0.0.1|00:05;00|juegos/index.asp
127.0.0.1|00:10;00|musica/index.asp

Quizas alguien más tenga otra idea, pero espero que esto te pueda servir de algo.

Saludos
Leo