Query para unificar datos?

SAP Business One
1

Estimados buen dia. Quiero solicitar de su conocimiento. Tengo este query que es de un libro de compras.

DECLARE @FechaInicial AS DATETIME 
DECLARE @FechaFinal AS DATETIME 

SELECT @FechaInicial = T0.F_RefDate, @FechaFinal = T0.T_RefDate 
FROM OFPR T0 
WHERE T0.F_RefDate = '[%0]' AND T0.T_RefDate = '[%1]' 
SET @FechaInicial = CONVERT(DATETIME, '[%0]', 112) 
SET @FechaFinal = CONVERT(DATETIME, '[%1]', 112)  

SELECT 
    A.DocNum,
    ch.Cheque,
    'OPCH' as Tabla,
    a.TaxDate as Fecha_Contabilizacion,
    CASE 
        WHEN A51.Seriesname LIKE '%F.Esp%' THEN 'FE' 
        WHEN B.U_TIPO = 'P' THEN 'PQ'
        ELSE 'FAC' 
    END as Tipo,
    A.CardName as 'CardName', -- Agregado CardName
    CASE 
        WHEN A.Series = '64' THEN (SELECT CA.CardName FROM OCRD CA INNER JOIN PCH1 BA 
            ON CA.CardCode=BA.U_Proveedor AND CA.CardType='S' AND B.LineNum=Ba.LineNum AND Ba.DocEntry=B.DocEntry)
        ELSE ISNULL(A1.U_NombreE, c.CardName) 
    END as 'Deudor/Acreedor',
    CASE 
        WHEN A.Series = '64' THEN (SELECT CA.U_NIT FROM OCRD CA INNER JOIN PCH1 BA 
            ON CA.CardCode=BA.U_Proveedor AND CA.CardType='S' AND B.LineNum=Ba.LineNum AND Ba.DocEntry=B.DocEntry)
        ELSE ISNULL(A1.U_NIT, c.U_NIT) 
    END as 'NIT',
    CASE 
        WHEN A.Series = '64' THEN 0 ELSE A.DocNum 
    END as NumeroDocumento,
    CASE 
        WHEN A.Series = '64' THEN ISNULL(B.U_Serie , 'Error') ELSE A.U_Serie 
    END as Serie,
    CASE 
        WHEN A.Series <> '64' THEN A.NumAtCard ELSE B.U_NumeroFc 
    END as NumeroReferencia,
    SUM(CASE WHEN B.U_TIPO = 'B' THEN B.LineTotal ELSE 0 END) as Bien,
    SUM(CASE WHEN B.U_TIPO = 'S' THEN B.LineTotal ELSE 0 END) as Servicio,
    SUM(CASE WHEN B.U_TIPO = 'C' THEN B.LineTotal ELSE 0 END) as Combustible,
    SUM(CASE WHEN B.U_TIPO IN ('E','P') THEN B.LineTotal ELSE 0 END) as Exento,
    SUM(CASE WHEN B.U_TIPO = 'I' AND B.TaxOnly = 'Y' THEN B.Price ELSE 0 END) as Importacion,
    SUM(CASE WHEN B.U_Tipo = 'I' AND B.TaxOnly = 'Y' THEN B.VatSum ELSE B.VatSum END) as IVA,
    SUM(CASE WHEN B.U_TIPO IN ('B', 'S', 'C', 'E', 'P', 'I') THEN B.LineTotal ELSE 0 END + CASE WHEN B.U_Tipo = 'I' AND B.TaxOnly = 'Y' THEN B.VatSum ELSE B.VatSum END) as Total
FROM
    PCH1 B 
    RIGHT JOIN OPCH A ON A.DocEntry = B.DocEntry
    LEFT JOIN OCRD C ON A.CardCode = C.CardCode
    LEFT JOIN NNM1 A51 ON A.series = A51.series
    LEFT JOIN RPC1 B1 ON B.DocEntry = B1.BaseEntry AND B.ObjType = B1.BaseType AND B.LineNum = B1.BaseLine
    LEFT JOIN ORPC B2 ON B1.DocEntry = B2.DocEntry
    LEFT JOIN [@PROV] A1 ON A.U_Prov = A1.Code
    LEFT OUTER JOIN (SELECT a.DocEntry, b.CheckNum as Cheque FROM VPM2 a LEFT OUTER JOIN VPM1 b ON a.DocNum = b.DocNum) ch ON A.Docnum = ch.docentry
WHERE
    A.DocDate >= @FechaInicial
    AND A.DocDate <= @FechaFinal
    AND B.U_TIPO <> 'N'
    AND A.DocSubType = '--'
    AND ISNULL(B2.Series, 0) IN (-1, 0)
GROUP BY 
    A.TaxDate,
    A.CardCode,
    A.CardName,
    C.U_NIT,
    A.DocNum,
    A.U_Serie,
    A.Series,
    A.NumAtCard,
    A.U_Prov,
    B.U_Tipo,
    A51.seriesname,
    C.QryGroup1,
    A1.Name,
    A1.U_NombreE,
    A1.U_NIT,
    B.U_Proveedor,
    C.CardCode,
    C.CardName,
    B.U_NumeroFc,
    B.U_Serie,
    B.LineTotal,
    B.LineNum,
    B.DocEntry,
    ch.cheque

UNION ALL

SELECT DISTINCT
    A.DocNum as DocNum,
    0,
    'ORPC' as Tabla,
    A.TaxDate as Fecha_Contabilizacion,
    'NC' as Tipo,
    A.CardName as 'CardName', -- Agregado CardName
    CASE WHEN C.QryGroup1 = 'Y' THEN ISNULL(A1.U_NombreE, A1.Name) ELSE A.CardName END as 'Deudor/Acreedor',
    CASE WHEN C.QryGroup1 = 'Y' THEN A1.U_NIT ELSE C.U_NIT END as 'NIT',
    A.DocNum as NumeroDocumento,
    A.U_Serie,
    A.NumAtCard as NumeroReferencia,
    SUM(CASE WHEN B.U_TIPO = 'B' THEN B.LineTotal * -1 ELSE 0 END) as Bien,
    SUM(CASE WHEN B.U_TIPO = 'S' THEN B.LineTotal * -1 ELSE 0 END) as Servicio,
    SUM(CASE WHEN B.U_TIPO = 'C' THEN B.LineTotal * -1 ELSE 0 END) as Combustible,
    SUM(CASE WHEN B.U_TIPO IN ('E', 'P') THEN B.LineTotal * -1 ELSE 0 END) as Exento,
    SUM(CASE WHEN B.U_TIPO = 'I' THEN 0 ELSE 0 END) as Importacion,
    SUM(CASE WHEN B.U_Tipo = 'I' THEN B.LineTotal * -1 ELSE B.VatSum * -1 END) as IVA,
    SUM(CASE WHEN B.U_TIPO IN ('B', 'S', 'C', 'E', 'P', 'I') THEN B.LineTotal * -1 ELSE 0 END + CASE WHEN B.U_Tipo = 'I' THEN B.LineTotal * -1 ELSE B.VatSum * -1 END) as Total
FROM
    ORPC A 
    JOIN RPC1 B ON A.DocEntry = B.DocEntry
    JOIN OCRD C ON A.CardCode = C.CardCode
    LEFT JOIN [@PROV] A1 ON A.U_Prov = A1.Code
WHERE
    A.DocDate >= @FechaInicial
    AND A.DocDate <= @FechaFinal
    AND B.U_TIPO <> 'N'
    AND A.Series = 102
    AND ISNULL(A.NumAtCard, '') <> ''
GROUP BY 
    A.TaxDate,
    A.CardCode,
    A.CardName,
    C.U_NIT,
    A.DocNum,
    A.U_Serie,
    A.NumAtCard,
    C.QryGroup1,
    A1.Name,
    A1.U_NombreE,
    A1.U_NIT;

Y me da esta informacion (imagen)

image
image1304×143 10.7 KB

El problema es que, por ejemplo lo marcado es de una factura de gasolina, esta la registran en dos lineas. Una registra el valor de combustible y la otra el excento. Necesito que me unifique ese dato a una sola linea, guiandonos de que es el mismo numero de documento, lo mismo me pasa con facturas de energia electrica, que llevan un registro en servicio y el otro en exento.

Podrian ayudarme para modificar este query. De antemano les agradezco el apoyo.

2

Hola @Gerson_de_Paz por aquí te comparto una versión actualizada de tu código, nos comentas si con esto se soluciona tu inconveniente, Saludos.

DECLARE @FechaInicial AS DATETIME 
DECLARE @FechaFinal AS DATETIME 

SELECT @FechaInicial = T0.F_RefDate, @FechaFinal = T0.T_RefDate 
FROM OFPR T0 
WHERE T0.F_RefDate = '[%0]' AND T0.T_RefDate = '[%1]' 
SET @FechaInicial = CONVERT(DATETIME, '[%0]', 112) 
SET @FechaFinal = CONVERT(DATETIME, '[%1]', 112)  

SELECT 
    A.DocNum,
    ch.Cheque,
    'OPCH' as Tabla,
    a.TaxDate as Fecha_Contabilizacion,
    CASE 
        WHEN A51.Seriesname LIKE '%F.Esp%' THEN 'FE' 
        WHEN B.U_TIPO = 'P' THEN 'PQ'
        ELSE 'FAC' 
    END as Tipo,
    A.CardName as 'CardName',
    CASE 
        WHEN A.Series = '64' THEN (SELECT CA.CardName FROM OCRD CA INNER JOIN PCH1 BA 
            ON CA.CardCode=BA.U_Proveedor AND CA.CardType='S' AND B.LineNum=Ba.LineNum AND Ba.DocEntry=B.DocEntry)
        ELSE ISNULL(A1.U_NombreE, c.CardName) 
    END as 'Deudor/Acreedor',
    CASE 
        WHEN A.Series = '64' THEN (SELECT CA.U_NIT FROM OCRD CA INNER JOIN PCH1 BA 
            ON CA.CardCode=BA.U_Proveedor AND CA.CardType='S' AND B.LineNum=Ba.LineNum AND Ba.DocEntry=B.DocEntry)
        ELSE ISNULL(A1.U_NIT, c.U_NIT) 
    END as 'NIT',
    CASE 
        WHEN A.Series = '64' THEN 0 ELSE A.DocNum 
    END as NumeroDocumento,
    CASE 
        WHEN A.Series = '64' THEN ISNULL(B.U_Serie , 'Error') ELSE A.U_Serie 
    END as Serie,
    CASE 
        WHEN A.Series <> '64' THEN A.NumAtCard ELSE B.U_NumeroFc 
    END as NumeroReferencia,
    SUM(CASE WHEN B.U_TIPO = 'B' THEN B.LineTotal ELSE 0 END) as Bien,
    SUM(CASE WHEN B.U_TIPO = 'S' THEN B.LineTotal ELSE 0 END) as Servicio,
    SUM(CASE WHEN B.U_TIPO = 'C' THEN B.LineTotal ELSE 0 END) as Combustible,
    SUM(CASE WHEN B.U_TIPO IN ('E','P') THEN B.LineTotal ELSE 0 END) as Exento,
    SUM(CASE WHEN B.U_TIPO = 'I' AND B.TaxOnly = 'Y' THEN B.Price ELSE 0 END) as Importacion,
    SUM(CASE WHEN B.U_Tipo = 'I' AND B.TaxOnly = 'Y' THEN B.VatSum ELSE B.VatSum END) as IVA,
    SUM(CASE WHEN B.U_TIPO IN ('B', 'S', 'C', 'E', 'P', 'I') THEN B.LineTotal ELSE 0 END + CASE WHEN B.U_Tipo = 'I' AND B.TaxOnly = 'Y' THEN B.VatSum ELSE B.VatSum END) as Total
FROM
    PCH1 B 
    RIGHT JOIN OPCH A ON A.DocEntry = B.DocEntry
    LEFT JOIN OCRD C ON A.CardCode = C.CardCode
    LEFT JOIN NNM1 A51 ON A.series = A51.series
    LEFT JOIN RPC1 B1 ON B.DocEntry = B1.BaseEntry AND B.ObjType = B1.BaseType AND B.LineNum = B1.BaseLine
    LEFT JOIN ORPC B2 ON B1.DocEntry = B2.DocEntry
    LEFT JOIN [@PROV] A1 ON A.U_Prov = A1.Code
    LEFT OUTER JOIN (SELECT a.DocEntry, b.CheckNum as Cheque FROM VPM2 a LEFT OUTER JOIN VPM1 b ON a.DocNum = b.DocNum) ch ON A.Docnum = ch.docentry
WHERE
    A.DocDate >= @FechaInicial
    AND A.DocDate <= @FechaFinal
    AND B.U_TIPO <> 'N'
    AND A.DocSubType = '--'
    AND ISNULL(B2.Series, 0) IN (-1, 0)
GROUP BY 
    A.TaxDate,
    A.CardCode,
    A.CardName,
    C.U_NIT,
    A.DocNum,
    A.U_Serie,
    A.Series,
    A.NumAtCard,
    A.U_Prov,
    B.U_Tipo,
    A51.seriesname,
    C.QryGroup1,
    A1.Name,
    A1.U_NombreE,
    A1.U_NIT,
    B.U_Proveedor,
    C.CardCode,
    C.CardName,
    B.U_NumeroFc,
    B.U_Serie,
    ch.cheque

HAVING 
    SUM(CASE WHEN B.U_TIPO IN ('C', 'S', 'E') THEN B.LineTotal ELSE 0 END) > 0

UNION ALL

SELECT DISTINCT
    A.DocNum as DocNum,
    0,
    'ORPC' as Tabla,
    A.TaxDate as Fecha_Contabilizacion,
    'NC' as Tipo,
    A.CardName as 'CardName',
    CASE WHEN C.QryGroup1 = 'Y' THEN ISNULL(A1.U_NombreE, A1.Name) ELSE A.CardName END as 'Deudor/Acreedor',
    CASE WHEN C.QryGroup1 = 'Y' THEN A1.U_NIT ELSE C.U_NIT END as 'NIT',
    A.DocNum as NumeroDocumento,
    A.U_Serie,
    A.NumAtCard as NumeroReferencia,
    SUM(CASE WHEN B.U_TIPO = 'B' THEN B.LineTotal * -1 ELSE 0 END) as Bien,
    SUM(CASE WHEN B.U_TIPO = 'S' THEN B.LineTotal * -1 ELSE 0 END) as Servicio,
    SUM(CASE WHEN B.U_TIPO = 'C' THEN B.LineTotal * -1 ELSE 0 END) as Combustible,
    SUM(CASE WHEN B.U_TIPO IN ('E', 'P') THEN B.LineTotal * -1 ELSE 0 END) as Exento,
    SUM(CASE WHEN B.U_TIPO = 'I' THEN 0 ELSE 0 END) as Importacion,
    SUM(CASE WHEN B.U_Tipo = 'I' THEN B.LineTotal * -1 ELSE B.VatSum * -1 END) as IVA,
    SUM(CASE WHEN B.U_TIPO IN ('B', 'S', 'C', 'E', 'P', 'I') THEN B.LineTotal * -1 ELSE 0 END + CASE WHEN B.U_Tipo = 'I' THEN B.LineTotal * -1 ELSE B.VatSum * -1 END) as Total
FROM
    ORPC A 
    JOIN RPC1 B ON A.DocEntry = B.DocEntry
    JOIN OCRD C ON A.CardCode = C.CardCode
    LEFT JOIN [@PROV] A1 ON A.U_Prov = A1.Code
WHERE
    A.DocDate >= @FechaInicial
    AND A.DocDate <= @FechaFinal
    AND B.U_TIPO <> 'N'
    AND A.Series = 102
    AND ISNULL(A.NumAtCard, '') <> ''
GROUP BY 
    A.TaxDate,
    A.CardCode,
    A.CardName,
    C.U_NIT,
    A.DocNum,
    A.U_Serie,
    A.NumAtCard,
    C.QryGroup1,
    A1.Name,
    A1.U_NombreE,
    A1.U_NIT,
    B.DocEntry,
    B.LineNum

HAVING 
    SUM(CASE WHEN B.U_TIPO IN ('C', 'S', 'E') THEN B.LineTotal ELSE 0 END) > 0;
3

Me da este error:
1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Column ‘PCH1.LineNum’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Column ‘PCH1.DocEntry’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 3). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement ‘Alertas recibidas’ (OAIB) (s) could not be prepared.

4

Hola, he realizado una actualización al código (Respuesta Anterior), he corregido las columnas que causaban error (PCH1.LineNum y PCH1.DocEntry) en el GROUP BY. Esto asegura que SQL Server ejecute el query correctamente sin violar las reglas de agrupación. Nos comentas como va todo, Saludos.

5

Evita el doble ingreso de factura usando la columna de “GASTOS ADICIONALES”.

6

¿Si los datos los quieres totalizados por documento, porque incluyes la información de las líneas?