Lineas de registros se duplican

[former_member228470]

Hola a todos

Tengo un reporte que me calcula las comisiones.  Hay facturas que se le aplica un pago a cuenta y tambien un recibo.  Cuando es pago a cuenta se reconcilia.  El reporte me tendria que leer las dos tablas para poder traerme las facturas pagadas

Cuando pasa eso, se me duplican o triplican los registros y me altera el total del pago de las comisiones.  Ejemplo este caso triplico el registro porque se hicieron dos pago a cuenta y un recibo

Sé que existe una funcion en SQL que permite traer una linea y en el caso de los numeros de recibo y reconciliacion los trae en un campo separado por coma pero realmente no lo recuerdo

En ese caso quedaria en una sola linea que es lo que requiero,

Gracias de antemano

Sara

[felipe_loyolarodriguez]

Sería de mucha utilidad que colocaras el script SQL que utilizas para obtener esa info y poder corregir el problema de la duplicidad.

Saludos

Felipe

[former_member228470]

/* SELECT FROM [DBO].[OINV] T0 */      
DECLARE @Desde AS datetime   
/*WHERE*/   SET @Desde= /* T0.DOCDATE */ '[%0]'           

/* SELECT FROM [DBO].[OINV] T0 */      
DECLARE @Hasta AS datetime  
/*WHERE*/      SET @Hasta=  /* T0.DOCDATE */ '[%1]'            

/* SELECT FROM [DBO].[OSLP] T2 */       
DECLARE @Vendedor AS varchar(50)
/*WHERE*/      SET @Vendedor=  /* T2.SLPNAME */ '[%2]'

SELECT  t0.DocNum '#Doc', T0.CardCode 'Cod.Cliente', T0.CardName 'Cliente',  T0.Docdate 'Fecha Doc.',
t2.slpname 'Vendedor',  (select case when t0.DocType = 'S' then T1.AcctCode else t1.itemcode end) 'Referencia',
T1.Quantity 'Cantidad', T1.price 'Precio', T1.LineTotal 'Total Linea', 
(Case WHEN DATEDIFF(DD,T0.DOCDATE,T3.DocDate ) > 120 AND T4.GroupCode NOT IN (107,123,124,125,126) THEN 0 
    WHEN DATEDIFF(DD,T0.DOCDATE,T3.DocDate ) > 120 AND T4.GroupCode     IN (107,123,124,125,126) THEN T1.Commission 
    WHEN DATEDIFF(DD,T0.DOCDATE,T3.DocDate ) <= 120 AND T4.GroupCode    IN (107,123,124,125,126) THEN T1.Commission 
    WHEN DATEDIFF(DD,T0.DOCDATE,T3.DocDate ) <=120 AND T4.GroupCode NOT IN (107,123,124,125,126) THEN T1.Commission END) AS '%Comision', 

(Case WHEN T0.doctype = 'S' AND   DATEDIFF(DD,T0.DOCDATE,T3.DocDate) > 120 AND T4.GroupCode  NOT IN(107,123,124,125,126)  THEN 0 
    WHEN T0.doctype = 'S' AND ((DATEDIFF(DD,T0.DOCDATE,T3.DocDate) <= 120 AND T4.GroupCode NOT IN(107,123,124,125,126))           
            OR  (DATEDIFF(DD,T0.DOCDATE,T3.DocDate) > 120 AND T4.GroupCode  IN(107,123,124,125,126))          
         OR  (DATEDIFF(DD,T0.DOCDATE,T3.DocDate) <=120 AND T4.GroupCode  IN(107,123,124,125,126))) THEN T0.U_COMI 
WHEN t0.doctype = 'I' AND   DATEDIFF(DD,T0.DOCDATE,T3.DocDate) > 120 AND T4.GroupCode NOT IN(107,123,124,125,126)  THEN 0 
WHEN T0.doctype = 'I' AND ((DATEDIFF(DD,T0.DOCDATE,T3.DocDate) <=120 AND T4.GroupCode NOT IN(107,123,124,125,126))            
      OR (DATEDIFF(DD,T0.DOCDATE,T3.DocDate) >120 AND T4.GroupCode IN(107,123,124,125,126))           
      OR (DATEDIFF(DD,T0.DOCDATE,T3.DocDate) <=120 AND T4.GroupCode IN(107,123,124,125,126))) THEN T1.U_Coto   END)  AS 'Total Comision', 
convert(varchar,T3.DocNum) 'Recibo', '' 'Reconciliación',  T3.DocDate  'Fecha Recibo/Reconc.',  @Desde, @Hasta,
(SELECT CASE WHEN t0.DocType = 'S' THEN 'S/A' ELSE t1.Dscription END) 'Descripción',  
(CASE WHEN DATEDIFF(DD,T0.DOCDATE,T3.DocDate) > 120  THEN '***' ELSE '' END) AS '+120 dias',
null, null,null 

FROM OINV T0 
INNER JOIN INV1 T1 ON t0.DocEntry = T1.docentry  
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode 
LEFT  JOIN  ORCT t3 on t3.DocNum =  T0.ReceiptNum 
INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode   

WHERE T1.TargetType != 14 and t0.PaidToDate = t0.DocTotal and (T3.DocDate BETWEEN @desde AND @Hasta )
AND (t2.SlpName =@Vendedor  OR @Vendedor='') 


UNION ALL

  SELECT  t0.DocNum '#Doc', T0.CardCode 'Cod.Cliente', T0.CardName 'Cliente',  T0.Docdate 'Fecha Doc.',
t2.slpname 'Vendedor',  (select case when t0.DocType = 'S' then T1.AcctCode else t1.itemcode end) 'Referencia',
T1.Quantity 'Cantidad', T1.price 'Precio', T1.LineTotal 'Total Linea', 
(Case WHEN DATEDIFF(DD,T0.DOCDATE,T5.ReconDate ) > 120 AND T4.GroupCode NOT IN (107,123,124,125,126) THEN 0 
    WHEN DATEDIFF(DD,T0.DOCDATE,T5.ReconDate ) > 120 AND T4.GroupCode     IN (107,123,124,125,126) THEN T1.Commission 
    WHEN DATEDIFF(DD,T0.DOCDATE,T5.ReconDate ) <= 120 AND T4.GroupCode    IN (107,123,124,125,126) THEN T1.Commission 
    WHEN DATEDIFF(DD,T0.DOCDATE,T5.ReconDate ) <=120 AND T4.GroupCode NOT IN (107,123,124,125,126) THEN T1.Commission END) AS '%Comision', 

(Case WHEN T0.doctype = 'S' AND   DATEDIFF(DD,T0.DOCDATE,T5.ReconDate) > 120 AND T4.GroupCode NOT IN(107,123,124,125,126)  THEN 0 
    WHEN T0.doctype = 'S' AND ((DATEDIFF(DD,T0.DOCDATE,T5.ReconDate) <= 120 AND T4.GroupCode NOT IN(107,123,124,125,126))           
            OR  (DATEDIFF(DD,T0.DOCDATE,T5.ReconDate) > 120 AND T4.GroupCode  IN(107,123,124,125,126))          
         OR  (DATEDIFF(DD,T0.DOCDATE,T5.ReconDate) <=120 AND T4.GroupCode  IN(107,123,124,125,126))) THEN T0.U_COMI 
WHEN t0.doctype = 'I' AND   DATEDIFF(DD,T0.DOCDATE,T5.ReconDate) > 120 AND T4.GroupCode NOT IN(107,123,124,125,126)  THEN 0 
WHEN T0.doctype = 'I' AND ((DATEDIFF(DD,T0.DOCDATE,T5.ReconDate) <=120 AND T4.GroupCode NOT IN(107,123,124,125,126))            
      OR (DATEDIFF(DD,T0.DOCDATE,T5.ReconDate) >120 AND T4.GroupCode IN(107,123,124,125,126))           
      OR (DATEDIFF(DD,T0.DOCDATE,T5.ReconDate) <=120 AND T4.GroupCode IN(107,123,124,125,126))) THEN T1.U_Coto   END)  AS 'Total Comision', 
'' 'Recibo', CONVERT(varchar,T5.Reconnum) 'Reconciliación',  T5.ReconDate  'Fecha Recibo/Reconc.',  @Desde, @Hasta,
(SELECT CASE WHEN t0.DocType = 'S' THEN 'S/A' ELSE t1.Dscription END) 'Descripción',  
(CASE WHEN DATEDIFF(DD,T0.DOCDATE,T5.ReconDate) > 120  THEN '***' ELSE '' END) AS '+120 dias',
null, null,null 

FROM OINV T0 
INNER JOIN INV1 T1 ON t0.DocEntry = T1.docentry  
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode 
INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode
LEFT JOIN ( SELECT Y.ReconNum,Y.SrcObjTyp,Y.SrcObjAbs ,X.ReconDate 
    FROM OITR X  INNER JOIN ITR1 Y ON X.ReconNum=Y.ReconNum 
    WHERE SrcObjTyp=13   AND X.ReconType=0) T5 ON T5.SrcObjAbs=T0.DocEntry     

WHERE T1.TargetType != 14 and t0.PaidToDate = t0.DocTotal and (T5.ReconDate BETWEEN @desde AND @Hasta )
AND (t2.SlpName =@Vendedor  OR @Vendedor='')

  ORDER BY t0.DocNum

[felipe_loyolarodriguez]

Cuanto traes el recibo, en vez de hacerlo con el campo directo desde la tabla, te sugiero hacer un COALESCE al campo.

Así en vez de traer los registros en forma de linea, ejemplo:

1234

23455

45677

Los traerá en una linea completa, así:

12134, 23455, 45677

Espero te sirva, usa como ejemplo este query:

DECLARE @pvt_table NVARCHAR(MAX)  
SELECT @pvt_table = COALESCE(@pvt_table + ',[' + CardCode + ']', '[' + CardCode + ']')  
FROM OCRD  
WHERE CardType = 'C'  
SELECT @pvt_table  

Atte.

Felipe