SELECT MONTH(fecha) AS MES
,SUM(f_total) AS TOTAL
FROM Facturacion.Opr_Facturas
WHERE YEAR(fecha) = 2013
GROUP BY MONTH(fecha)
-- Meses de Forma Horizontal
SELECT 'TOTALES' AS MES, [1] Ene, [2] Feb, [3] Mar, [4] Abr, [5] May, [6] Jun,
[7] Jul, [8] Ago, [9] Sep, [10] Oct, [11] Nov, [12] Dic
FROM (
SELECT MONTH(fecha) AS MES
,SUM(f_total) AS TOTAL
FROM Facturacion.Opr_Facturas
WHERE YEAR(fecha) = 2013
GROUP BY MONTH(fecha)
) V PIVOT ( SUM(TOTAL) FOR MES IN ([1], [2], [3], [4], [5],
[6], [7], [8], [9], [10], [11], [12]) ) as PT
-- Totales por Tarifa
SELECT id_tarifa AS TARIFA
,COUNT(id_tarifa) AS TOTAL
FROM Padron.Cat_Padron
WHERE id_cuenta > 0
AND id_estatus = 1
GROUP BY id_tarifa
-- Por tarifa Horizontal
SELECT 'TOTALES' AS TARIFA, [1] DOMESTICO, ISNULL([2],0) URBANO, ISNULL([3],0) RESIDENCIAL, [4] HOTELERA, [5] COMERCIAL, [6] INDUSTRIAL,
[7] SERVS
FROM (
SELECT id_tarifa AS TARIFA
,COUNT(id_tarifa) AS TOTAL
FROM Padron.Cat_Padron
WHERE id_cuenta > 0
AND id_estatus = 1
GROUP BY id_tarifa
) V PIVOT ( SUM(TOTAL) FOR TARIFA IN ([1], [2], [3], [4], [5], [6], [7]) ) as PT
SELECT 'TOTALES' AS TARIFA,
[1], [2], [3], [4], [5], [6], [7]
FROM
( SELECT id_tarifa AS TARIFA
,COUNT(id_tarifa) AS TOTAL
FROM Padron.Cat_Padron
WHERE id_cuenta > 0
AND id_estatus = 1
GROUP BY id_tarifa) AS SourceTable
PIVOT
(
SUM(TOTAL)
FOR TARIFA IN ([1], [2], [3], [4], [5], [6], [7])
) AS PivotTable;
SELECT 'TARIFAS' AS TARIFA,
[1], [2], [3], [4], [5], [6], [7]
FROM
( SELECT id_tipousuario AS TARIFA
,descripcion AS DESCRIPCION
FROM Padron.Cat_TiposUsuario
WHERE inactivo = 0) AS SourceTable
PIVOT
(MAX(DESCRIPCION)
FOR TARIFA IN ([1], [2], [3], [4], [5], [6], [7])
) AS PivotTable;
No hay comentarios:
Publicar un comentario