SELECT
-- TRANSACCIONES
FECHA AS FECHA_DE_TRANSAC,
CAST(MONTH (FECHA) AS CHAR) AS MES,
CAST(YEAR (FECHA) AS CHAR) AS AÑO,
-- (COALESCE(T.[NRO COMPROBANTE],'') + '-' + COALESCE([IPO DE COMPROBANTE],'')) as NRO_COMPROBANTE_TRANSAC,
T.[NRO COMPROBANTE] AS NRO_COMPROBANTE_TRANSAC,
C.CODIGO AS COD_CLIENTE,
T."RAZON SOCIAL" AS RAZON_SOCIAL,
T."LISTA DE PRECIO" AS LISTA_DE_PRECIO,
"NOMBRE LISTA" AS NOMBRE_LISTA,
"PROV COSTO REF" AS PROV_COSTO_REF,
"DIR DE ENTREGA" AS DIR_DE_ENTREGA,
"NOMBRE ZONA" AS NOMBRE_ZONA,
"COD ZONA" AS COD_ZONA,
"CLASI CLIENTE" AS CLASE_CLIENTE,
SUCURSAL,
"NOMBRE SUCURSAL" AS NOMBRE_SUCURSAL,
-- CLIENTES
"QUE HACE" AS QUE_HACE,
"OBS DE VENTA" AS OBS_DE_VENTA,
"OBS PAGO" AS OBS_PAGO,
"DIR VISITA COMERCIAL" AS DIR_VISITA_COMERCIAL,
REVENDEDOR,
"GRUPO EMPRESARIO" AS GRUPO_EMPRESARIO,
"CUPO DE CREDITO" AS CUPO_DE_CREDITO,
"CONDICION DE PAGO" AS CONDICION_DE_PAGO,
COUNT("COD ARTICULO") AS COUNT_COD_ARTICULO,
SUM(BONIFICACION) AS SUM_BONIFICACION, -- SUM
SUM(DESCUENTO) AS SUM_DESCUENTO, -- SUM
SUM(T.CANT) AS SUM_CANT, -- SUM
SUM("IMPORTE SIN IVA") AS SUM_IMPORTE_SIN_IVA, -- SUM
SUM(CASE
WHEN UM = 'Kilogramo' THEN T.CANT
ELSE T.CANT * T.PESO
END) AS SUM_KILOS-- SUM
FROM TABLERO.dbo.TRANSACCIONES T
LEFT JOIN TABLERO.dbo.CLIENTES C ON T."COD CLIENTE" = C.CODIGO
LEFT JOIN TABLERO.dbo.ARTICULOS A ON T."COD ARTICULO" = A.CODIGO
WHERE FECHA BETWEEN
'2019-01-01' -- Inicio del mes del año anterior
AND DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) -- Último día del mes actual
AND [IPO DE COMPROBANTE] = 'FAC' -- Último día del mes actual
-- AND CLASIFICACION = 'C'
GROUP BY
FECHA,
CAST(MONTH(FECHA) AS CHAR),
CAST(YEAR(FECHA) AS CHAR),
T."NRO COMPROBANTE",
[IPO DE COMPROBANTE],
C.CODIGO,
T."RAZON SOCIAL",
T."LISTA DE PRECIO",
"NOMBRE LISTA",
"COSTO REFERENCIA",
"PROV COSTO REF",
"DIR DE ENTREGA",
"NOMBRE ZONA",
"COD ZONA",
"CLASI CLIENTE",
SUCURSAL,
"NOMBRE SUCURSAL",
"QUE HACE",
"OBS DE VENTA",
"OBS PAGO",
"DIR VISITA COMERCIAL",
REVENDEDOR,
"GRUPO EMPRESARIO",
"CUPO DE CREDITO",
"CONDICION DE PAGO"