SQL Server - CONSULTA SQL CON FILTROS DE HORAS Y SUMA DE VENTAS

Buen día,
Necesito hacer una consulta en una sola tabla que me muestre las ventas por intervalos de horas, por decir, de 03:00 a 04:00 = ventas, de 04:00 a 05:00 = ventas y así sucesivamente.

Adjunto la imagen del ejemplo que necesito hacer igual pero mi idea es hacerlo en un form de vb6 llenando un flaxgrid con la consulta sql.

Espero me puedan ayudar por favor.
Gracias

ESTA ES LA TABLA QUE USO EN SQL SERVER 2000

Buenas imágenes, ¿Qué avance llevas de tu query?

Como voy?
No he podido avanzar porque necesito ayuda!
No se como hacerle para que me de los resultados como en la primer imagen según las horas!!

Pues dependerá de tu skill en SQL Server, ¿Qué nivel tienes?

A ver Isaias, tu crees que si tuviera la experiencia necesaria para poder hacer este trabajo tuviera la necesidad de publicarlo y sobre todo pedir ayuda? No verdad! Entonces deja tus respuestas sarcásticas y tan relativamente carentes de ayuda.

Si no pretendes ayudar mejor rumbale a otro foro o mejor aun ocúpate en otras cosas en serio porque si tuvieras el conocimiento y las ganas de ayudar, ya me hubieras puesto la solución en vez de contestarme con preguntas como lo haces en la mayorías de tus colaboraciones en este sitio.

Señores colaboradores de estos foros, les pido de la manera mas atenta me puedan ayudar a resolver este pendiente por favor!!

Gracias!

Eduardo

Este y muchos foros mas, tienen sus reglas y una de ellas, si no la mas importante, es que NO hacemos chambas al 100%, tampoco tareas escolares.

Por otro lado, no te molestes, estamos (estoy) intentando ayudarte

El primer pic que publicaste al parecer es algo referente a un BI tipo PowerView de Excel

Si tu quieres sacar el total de ventas por HORA, es tan simple como:

SELECT DATEPART(HH, tufecha) as Hora, SUM(ventas) as Total
FROM VENTAS
GROUP BY 1

Pero no creo que sea ese el objetivo, al parecer, tu quieres algo mas, que no puedes o no sabes como explicar, ¿cierto?

Cierto y quizás no me he explicado bien lo que necesito, te pido una disculpa.

Mira, tengo una aplicación que registra las vueltas que dan unas unidades (camiones de ruta urbana) y aparte que registras las horas de salida y llegada de cada una, también registra las ventas de Boletos y pagos con tarjetas Feria.

Este reporte que necesito se llama "Horarios" imagen de arriba, ahí te va mostrando las ventas que se realizaron según el intervalo de horas:

de 03:00 a 04:00 = ventas
de 04:00 a 05:00 = ventas y así sucesivamente

el detalle es que no todas las unidades salen y llegan igual a la misma hora! y solo salen 4 veces por turno:

HSV1 = hora de salida vuelta 1 = tipo varchar (ejemplo: 03:01)
HSV2 = hora de salida vuelta 2 = tipo varchar (ejemplo: 03:01)
HSV3 = hora de salida vuelta 3 = tipo varchar (ejemplo: 03:01)
HSV4 = hora de salida vuelta 4 = tipo varchar (ejemplo: 03:01)

Obviamente por cada salida habrá ventas, mismas que necesito que el reporte vaya sumando según la hora.

Estos campos son los que registran la suma de todas las ventas por vuelta:

PasajerosV1 = tipo money
PasajerosV2 = tipo money
PasajerosV3 = tipo money
PasajerosV4 = tipo money

Eso es lo que no se como hacerle o si ocupo hacer una Función o SQL Expression Fields no se como hacerle en Crystal Reports 9

Espero ya con esto me haya entendido mejor!
Saludos!

Aquí te muestro un reporte que acabo de hacer para que todavía te des una mejor idea de lo que necesito:



Estas son las FUNCIONES que hice tratando de sacar los resultados:




ADJUNTO EL REPORTE HECHO EN CRYSTAL REPORTS 9 (Horarios.rar)

• Horarios.rar(13,4 KB)

Bueno

No conozco Crystal Reports, te sugiero le des una vuelta a REPORTIN SERVICES..

Para agrupar por horas es muy sencillo, checa esto:

SELEC SUM(CASE WHEN DATEPART(HH, TuFecha) BETWEEN 3 AND 4 THEN Venta END) AS "3 a 4",
SUM(CASE WHEN DATEPART(HH, TuFecha) BETWEEN 5 AND 6 THEN Venta END) AS "5 a 6",
SUM(CASE WHEN DATEPART(HH, TuFecha) BETWEEN 7 AND 8 THEN Venta END) AS "7 a 8",

etc.

He armado este Query pero no me resulta porque solo me suma la primera vuelta "HSV1" como podre saber o hacer para que SQL sepa cual sumar según la hora?

SELECT UNIDAD AS UND,TURNO AS T,
CONVERT(VARCHAR(5), HSV1, 108) AS HSV1,CONVERT(VARCHAR(5), HSV2, 108) AS HSV2,
CONVERT(VARCHAR(5), HSV3, 108) AS HSV3,CONVERT(VARCHAR(5), HSV4, 108) AS HSV4,
SUM(CASE WHEN HSV1 BETWEEN '03:00' AND '03:59' THEN VENDIDOSV1 ELSE 0 END) AS '03/04',
SUM(CASE WHEN HSV1 BETWEEN '04:00' AND '04:59' THEN VENDIDOSV1 ELSE 0 END) AS '04/05',
SUM(CASE WHEN HSV1 BETWEEN '05:00' AND '05:59' THEN VENDIDOSV1 ELSE 0 END) AS '05/06',
SUM(CASE WHEN HSV1 BETWEEN '06:00' AND '06:59' THEN VENDIDOSV1 ELSE 0 END) AS '06/07',
SUM(CASE WHEN HSV1 BETWEEN '07:00' AND '07:59' THEN VENDIDOSV1 ELSE 0 END) AS '07/08',
SUM(CASE WHEN HSV1 BETWEEN '08:00' AND '08:59' THEN VENDIDOSV1 ELSE 0 END) AS '08/09',
SUM(CASE WHEN HSV1 BETWEEN '09:00' AND '09:59' THEN VENDIDOSV1 ELSE 0 END) AS '09/10',
SUM(CASE WHEN HSV1 BETWEEN '10:00' AND '10:59' THEN VENDIDOSV1 ELSE 0 END) AS '10/11',
SUM(CASE WHEN HSV1 BETWEEN '11:00' AND '11:59' THEN VENDIDOSV1 ELSE 0 END) AS '11/12',
SUM(CASE WHEN HSV1 BETWEEN '12:00' AND '12:59' THEN VENDIDOSV1 ELSE 0 END) AS '12/13',
SUM(CASE WHEN HSV1 BETWEEN '13:00' AND '13:59' THEN VENDIDOSV1 ELSE 0 END) AS '13/14',
SUM(CASE WHEN HSV1 BETWEEN '14:00' AND '14:59' THEN VENDIDOSV1 ELSE 0 END) AS '14/15',
SUM(CASE WHEN HSV1 BETWEEN '15:00' AND '15:59' THEN VENDIDOSV1 ELSE 0 END) AS '15/16',
SUM(CASE WHEN HSV1 BETWEEN '16:00' AND '16:59' THEN VENDIDOSV1 ELSE 0 END) AS '16/17',
SUM(CASE WHEN HSV1 BETWEEN '17:00' AND '17:59' THEN VENDIDOSV1 ELSE 0 END) AS '17/18',
SUM(CASE WHEN HSV1 BETWEEN '18:00' AND '18:59' THEN VENDIDOSV1 ELSE 0 END) AS '18/19',
SUM(CASE WHEN HSV1 BETWEEN '19:00' AND '19:59' THEN VENDIDOSV1 ELSE 0 END) AS '19/20',
SUM(CASE WHEN HSV1 BETWEEN '20:00' AND '20:59' THEN VENDIDOSV1 ELSE 0 END) AS '20/21',
SUM(CASE WHEN HSV1 BETWEEN '21:00' AND '21:59' THEN VENDIDOSV1 ELSE 0 END) AS '21/22',
SUM(CASE WHEN HSV1 BETWEEN '22:00' AND '22:59' THEN VENDIDOSV1 ELSE 0 END) AS '22/23',
SUM(CASE WHEN HSV1 BETWEEN '23:00' AND '23:59' THEN VENDIDOSV1 ELSE 0 END) AS '23/24'
FROM TRACKINGBOLETAJE
WHERE FECHA = '20140722'
AND RUTA = 'R-217'
AND TURNO = '1'
GROUP BY UNIDAD,TURNO,HSV1,HSV2,HSV3,HSV4
ORDER BY HSV1

Bueno, creo que sumando por cada HSV1,HSV2,HSV3,HSV4 y hacer una UNION, podrias lograr el objetivo.


SELECT
CONVERT(VARCHAR(5), HSV1, 108) AS HSV1,
......etc, etc, etc........
SUM (CASE..........etc, etc, etc..........
FROM TRACKINGBOLETAJE
WHERE FECHA >= '20140722' AND FECHA <= '20140722'
AND RUTA = 'R-217'
AND TURNO = '1'
GROUP BY UNIDAD,TURNO,HSV1
UNION
SELECT
CONVERT(VARCHAR(5), HSV2, 108) AS HSV2,
......etc, etc, etc........
SUM (CASE..........etc, etc, etc..........
FROM TRACKINGBOLETAJE
WHERE FECHA >= '20140722' AND FECHA <= '20140722'
AND RUTA = 'R-217'
AND TURNO = '1'
GROUP BY UNIDAD,TURNO,HSV2
UNION
SELECT
CONVERT(VARCHAR(5), HSV3, 108) AS HSV3,
......etc, etc, etc........
SUM (CASE..........etc, etc, etc..........
FROM TRACKINGBOLETAJE
WHERE FECHA >= '20140722' AND FECHA <= '20140722'
AND RUTA = 'R-217'
AND TURNO = '1'
GROUP BY UNIDAD,TURNO,HSV3

Las ventas se van registrando por vueltas:

HSV1 "Hora Salida Vuelta 1" + PASAJEROSV1 = total de ventas registradas en esa primer vuelta
HSV2 "Hora Salida Vuelta 1" + PASAJEROSV2 = total de ventas registradas en esa segunda vuelta
HSV3 "Hora Salida Vuelta 1" + PASAJEROSV3 = total de ventas registradas en esa tercer vuelta
HSV4 "Hora Salida Vuelta 1" + PASAJEROSV4 = total de ventas registradas en esa cuarta vuelta

Entonces las vueltas varían por cada unidad porque ninguna sale a la misma hora, cada una unidad (camión) sale con 7 MINUTOS DE DIFERENCIA entra cada salida y CADA VUELTA DURA 3 HORAS.

Vamos a poner el ejemplo de 2 camiones:

Camion1 - HSV1 = 03:00 AM --- LLEGA = 06:00 AM --- PASAJEROSV1 = 100
Camion2 - HSV1 = 03:07 AM --- LLEGA = 06:07 AM --- PASAJEROSV1 = 90

Camion1 - HSV2 = 06:14 AM --- LLEGA = 09:14 AM --- PASAJEROSV2 = 150
Camion2 - HSV2 = 06:24 AM --- LLEGA = 09:24 AM --- PASAJEROSV2 = 100

Camion1 - HSV3 = 09:30 AM --- LLEGA = 12:30 PM --- PASAJEROSV3 = 50
Camion2 - HSV3 = 09:40 AM --- LLEGA = 12:40 PM --- PASAJEROSV3 = 70

Camion1 - HSV4 = 13:00 PM --- LLEGA = 16:00 PM --- PASAJEROSV4 = 150
Camion2 - HSV4 = 13:10 PM --- LLEGA = 16:10 PM --- PASAJEROSV4 = 100

Te fijas que cada vez se va desfazando la primer hora de salida hasta la ultima hora?
Imaginate a que hora llegaria un Camion que salga a las 18:00 pm?
Por eso este reporte se necesita que indique las ventas por RANGO DE HORAS de 03:00 AM que es cuando sale el primer camion a ruta, hasta las 21:00 PM que sale el ultimo camion que es la ultima vuelta del dia y llegaria a las 23:59 PM

Espero ahora si lo haya dejado mas claro!
Saludos!!

hice este código UNION pero me repite las filas:

SELECT UNIDAD AS UND,TURNO AS T,
CASE WHEN HSV1 <> '' THEN CONVERT(VARCHAR(5), HSV1, 108) ELSE '--:--' END AS HSV1,
CASE WHEN HSV2 <> '' THEN CONVERT(VARCHAR(5), HSV2, 108) ELSE '--:--' END AS HSV2,
CASE WHEN HSV3 <> '' THEN CONVERT(VARCHAR(5), HSV3, 108) ELSE '--:--' END AS HSV3,
SUM(CASE WHEN HSV1 BETWEEN '03:00' AND '03:59' THEN VENDIDOSV1 ELSE 0 END) AS '03:04',
SUM(CASE WHEN HSV1 BETWEEN '04:00' AND '04:59' THEN VENDIDOSV1 ELSE 0 END) AS '04:05',
SUM(CASE WHEN HSV1 BETWEEN '05:00' AND '05:59' THEN VENDIDOSV1 ELSE 0 END) AS '05:06',
SUM(CASE WHEN HSV1 BETWEEN '06:00' AND '06:59' THEN VENDIDOSV1 ELSE 0 END) AS '06:07',
SUM(CASE WHEN HSV1 BETWEEN '07:00' AND '07:59' THEN VENDIDOSV1 ELSE 0 END) AS '07:08',
SUM(CASE WHEN HSV1 BETWEEN '08:00' AND '08:59' THEN VENDIDOSV1 ELSE 0 END) AS '08:09',
SUM(CASE WHEN HSV1 BETWEEN '09:00' AND '09:59' THEN VENDIDOSV1 ELSE 0 END) AS '09:10',
SUM(CASE WHEN HSV1 BETWEEN '10:00' AND '10:59' THEN VENDIDOSV1 ELSE 0 END) AS '10:11',
SUM(CASE WHEN HSV1 BETWEEN '11:00' AND '11:59' THEN VENDIDOSV1 ELSE 0 END) AS '11:12',
SUM(CASE WHEN HSV1 BETWEEN '12:00' AND '12:59' THEN VENDIDOSV1 ELSE 0 END) AS '12:13',
SUM(CASE WHEN HSV1 BETWEEN '13:00' AND '13:59' THEN VENDIDOSV1 ELSE 0 END) AS '13:14',
SUM(CASE WHEN HSV1 BETWEEN '14:00' AND '14:59' THEN VENDIDOSV1 ELSE 0 END) AS '14:15',
SUM(CASE WHEN HSV1 BETWEEN '15:00' AND '15:59' THEN VENDIDOSV1 ELSE 0 END) AS '15:16',
SUM(CASE WHEN HSV1 BETWEEN '16:00' AND '16:59' THEN VENDIDOSV1 ELSE 0 END) AS '16:17',
SUM(CASE WHEN HSV1 BETWEEN '17:00' AND '17:59' THEN VENDIDOSV1 ELSE 0 END) AS '17:18',
SUM(CASE WHEN HSV1 BETWEEN '18:00' AND '18:59' THEN VENDIDOSV1 ELSE 0 END) AS '18:19',
SUM(CASE WHEN HSV1 BETWEEN '19:00' AND '19:59' THEN VENDIDOSV1 ELSE 0 END) AS '19:20',
SUM(CASE WHEN HSV1 BETWEEN '20:00' AND '20:59' THEN VENDIDOSV1 ELSE 0 END) AS '20:21',
SUM(CASE WHEN HSV1 BETWEEN '21:00' AND '21:59' THEN VENDIDOSV1 ELSE 0 END) AS '21:22',
SUM(CASE WHEN HSV1 BETWEEN '22:00' AND '22:59' THEN VENDIDOSV1 ELSE 0 END) AS '22:23',
SUM(CASE WHEN HSV1 BETWEEN '23:00' AND '23:59' THEN VENDIDOSV1 ELSE 0 END) AS '23:24'
FROM TRACKINGBOLETAJE
WHERE FECHA = '20140723'
AND RUTA = 'R-217'
AND TURNO IN ('1','2')
GROUP BY UNIDAD,TURNO,HSV1,HSV2,HSV3

UNION

SELECT UNIDAD AS UND,TURNO AS T,
CASE WHEN HSV1 <> '' THEN CONVERT(VARCHAR(5), HSV1, 108) ELSE '--:--' END AS HSV1,
CASE WHEN HSV2 <> '' THEN CONVERT(VARCHAR(5), HSV2, 108) ELSE '--:--' END AS HSV2,
CASE WHEN HSV3 <> '' THEN CONVERT(VARCHAR(5), HSV3, 108) ELSE '--:--' END AS HSV3,
SUM(CASE WHEN HSV2 BETWEEN '03:00' AND '03:59' THEN VENDIDOSV2 ELSE 0 END) AS '03:04',
SUM(CASE WHEN HSV2 BETWEEN '04:00' AND '04:59' THEN VENDIDOSV2 ELSE 0 END) AS '04:05',
SUM(CASE WHEN HSV2 BETWEEN '05:00' AND '05:59' THEN VENDIDOSV2 ELSE 0 END) AS '05:06',
SUM(CASE WHEN HSV2 BETWEEN '06:00' AND '06:59' THEN VENDIDOSV2 ELSE 0 END) AS '06:07',
SUM(CASE WHEN HSV2 BETWEEN '07:00' AND '07:59' THEN VENDIDOSV2 ELSE 0 END) AS '07:08',
SUM(CASE WHEN HSV2 BETWEEN '08:00' AND '08:59' THEN VENDIDOSV2 ELSE 0 END) AS '08:09',
SUM(CASE WHEN HSV2 BETWEEN '09:00' AND '09:59' THEN VENDIDOSV2 ELSE 0 END) AS '09:10',
SUM(CASE WHEN HSV2 BETWEEN '10:00' AND '10:59' THEN VENDIDOSV2 ELSE 0 END) AS '10:11',
SUM(CASE WHEN HSV2 BETWEEN '11:00' AND '11:59' THEN VENDIDOSV2 ELSE 0 END) AS '11:12',
SUM(CASE WHEN HSV2 BETWEEN '12:00' AND '12:59' THEN VENDIDOSV2 ELSE 0 END) AS '12:13',
SUM(CASE WHEN HSV2 BETWEEN '13:00' AND '13:59' THEN VENDIDOSV2 ELSE 0 END) AS '13:14',
SUM(CASE WHEN HSV2 BETWEEN '14:00' AND '14:59' THEN VENDIDOSV2 ELSE 0 END) AS '14:15',
SUM(CASE WHEN HSV2 BETWEEN '15:00' AND '15:59' THEN VENDIDOSV2 ELSE 0 END) AS '15:16',
SUM(CASE WHEN HSV2 BETWEEN '16:00' AND '16:59' THEN VENDIDOSV2 ELSE 0 END) AS '16:17',
SUM(CASE WHEN HSV2 BETWEEN '17:00' AND '17:59' THEN VENDIDOSV2 ELSE 0 END) AS '17:18',
SUM(CASE WHEN HSV2 BETWEEN '18:00' AND '18:59' THEN VENDIDOSV2 ELSE 0 END) AS '18:19',
SUM(CASE WHEN HSV2 BETWEEN '19:00' AND '19:59' THEN VENDIDOSV2 ELSE 0 END) AS '19:20',
SUM(CASE WHEN HSV2 BETWEEN '20:00' AND '20:59' THEN VENDIDOSV2 ELSE 0 END) AS '20:21',
SUM(CASE WHEN HSV2 BETWEEN '21:00' AND '21:59' THEN VENDIDOSV2 ELSE 0 END) AS '21:22',
SUM(CASE WHEN HSV2 BETWEEN '22:00' AND '22:59' THEN VENDIDOSV2 ELSE 0 END) AS '22:23',
SUM(CASE WHEN HSV2 BETWEEN '23:00' AND '23:59' THEN VENDIDOSV2 ELSE 0 END) AS '23:24'
FROM TRACKINGBOLETAJE
WHERE FECHA = '20140723'
AND RUTA = 'R-217'
AND TURNO IN ('1','2')
GROUP BY UNIDAD,TURNO,HSV1,HSV2,HSV3

UNION

SELECT UNIDAD AS UND,TURNO AS T,
CASE WHEN HSV1 <> '' THEN CONVERT(VARCHAR(5), HSV1, 108) ELSE '--:--' END AS HSV1,
CASE WHEN HSV2 <> '' THEN CONVERT(VARCHAR(5), HSV2, 108) ELSE '--:--' END AS HSV2,
CASE WHEN HSV3 <> '' THEN CONVERT(VARCHAR(5), HSV3, 108) ELSE '--:--' END AS HSV3,
SUM(CASE WHEN HSV3 BETWEEN '03:00' AND '03:59' THEN VENDIDOSV3 ELSE 0 END) AS '03:04',
SUM(CASE WHEN HSV3 BETWEEN '04:00' AND '04:59' THEN VENDIDOSV3 ELSE 0 END) AS '04:05',
SUM(CASE WHEN HSV3 BETWEEN '05:00' AND '05:59' THEN VENDIDOSV3 ELSE 0 END) AS '05:06',
SUM(CASE WHEN HSV3 BETWEEN '06:00' AND '06:59' THEN VENDIDOSV3 ELSE 0 END) AS '06:07',
SUM(CASE WHEN HSV3 BETWEEN '07:00' AND '07:59' THEN VENDIDOSV3 ELSE 0 END) AS '07:08',
SUM(CASE WHEN HSV3 BETWEEN '08:00' AND '08:59' THEN VENDIDOSV3 ELSE 0 END) AS '08:09',
SUM(CASE WHEN HSV3 BETWEEN '09:00' AND '09:59' THEN VENDIDOSV3 ELSE 0 END) AS '09:10',
SUM(CASE WHEN HSV3 BETWEEN '10:00' AND '10:59' THEN VENDIDOSV3 ELSE 0 END) AS '10:11',
SUM(CASE WHEN HSV3 BETWEEN '11:00' AND '11:59' THEN VENDIDOSV3 ELSE 0 END) AS '11:12',
SUM(CASE WHEN HSV3 BETWEEN '12:00' AND '12:59' THEN VENDIDOSV3 ELSE 0 END) AS '12:13',
SUM(CASE WHEN HSV3 BETWEEN '13:00' AND '13:59' THEN VENDIDOSV3 ELSE 0 END) AS '13:14',
SUM(CASE WHEN HSV3 BETWEEN '14:00' AND '14:59' THEN VENDIDOSV3 ELSE 0 END) AS '14:15',
SUM(CASE WHEN HSV3 BETWEEN '15:00' AND '15:59' THEN VENDIDOSV3 ELSE 0 END) AS '15:16',
SUM(CASE WHEN HSV3 BETWEEN '16:00' AND '16:59' THEN VENDIDOSV3 ELSE 0 END) AS '16:17',
SUM(CASE WHEN HSV3 BETWEEN '17:00' AND '17:59' THEN VENDIDOSV3 ELSE 0 END) AS '17:18',
SUM(CASE WHEN HSV3 BETWEEN '18:00' AND '18:59' THEN VENDIDOSV3 ELSE 0 END) AS '18:19',
SUM(CASE WHEN HSV3 BETWEEN '19:00' AND '19:59' THEN VENDIDOSV3 ELSE 0 END) AS '19:20',
SUM(CASE WHEN HSV3 BETWEEN '20:00' AND '20:59' THEN VENDIDOSV3 ELSE 0 END) AS '20:21',
SUM(CASE WHEN HSV3 BETWEEN '21:00' AND '21:59' THEN VENDIDOSV3 ELSE 0 END) AS '21:22',
SUM(CASE WHEN HSV3 BETWEEN '22:00' AND '22:59' THEN VENDIDOSV3 ELSE 0 END) AS '22:23',
SUM(CASE WHEN HSV3 BETWEEN '23:00' AND '23:59' THEN VENDIDOSV3 ELSE 0 END) AS '23:24'
FROM TRACKINGBOLETAJE
WHERE FECHA = '20140723'
AND RUTA = 'R-217'
AND TURNO IN ('1','2')
GROUP BY UNIDAD,TURNO,HSV1,HSV2,HSV3
ORDER BY TURNO,HSV1

ESTE ES EL RESULTADO:

Si revisas el código de ejemplo que te puse, yo hice UN query por cada vuelta y luego las uni

Si lo revise y lo probe pero solo me muestra las siguientes columnas:

UND/T/HSV1/03:04/04/:05 ETC ETC ETC

Y yo necesito que me muestre todas las primeras:

UND/T/HSV1/HSV2/HSV3/HSV4/03:04/04/:05 ETC ETC ETC

Espero me haya explicado!

Tal vez el problema esta en el diseño de la tabla, tengo esta duda

HSV1, HSV2, HSV3, HSV4

¿Nunca podrán tener el mismo horario?, ¿Cierto?

Así es, nunca podrán tener el mismo horario!!

SELECT UNIDAD AS UND,TURNO AS T,
CASE WHEN HSV1 <> '' THEN CONVERT(VARCHAR(5), HSV1, 108) ELSE '--:--' END AS HSV1,
CASE WHEN HSV2 <> '' THEN CONVERT(VARCHAR(5), HSV2, 108) ELSE '--:--' END AS HSV2,
CASE WHEN HSV3 <> '' THEN CONVERT(VARCHAR(5), HSV3, 108) ELSE '--:--' END AS HSV3,
SUM(CASE WHEN HSV1 BETWEEN '03:00' AND '03:59' THEN VENDIDOSV1 ELSE 0 END) AS '03:04',
SUM(CASE WHEN HSV1 BETWEEN '04:00' AND '04:59' THEN VENDIDOSV1 ELSE 0 END) AS '04:05',
SUM(CASE WHEN HSV1 BETWEEN '05:00' AND '05:59' THEN VENDIDOSV1 ELSE 0 END) AS '05:06',
SUM(CASE WHEN HSV1 BETWEEN '06:00' AND '06:59' THEN VENDIDOSV1 ELSE 0 END) AS '06:07',
SUM(CASE WHEN HSV1 BETWEEN '07:00' AND '07:59' THEN VENDIDOSV1 ELSE 0 END) AS '07:08',
SUM(CASE WHEN HSV1 BETWEEN '08:00' AND '08:59' THEN VENDIDOSV1 ELSE 0 END) AS '08:09',
SUM(CASE WHEN HSV1 BETWEEN '09:00' AND '09:59' THEN VENDIDOSV1 ELSE 0 END) AS '09:10',
SUM(CASE WHEN HSV1 BETWEEN '10:00' AND '10:59' THEN VENDIDOSV1 ELSE 0 END) AS '10:11',
SUM(CASE WHEN HSV1 BETWEEN '11:00' AND '11:59' THEN VENDIDOSV1 ELSE 0 END) AS '11:12',
SUM(CASE WHEN HSV1 BETWEEN '12:00' AND '12:59' THEN VENDIDOSV1 ELSE 0 END) AS '12:13',
SUM(CASE WHEN HSV1 BETWEEN '13:00' AND '13:59' THEN VENDIDOSV1 ELSE 0 END) AS '13:14',
SUM(CASE WHEN HSV1 BETWEEN '14:00' AND '14:59' THEN VENDIDOSV1 ELSE 0 END) AS '14:15',
SUM(CASE WHEN HSV1 BETWEEN '15:00' AND '15:59' THEN VENDIDOSV1 ELSE 0 END) AS '15:16',
SUM(CASE WHEN HSV1 BETWEEN '16:00' AND '16:59' THEN VENDIDOSV1 ELSE 0 END) AS '16:17',
SUM(CASE WHEN HSV1 BETWEEN '17:00' AND '17:59' THEN VENDIDOSV1 ELSE 0 END) AS '17:18',
SUM(CASE WHEN HSV1 BETWEEN '18:00' AND '18:59' THEN VENDIDOSV1 ELSE 0 END) AS '18:19',
SUM(CASE WHEN HSV1 BETWEEN '19:00' AND '19:59' THEN VENDIDOSV1 ELSE 0 END) AS '19:20',
SUM(CASE WHEN HSV1 BETWEEN '20:00' AND '20:59' THEN VENDIDOSV1 ELSE 0 END) AS '20:21',
SUM(CASE WHEN HSV1 BETWEEN '21:00' AND '21:59' THEN VENDIDOSV1 ELSE 0 END) AS '21:22',
SUM(CASE WHEN HSV1 BETWEEN '22:00' AND '22:59' THEN VENDIDOSV1 ELSE 0 END) AS '22:23',
SUM(CASE WHEN HSV1 BETWEEN '23:00' AND '23:59' THEN VENDIDOSV1 ELSE 0 END) AS '23:24',
SUM(CASE WHEN HSV2 BETWEEN '03:00' AND '03:59' THEN VENDIDOSV2 ELSE 0 END) AS '03:04',
SUM(CASE WHEN HSV2 BETWEEN '04:00' AND '04:59' THEN VENDIDOSV2 ELSE 0 END) AS '04:05',
SUM(CASE WHEN HSV2 BETWEEN '05:00' AND '05:59' THEN VENDIDOSV2 ELSE 0 END) AS '05:06',
SUM(CASE WHEN HSV2 BETWEEN '06:00' AND '06:59' THEN VENDIDOSV2 ELSE 0 END) AS '06:07',
SUM(CASE WHEN HSV2 BETWEEN '07:00' AND '07:59' THEN VENDIDOSV2 ELSE 0 END) AS '07:08',
SUM(CASE WHEN HSV2 BETWEEN '08:00' AND '08:59' THEN VENDIDOSV2 ELSE 0 END) AS '08:09',
SUM(CASE WHEN HSV2 BETWEEN '09:00' AND '09:59' THEN VENDIDOSV2 ELSE 0 END) AS '09:10',
SUM(CASE WHEN HSV2 BETWEEN '10:00' AND '10:59' THEN VENDIDOSV2 ELSE 0 END) AS '10:11',
SUM(CASE WHEN HSV2 BETWEEN '11:00' AND '11:59' THEN VENDIDOSV2 ELSE 0 END) AS '11:12',
SUM(CASE WHEN HSV2 BETWEEN '12:00' AND '12:59' THEN VENDIDOSV2 ELSE 0 END) AS '12:13',
SUM(CASE WHEN HSV2 BETWEEN '13:00' AND '13:59' THEN VENDIDOSV2 ELSE 0 END) AS '13:14',
SUM(CASE WHEN HSV2 BETWEEN '14:00' AND '14:59' THEN VENDIDOSV2 ELSE 0 END) AS '14:15',
SUM(CASE WHEN HSV2 BETWEEN '15:00' AND '15:59' THEN VENDIDOSV2 ELSE 0 END) AS '15:16',
SUM(CASE WHEN HSV2 BETWEEN '16:00' AND '16:59' THEN VENDIDOSV2 ELSE 0 END) AS '16:17',
SUM(CASE WHEN HSV2 BETWEEN '17:00' AND '17:59' THEN VENDIDOSV2 ELSE 0 END) AS '17:18',
SUM(CASE WHEN HSV2 BETWEEN '18:00' AND '18:59' THEN VENDIDOSV2 ELSE 0 END) AS '18:19',
SUM(CASE WHEN HSV2 BETWEEN '19:00' AND '19:59' THEN VENDIDOSV2 ELSE 0 END) AS '19:20',
SUM(CASE WHEN HSV2 BETWEEN '20:00' AND '20:59' THEN VENDIDOSV2 ELSE 0 END) AS '20:21',
SUM(CASE WHEN HSV2 BETWEEN '21:00' AND '21:59' THEN VENDIDOSV2 ELSE 0 END) AS '21:22',
SUM(CASE WHEN HSV2 BETWEEN '22:00' AND '22:59' THEN VENDIDOSV2 ELSE 0 END) AS '22:23',
SUM(CASE WHEN HSV2 BETWEEN '23:00' AND '23:59' THEN VENDIDOSV2 ELSE 0 END) AS '23:24',
SUM(CASE WHEN HSV3 BETWEEN '03:00' AND '03:59' THEN VENDIDOSV3 ELSE 0 END) AS '03:04',
SUM(CASE WHEN HSV3 BETWEEN '04:00' AND '04:59' THEN VENDIDOSV3 ELSE 0 END) AS '04:05',
SUM(CASE WHEN HSV3 BETWEEN '05:00' AND '05:59' THEN VENDIDOSV3 ELSE 0 END) AS '05:06',
SUM(CASE WHEN HSV3 BETWEEN '06:00' AND '06:59' THEN VENDIDOSV3 ELSE 0 END) AS '06:07',
SUM(CASE WHEN HSV3 BETWEEN '07:00' AND '07:59' THEN VENDIDOSV3 ELSE 0 END) AS '07:08',
SUM(CASE WHEN HSV3 BETWEEN '08:00' AND '08:59' THEN VENDIDOSV3 ELSE 0 END) AS '08:09',
SUM(CASE WHEN HSV3 BETWEEN '09:00' AND '09:59' THEN VENDIDOSV3 ELSE 0 END) AS '09:10',
SUM(CASE WHEN HSV3 BETWEEN '10:00' AND '10:59' THEN VENDIDOSV3 ELSE 0 END) AS '10:11',
SUM(CASE WHEN HSV3 BETWEEN '11:00' AND '11:59' THEN VENDIDOSV3 ELSE 0 END) AS '11:12',
SUM(CASE WHEN HSV3 BETWEEN '12:00' AND '12:59' THEN VENDIDOSV3 ELSE 0 END) AS '12:13',
SUM(CASE WHEN HSV3 BETWEEN '13:00' AND '13:59' THEN VENDIDOSV3 ELSE 0 END) AS '13:14',
SUM(CASE WHEN HSV3 BETWEEN '14:00' AND '14:59' THEN VENDIDOSV3 ELSE 0 END) AS '14:15',
SUM(CASE WHEN HSV3 BETWEEN '15:00' AND '15:59' THEN VENDIDOSV3 ELSE 0 END) AS '15:16',
SUM(CASE WHEN HSV3 BETWEEN '16:00' AND '16:59' THEN VENDIDOSV3 ELSE 0 END) AS '16:17',
SUM(CASE WHEN HSV3 BETWEEN '17:00' AND '17:59' THEN VENDIDOSV3 ELSE 0 END) AS '17:18',
SUM(CASE WHEN HSV3 BETWEEN '18:00' AND '18:59' THEN VENDIDOSV3 ELSE 0 END) AS '18:19',
SUM(CASE WHEN HSV3 BETWEEN '19:00' AND '19:59' THEN VENDIDOSV3 ELSE 0 END) AS '19:20',
SUM(CASE WHEN HSV3 BETWEEN '20:00' AND '20:59' THEN VENDIDOSV3 ELSE 0 END) AS '20:21',
SUM(CASE WHEN HSV3 BETWEEN '21:00' AND '21:59' THEN VENDIDOSV3 ELSE 0 END) AS '21:22',
SUM(CASE WHEN HSV3 BETWEEN '22:00' AND '22:59' THEN VENDIDOSV3 ELSE 0 END) AS '22:23',
SUM(CASE WHEN HSV3 BETWEEN '23:00' AND '23:59' THEN VENDIDOSV3 ELSE 0 END) AS '23:24'
FROM TRACKINGBOLETAJE
WHERE FECHA >= '20140722'
AND FECHA <= '20140722'
AND RUTA = 'R-217'
AND TURNO = '1'
GROUP BY UNIDAD,TURNO,HSV1,HSV2,HSV3,HSV4
ORDER BY HSV1

Ese query ya lo había hecho pero es lo mismo, me repite las columnas y lo necesito igual a la primera imagen que publique en este tema.