Hola buenas tardes, compañeros tengo un problema con un query al momento de ver los resultados en algunos resultados me los trae duplicados
SELECT CASE T0.[Series] WHEN 84 THEN 'FS'
WHEN 4 THEN 'SI'
WHEN 83 THEN 'SI3'
WHEN 91 THEN 'SI4'
ELSE 'OT' END 'Serie', T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[NumAtCard],
case t0.GroupNum when '1' then '30 dias'
when '10' then '7 dias'
when '-1' then 'Contado'
when '9' then '15 dias'
when '7' then '45 dias'
when '8' then '60 dias' else 'credito' end 'Dias de credito' ,'' 'Tipo Factura', T5.Project, T5.U_ProjectName, T5.U_GlobalAgreement, T0.[DocDate], T0.[DocDueDate],
CASE T0.[DocCur] WHEN 'USD' THEN T0.DocTotalFC
WHEN 'EUR' THEN T0.DocTotalFC
ELSE T0.DocTotal END 'Total Documento', T0.[DocCur] as Moneda,
CASE T0.[DocCur] WHEN 'USD' THEN T0.VatSumFC
WHEN 'EUR' THEN T0.VatSumFC
ELSE T0.VatSum END 'Iva', T0.[OwnerCode], T1.[firstName], T1.[lastName],
CASE
WHEN T0.[DocStatus] = 'O' THEN 'Pendiente'
WHEN T0.[DocStatus] = 'C' and T0.[CANCELED] = 'N' and T5.TrgetEntry is null THEN 'Pagada'
WHEN T0.[DocStatus] = 'C' and T5.TrgetEntry is not null THEN ' - ' ELSE 'Cancelada'
END 'Estatus',
(select A.Docnum from ORIN A where a.DocEntry=T5.TrgetEntry) 'Nota Cred.', F.Name 'Asesor Comercial',
G.Name 'Ingeniero de Proyecto', I.Name 'Project Manager',t0.DocTotalSy 'Total del documento USD',t0.PaidSys 'Importe Aplicado USD',
case T0.[DocCur] when 'MXP' then t0.PaidtoDate
when 'USD' then t0.PaidFC else '0' end 'Importe aplicado', t0.DocCur as Mond,t0.U_CC_ProjectCode,t0.U_CC_ProjectName,t0.U_CC_AGCode
FROM OINV T0 LEFT OUTER JOIN OHEM T1 ON T0.[OwnerCode] = T1.[empID]
INNER JOIN INV1 T5 ON T0.DocEntry=T5.DocEntry
left join OPRJ E on t5.Project=E.PrjCode
left join [dbo].[@PJ_AC] F on E.U_PJ_AC=F.Code
left join [dbo].[@PJ_EN] G on E.U_PJ_ProjectEn=G.Code
left join [dbo].[@AG_DM] H on T5.U_GlobalAgreement=H.Code
left join [dbo].[@CAT_PM] I on H.U_NomPM=I.Code
GROUP BY T0.Series, T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[NumAtCard],t0.GroupNum, T5.Project, T5.U_ProjectName,
T5.U_GlobalAgreement, T0.[DocDate], T0.[DocDueDate], T0.DocTotalFC, T0.VatSumFC, T0.[DocTotal], T0.VatSum, T0.[DocCur],
T0.[OwnerCode], T1.[firstName], T1.[lastName]
,T0.DocStatus, T0.[CANCELED],T5.TrgetEntry, F.Name, G.Name, I.Name,t0.DocTotalSy,t0.PaidSys,t0.PaidFC,t0.PaidtoDate,
t0.U_CC_ProjectCode,t0.U_CC_ProjectName,t0.U_CC_AGCode
UNION ALL
SELECT CASE T3.[Series] WHEN 87 THEN 'FA'
WHEN 34 THEN 'SI'
ELSE 'OT' END 'Serie', T3.[DocNum], T3.[CardCode], T3.[CardName], T3.[NumAtCard],case t3.GroupNum
when '1' then '30 dias'
when '10' then '7 dias'
when '-1' then 'Contado'
when '9' then '15 dias'
when '7' then '45 dias'
when '8' then '60 dias' else 'credito' end 'Dias de credito', 'Anticipo' 'Tipo Factura', T6.Project, T6.U_ProjectName, T6.U_GlobalAgreement, T3.[DocDate], T3.[DocDueDate],
CASE T3.[DocCur] WHEN 'USD' THEN T3.DocTotalFC
WHEN 'EUR' THEN T3.DocTotalFC
ELSE T3.DocTotal END 'Total Documento', T3.[DocCur] as Monedas,
CASE T3.[DocCur] WHEN 'USD' THEN T3.VatSumFC
WHEN 'EUR' THEN T3.VatSumFC
ELSE T3.VatSum END 'Iva', T3.[OwnerCode], T4.[firstName], T4.[lastName],
CASE
WHEN T3.[DocStatus] = 'O' THEN 'Pendiente'
WHEN T3.[DocStatus] = 'C' and T3.[CANCELED] = 'N' and T6.TrgetEntry is null THEN 'Pagada'
WHEN T3.[DocStatus] = 'C' and T6.TrgetEntry is not null THEN ' - ' ELSE 'Cancelada'
END 'Estatus',
(select A.Docnum from ORIN A where a.DocEntry=T6.TrgetEntry) 'Nota Cred.', F.Name 'Asesor Comercial',
G.Name 'Ingeniero de Proyecto', I.Name 'Project Manager',t3.DocTotalSy 'Total del documento USD',t3.PaidSys 'Importe Aplicado USD'
,case T3.[DocCur] when 'MXP' then t3.PaidtoDate
when 'USD' then t3.PaidFC else '0' end 'Importe aplicado', t3.DocCur as Monedass,t3.U_CC_ProjectCode,t3.U_CC_ProjectName,t3.U_CC_AGCode
FROM ODPI T3 LEFT OUTER JOIN OHEM T4 ON T3.[OwnerCode] = T4.[empID]
INNER JOIN DPI1 T6 ON T3.DocEntry=T6.DocEntry
left join OPRJ E on T3.Project=E.PrjCode
left join [dbo].[@PJ_AC] F on E.U_PJ_AC=F.Code
left join [dbo].[@PJ_EN] G on E.U_PJ_ProjectEn=G.Code
left join [dbo].[@AG_DM] H on T6.U_GlobalAgreement=H.Code
left join [dbo].[@CAT_PM] I on H.U_NomPM=I.Code
GROUP BY T3.Series, T3.[DocNum], T3.[CardCode], T3.[CardName], T3.[NumAtCard],t3.GroupNum, T6.Project, T6.U_ProjectName,
T6.U_GlobalAgreement, T3.[DocDate], T3.[DocDueDate], T3.DocTotalFC, T3.VatSumFC, T3.[DocTotal], T3.VatSum, T3.[DocCur],
T3.[OwnerCode], T4.[firstName], T4.[lastName]
, T3.DocStatus, T3.[CANCELED],t6.TrgetEntry, F.Name, G.Name, I.Name,t3.DocTotalSy,t3.PaidSys,t3.PaidFC,t3.PaidtoDate,
t3.DocCur,t3.U_CC_ProjectCode,t3.U_CC_ProjectName,t3.U_CC_AGCode
ORDER BY CardCode, DocNum
ejemplos
lo pase aun excel y me muestra repetidos
no se de que manera podria quitarlos
