Bom dia
por favor, criei uma Subquery e agora preciso modifica-la, sendo que rel.[pago?] like = 's'
me traga a data da baixa e sendo que
rel.[pago?] like = 'n' me traga data do vencimento, não sei se a estrutura existem da query está apta para atender esse necessidade. Obrigado
SELECT *
FROM (
SELECT
T1.DocNum as 'Primario'
, ISNULL (T1.Serial, '') AS 'INV/FAT/LCM'
, case when t5.InvType = '30'then 'LC'
when t5.InvType = '203' then 'AD'
when t5.InvType = '13' then 'NF'
else 'SEM DOC' end as 'Tipo do Doc'
,v1.NfmName Modelo
, T1.CardCode as 'Cod.PN'
, T1.CardName as 'Razao Social'
, T10.CardFName AS 'Nome Extrangeiro'
--, convert(nvarchar(max),T1.U_Observacao) AS 'Observação'
, CONVERT (VARCHAR, T1.DocDate, 103) as 'DT.Lancamento'
, CONVERT (VARCHAR, T1.TaxDate, 103) as 'DT.Documento'
, T3.DueDate as 'DT.Vencimento'
,convert(date,t6.TrsfrDate,103) as 'DT.Baixa'
, DATEDIFF (DAY, T3.DueDate, GETDATE()) as 'Dias Atraso'
, T3.InstlmntID AS 'Parcela'
, CAST (T1.DocTotal AS MONEY) AS 'VLR Liquido'
, (T1.DocTotal + t1.WTSum) AS 'VLR Bruto'
, CAST (T3.InsTotal AS MONEY) AS 'Valor Parcela'
, CAST (T3.Paid AS MONEY) as 'Valor Contas a Receber'
, CASE
WHEN T7.BoeStatus = 'P' THEN T6.BoeSum
WHEN T6.TrsfrSum > 0 THEN T6.TrsfrSum
WHEN T6.CashSum > 0 THEN T6.CashSum
WHEN T6.CheckSum > 0 THEN T6.CheckSum
ELSE '0'
END AS 'Pago'
, CASE
WHEN T7.BoeStatus = 'P' THEN 'S'
WHEN T6.TrsfrSum > 0 THEN 'S'
WHEN T6.CashSum > 0 THEN 'S'
WHEN T6.CheckSum > 0 THEN 'S'
WHEN T9.TransId = (SELECT T99.TransId
From ITR1 T99
INNER JOIN OITR T98 ON T99.ReconNum = T98.ReconNum
WHERE T9.TransId = T99.TransId
AND T9.Line_ID = T99.TransRowId
AND T98.Canceled = 'N'
AND T98.IsSystem = 'N'
) THEN 'S'
ELSE 'N'
END AS 'Pago?'
, ISNULL (T2.OcrCode, '') AS 'Centro de Custo'
, ISNULL (T2.Project, '') AS 'Projeto'
, ISNULL (b2.SerialNfSe, '') AS 'Nota Fiscal'
, u3.Usage Utilização
, ISNULL (T1.PeyMethod, '') AS 'Meio Pagamento'
, ISNULL (T4.PymntGroup, '') AS 'Condição Pagto'
, T1.Comments as 'Comentário'
, (T5.BfDcntSum) AS 'Total Antes Desconto'
, CAST (T5.Dcount AS MONEY) AS '% Desconto'
, (T5.DcntSum) AS 'Vlr Desconto'
, CAST (T5.SumApplied AS MONEY) AS 'Total NF'
, CAST (T6.DocTotal AS MONEY) AS 'Total CP'
FROM OINV T1
LEFT JOIN INV1 T2 ON T1.DocEntry = T2.DocEntry
AND T2.LineNum = 0
LEFT JOIN INV6 T3 ON T1.DocEntry = T3.DocEntry
LEFT JOIN OCTG T4 ON T1.GroupNum = T4.GroupNum
LEFT JOIN RCT2 T5 ON T3.DocEntry = T5.DocEntry
AND T3.InstlmntID = T5.InstId
LEFT JOIN ORCT T6 ON T5.DocNum = T6.DocNum
LEFT JOIN OBOE T7 ON T6.BoeAbs = T7.BoeKey
LEFT JOIN OPYM T8 ON T1.PeyMethod = T8.PayMethCod
LEFT JOIN JDT1 T9 ON T1.TransId = T9.TransId
AND T1.CardCode = T9.ShortName
INNER JOIN OCRD T10 ON T1.CardCode = T10.CardCode
left join SBO_TaxOne.dbo.Doc B2 on T1.docentry=b2.DocEntry
LEFT JOIN OUSG u3 ON T2.Usage=u3.ID
left join onfm v1 on t1.Model=v1.AbsEntry
WHERE
T1.Canceled = 'N'
AND
(T7.BoeStatus <> 'C' OR T7.BoeStatus IS NULL)
AND T2.TrgetEntry is null
AND (T6.Canceled = 'N' OR T6.Canceled IS NULL)
AND (T6.Canceled = 'N' OR T6.Canceled IS NULL)
) AS REL
where rel.[Pago?] like 's'
GROUP BY REL.Primario,rel.[INV/FAT/LCM] ,REL.[Cod.PN], REL.[Razao Social], REL.[DT.Lancamento], REL.[DT.Documento],
REL.[DT.Vencimento], REL.[Dias Atraso], REL.Parcela, REL.[VLR Liquido], REL.[Valor Parcela], REL.[Valor Contas a Receber],
REL.Pago, REL.[Pago?], REL.[Centro de Custo], REL.[Meio Pagamento], REL.[Condição Pagto], REL.Comentário,
REL.[Total Antes Desconto], REL.[% Desconto], REL.[Vlr Desconto], REL.[Total NF], REL.[Total CP], REL.[Nome Extrangeiro]
,REL.Projeto,rel.[Nota Fiscal],rel.Utilização,rel.[VLR Bruto]
,rel.[DT.Baixa],rel.[Tipo do Doc],rel.[Modelo]
order by REL.[DT.Baixa], rel.[DT.Vencimento]
