COMPRAS -TRANSACCIONES UTILIZADAS POR CLIENTE

DECLARE @F_INICIO DATETIME

DECLARE @F_FIN DATETIME

SET @F_INICIO = '2024-07-16 21:03:55'

SET @F_FIN = '2024-07-31 14:11:10'

;with emision

as (

      select e.c_emisor, co.c_compra, (co.n_unidades/100) AS n_unidades, ROW_NUMBER() OVER (PARTITION BY e.c_emisor ORDER BY n_unidades desc) AS NumeroFila

      from v_rl_cs_emisor_compra e

   INNER join v_cs_contador_compra co on co.c_compra = e.c_compra

)

--select c_emisor, c_compra, n_unidades, NumeroFila from emision where NumeroFila = 1

SELECT e.d_rfc, c_compra, n_unidades, NumeroFila, COUNT(1) AS Transacciones

FROM dbo.mm_comprobante c

inner JOIN dbo.mm_comprobante_timbre t ON t.c_comprobante = c.c_comprobante

inner join dbo.cs_emisor e ON e.c_emisor = c.c_emisor

INNER JOIN emision emi ON emi.c_emisor = e.c_emisor

WHERE 1 = 1

AND c.f_fecha_facturacion >= @F_INICIO

AND c.f_fecha_facturacion <= @F_FIN

AND emi.NumeroFila = 1

GROUP BY e.d_rfc,

emi.c_compra,

emi.n_unidades,

emi.NumeroFila

UNION ALL

SELECT e.d_rfc, c_compra, n_unidades, NumeroFila, COUNT(1) AS Transacciones

FROM dbo.mm_comprobante_pago c

inner JOIN dbo.mm_comprobante_pago_timbre t ON t.c_comprobante_pago = c.c_comprobante_pago

inner join dbo.cs_emisor e ON e.c_emisor = c.c_emisor

INNER JOIN emision emi ON emi.c_emisor = e.c_emisor

WHERE 1 = 1

AND c.f_fecha_comprobante >= @F_INICIO

AND c.f_fecha_comprobante <= @F_FIN

AND emi.NumeroFila = 1

GROUP BY e.d_rfc,

emi.c_compra,

emi.n_unidades,

emi.NumeroFila

UNION ALL

SELECT e.d_rfc, c_compra, n_unidades, NumeroFila, COUNT(1) AS Transacciones

FROM dbo.mm_retencion c

inner JOIN dbo.mm_retencion_timbre t ON t.c_retencion = c.c_retencion

inner join dbo.cs_emisor e ON e.c_emisor = c.c_emisor

INNER JOIN emision emi ON emi.c_emisor = e.c_emisor

WHERE 1 = 1

AND c.f_expedicion >= @F_INICIO

AND c.f_expedicion <= @F_FIN

AND emi.NumeroFila = 1

GROUP BY e.d_rfc,

emi.c_compra,

emi.n_unidades,

emi.NumeroFila

UNION ALL

SELECT e.d_rfc, c_compra, n_unidades, NumeroFila, COUNT(1) AS Transacciones

FROM dbo.mm_comprobante_secpri c

inner JOIN dbo.mm_comprobante_secpri_timbre t ON t.c_comprobante_secpri = c.c_comprobante_secpri

inner join dbo.cs_emisor e ON e.c_emisor = c.c_emisor

INNER JOIN emision emi ON emi.c_emisor = e.c_emisor

WHERE 1 = 1

AND c.f_fecha_comprobante >= @F_INICIO

AND c.f_fecha_comprobante <= @F_FIN

AND emi.NumeroFila = 1

GROUP BY e.d_rfc,

emi.c_compra,

emi.n_unidades,

emi.NumeroFila

order by e.d_rfc