Solved: Ayudadita con query casi terminado

Former Member · ‎07-16-2012

Amigos pues necesito una pequeña ayuda con un query que me facilito el buen Felipe Loyola Rodriguez, yo ya le he hecho algunas modificaciones, solo me falta hacer algo como tipo "filtros", pues el query es para ver la deuda de los socios de negocios por vendedores, pero el query me despliega la lista con todos los vendedores, y a mi solo me interesa una, como haria para que solo saliera un solo vendedor?

Agradezco su ayuda.

Aqui el query:

DECLARE @VAR INT

SET @VAR = (SELECT TOP 1 A.TransId FROM JDT1 A WHERE A.RefDate <=getdate())

SELECT

CASE T1.TransType

WHEN '13' THEN (SELECT X.SlpName FROM OSLP X INNER JOIN OINV Y ON Y.SlpCode=X.SlpCode WHERE Y.TransId = T1.TransId)

WHEN '14' THEN (SELECT X.SlpName FROM OSLP X INNER JOIN ORIN Y ON Y.SlpCode=X.SlpCode WHERE Y.TransId = T1.TransId)

ELSE ''

END'Vendedor', T0.CardCode, T0.CardName, T1.TransId'Asiento',

CASE T1.TransType

WHEN 13 THEN 'Venta'

WHEN 14 THEN 'N.Credito'

WHEN 24 THEN 'Abono'

WHEN 30 THEN 'Traspaso'

END 'Comprobante',

T4.BaseRef 'Doc Interno', T4.Folionum'Folio', T1.TaxDate, T1.DueDate, DATEDIFF(DAY,getdate(),T1.DueDate)'Días Cred.',

CASE T1.TransType

WHEN '13' THEN (SELECT Y.PymntGroup FROM OINV X INNER JOIN OCTG Y ON Y.GroupNum=X.GroupNum WHERE X.TransId = T1.TransId)

WHEN '14' THEN (SELECT Y.PymntGroup FROM ORIN X INNER JOIN OCTG Y ON Y.GroupNum=X.GroupNum WHERE X.TransId = T1.TransId)

ELSE ''

END'Cond.Pago',

CASE

WHEN T3.DebHab = 'D' THEN (T1.Debit-T1.Credit-T3.ReconSum)

WHEN T3.DebHab = 'C' THEN (T1.Debit-T1.Credit+T3.ReconSum)

ELSE (T1.Debit-T1.Credit)

END 'Saldo',

CASE

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) > 0 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) > 0 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) > 0 then (T1.Debit-T1.Credit) end 'Por vencer',

CASE

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= 0 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -30 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= 0 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -30 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= 0 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -30 then (T1.Debit-T1.Credit) end '0-30 dias',

CASE

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -31 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -60 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -31 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -60 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -31 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -60 then (T1.Debit-T1.Credit) end '31-60 dias',

CASE

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -61 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -90 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -61 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -90 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -61 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -90 then (T1.Debit-T1.Credit) end '61-90 dias',

CASE

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -91 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -120 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -91 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -120 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -91 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -120 then (T1.Debit-T1.Credit) end '91-120 dias',

CASE

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) < -120 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) < -120 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) < -120 then (T1.Debit-T1.Credit) end '+120 dias',

T1.LineMemo'Comentarios'

FROM OCRD T0

INNER JOIN JDT1 T1 ON T1.ShortName = T0.CardCode

INNER JOIN OACT T2 ON T2.AcctCode = T1.Account

INNER JOIN OJDT T4 ON T4.TransId = T1.TransId

LEFT JOIN (SELECT X0.ShortName 'SN', X0.TransId 'TransId', SUM(X0.ReconSum)'ReconSum', X0.IsCredit 'DebHab', X0.TransRowId 'Linea'

FROM ITR1 X0

INNER JOIN OITR X1 ON X1.ReconNum = X0.ReconNum

WHERE X1.ReconDate <= getdate() AND X1.CancelAbs = ''

GROUP BY X0.ShortName, X0.TransId, X0.IsCredit, X0.TransRowId) T3 ON T3.TransId = T1.TransId AND T3.SN = T1.ShortName AND T3.Linea = T1.Line_ID

WHERE T0.CardType = 'C' AND T1.RefDate <= getdate() /*AND T2.AcctCode = ''*/ AND T1.TransType NOT IN ('-2','-3') AND

(CASE

WHEN T3.DebHab = 'D' THEN (T1.Debit-T1.Credit-T3.ReconSum)

WHEN T3.DebHab = 'C' THEN (T1.Debit-T1.Credit+T3.ReconSum)

ELSE (T1.Debit-T1.Credit)

END) != '0'

ORDER BY T0.CardCode, T1.TransId

felipe_loyolarodriguez · ‎07-17-2012

Hola

Prueba el query en SAP así

DECLARE @VAR INT, @FECHAFIN DATETIME , @VAR2 SMALLINT, @VENDEDOR NVARCHAR(155)
SET @VAR = (SELECT TOP 1 A.TransId FROM [dbo].[JDT1] A WHERE A.RefDate <='[%0]')  
SET @FECHAFIN = '[%0]' 
SET @VAR2 = (SELECT TOP 1 B.SlpCode FROM [dbo].[OSLP] B WHERE B.SlpName = '[%1]')
SET @VENDEDOR = '[%1]' 
  
SELECT *
FROM (
SELECT   
CASE T1.TransType  
          WHEN '13' THEN (SELECT X.SlpName FROM OSLP X INNER JOIN OINV Y ON Y.SlpCode=X.SlpCode WHERE Y.TransId = T1.TransId)  
          WHEN '14' THEN (SELECT X.SlpName FROM OSLP X INNER JOIN ORIN Y ON Y.SlpCode=X.SlpCode WHERE Y.TransId = T1.TransId)  
          ELSE ''  
END'Vendedor', T0.CardCode AS Codigo, T0.CardName AS Cliente, T1.TransId'Asiento',   
CASE T1.TransType  
          WHEN 13 THEN 'Venta'  
          WHEN 14 THEN 'N.Credito'  
          WHEN 24 THEN 'Abono'  
          WHEN 30 THEN 'Traspaso'  
END 'Comprobante',   
T4.BaseRef 'Doc Interno', T4.Folionum'Folio', T1.TaxDate AS Fecha_Doc, T1.DueDate AS Fecha_Venc, DATEDIFF(DAY,@FECHAFIN,T1.DueDate)'Días Cred.',  
CASE T1.TransType  
          WHEN '13' THEN (SELECT Y.PymntGroup FROM OINV X INNER JOIN OCTG Y ON Y.GroupNum=X.GroupNum WHERE X.TransId = T1.TransId)  
          WHEN '14' THEN (SELECT Y.PymntGroup FROM ORIN X INNER JOIN OCTG Y ON Y.GroupNum=X.GroupNum WHERE X.TransId = T1.TransId)  
          ELSE ''  
END'Cond.Pago',  
CASE  
          WHEN T3.DebHab = 'D' THEN (T1.Debit-T1.Credit-T3.ReconSum)  
          WHEN T3.DebHab = 'C' THEN (T1.Debit-T1.Credit+T3.ReconSum)  
          ELSE (T1.Debit-T1.Credit)  
END 'Saldo',  
CASE  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) > 0 and T3.DebHab = 'D'  then T1.Debit-T1.Credit-T3.ReconSum  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) > 0 and T3.DebHab = 'C'  then T1.Debit-T1.Credit+T3.ReconSum  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) > 0 then (T1.Debit-T1.Credit) end 'Por vencer',  
CASE  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) <= 0 AND DATEDIFF(DAY,@FECHAFIN,T1.DueDate) >= -30 and T3.DebHab = 'D'  then T1.Debit-T1.Credit-T3.ReconSum  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) <= 0 AND DATEDIFF(DAY,@FECHAFIN,T1.DueDate) >= -30 and T3.DebHab = 'C'  then T1.Debit-T1.Credit+T3.ReconSum  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) <= 0 AND DATEDIFF(DAY,@FECHAFIN,T1.DueDate) >= -30 then (T1.Debit-T1.Credit) end '0-30 dias',  
CASE  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) <= -31 AND DATEDIFF(DAY,@FECHAFIN,T1.DueDate) >= -60 and T3.DebHab = 'D'  then T1.Debit-T1.Credit-T3.ReconSum  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) <= -31 AND DATEDIFF(DAY,@FECHAFIN,T1.DueDate) >= -60 and T3.DebHab = 'C'  then T1.Debit-T1.Credit+T3.ReconSum  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) <= -31 AND DATEDIFF(DAY,@FECHAFIN,T1.DueDate) >= -60 then (T1.Debit-T1.Credit) end '31-60 dias',  
CASE  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) <= -61 AND DATEDIFF(DAY,@FECHAFIN,T1.DueDate) >= -90 and T3.DebHab = 'D'  then T1.Debit-T1.Credit-T3.ReconSum  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) <= -61 AND DATEDIFF(DAY,@FECHAFIN,T1.DueDate) >= -90 and T3.DebHab = 'C'  then T1.Debit-T1.Credit+T3.ReconSum  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) <= -61 AND DATEDIFF(DAY,@FECHAFIN,T1.DueDate) >= -90 then (T1.Debit-T1.Credit) end '61-90 dias',  
CASE  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) <= -91 AND DATEDIFF(DAY,@FECHAFIN,T1.DueDate) >= -120 and T3.DebHab = 'D'  then T1.Debit-T1.Credit-T3.ReconSum  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) <= -91 AND DATEDIFF(DAY,@FECHAFIN,T1.DueDate) >= -120 and T3.DebHab = 'C'  then T1.Debit-T1.Credit+T3.ReconSum  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) <= -91 AND DATEDIFF(DAY,@FECHAFIN,T1.DueDate) >= -120 then (T1.Debit-T1.Credit) end '91-120 dias',  
CASE  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) < -120 and T3.DebHab = 'D'  then T1.Debit-T1.Credit-T3.ReconSum  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) < -120 and T3.DebHab = 'C'  then T1.Debit-T1.Credit+T3.ReconSum  
WHEN DATEDIFF(DAY,@FECHAFIN,T1.DueDate) < -120 then (T1.Debit-T1.Credit) end '+120 dias',  
T1.LineMemo'Comentarios'  
FROM dbo.OCRD T0  
INNER JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode  
INNER JOIN dbo.OACT T2 ON T2.AcctCode = T1.Account  
INNER JOIN dbo.OJDT T4 ON T4.TransId = T1.TransId  
LEFT JOIN (SELECT X0.ShortName 'SN', X0.TransId 'TransId', SUM(X0.ReconSum)'ReconSum', X0.IsCredit 'DebHab', X0.TransRowId 'Linea'  
          FROM dbo.ITR1 X0  
          INNER JOIN dbo.OITR X1 ON X1.ReconNum = X0.ReconNum  
          WHERE X1.ReconDate <= @FECHAFIN AND X1.CancelAbs = ''  
          GROUP BY X0.ShortName, X0.TransId, X0.IsCredit, X0.TransRowId) T3 ON T3.TransId = T1.TransId AND T3.SN = T1.ShortName AND T3.Linea = T1.Line_ID  
WHERE T0.CardType = 'C' AND T1.RefDate <= @FECHAFIN AND T1.TransType NOT IN ('-2','-3') AND  
(CASE  
          WHEN T3.DebHab = 'D' THEN (T1.Debit-T1.Credit-T3.ReconSum)  
          WHEN T3.DebHab = 'C' THEN (T1.Debit-T1.Credit+T3.ReconSum)  
          ELSE (T1.Debit-T1.Credit)  
END) != '0'  
) AS T0
WHERE T0.Vendedor=@VENDEDOR
ORDER BY T0.Cliente, T0.Asiento
FOR BROWSE

Saludos

Former Member · ‎07-17-2012

Omar:

Yo te aconsejo que esta query la utilizes para crear una tabla temporal y esta tala la cruces con la oslp, seria algo asi:

DECLARE @VAR INT

SET @VAR = (SELECT TOP 1 A.TransId FROM JDT1 A WHERE A.RefDate <=getdate())

SELECT

CASE T1.TransType

WHEN '13' THEN (SELECT X.SlpName FROM OSLP X INNER JOIN OINV Y ON Y.SlpCode=X.SlpCode WHERE Y.TransId = T1.TransId)

WHEN '14' THEN (SELECT X.SlpName FROM OSLP X INNER JOIN ORIN Y ON Y.SlpCode=X.SlpCode WHERE Y.TransId = T1.TransId)

ELSE ''

END'Vendedor', T0.CardCode, T0.CardName, T1.TransId'Asiento',

CASE T1.TransType

WHEN 13 THEN 'Venta'

WHEN 14 THEN 'N.Credito'

WHEN 24 THEN 'Abono'

WHEN 30 THEN 'Traspaso'

END 'Comprobante',

T4.BaseRef 'Doc Interno', T4.Folionum'Folio', T1.TaxDate, T1.DueDate, DATEDIFF(DAY,getdate(),T1.DueDate)'Días Cred.',

CASE T1.TransType

WHEN '13' THEN (SELECT Y.PymntGroup FROM OINV X INNER JOIN OCTG Y ON Y.GroupNum=X.GroupNum WHERE X.TransId = T1.TransId)

WHEN '14' THEN (SELECT Y.PymntGroup FROM ORIN X INNER JOIN OCTG Y ON Y.GroupNum=X.GroupNum WHERE X.TransId = T1.TransId)

ELSE ''

END'Cond.Pago',

CASE

WHEN T3.DebHab = 'D' THEN (T1.Debit-T1.Credit-T3.ReconSum)

WHEN T3.DebHab = 'C' THEN (T1.Debit-T1.Credit+T3.ReconSum)

ELSE (T1.Debit-T1.Credit)

END 'Saldo',

CASE

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) > 0 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) > 0 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) > 0 then (T1.Debit-T1.Credit) end 'Por vencer',

CASE

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= 0 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -30 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= 0 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -30 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= 0 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -30 then (T1.Debit-T1.Credit) end '0-30 dias',

CASE

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -31 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -60 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -31 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -60 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -31 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -60 then (T1.Debit-T1.Credit) end '31-60 dias',

CASE

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -61 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -90 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -61 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -90 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -61 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -90 then (T1.Debit-T1.Credit) end '61-90 dias',

CASE

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -91 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -120 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -91 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -120 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) <= -91 AND DATEDIFF(DAY,getdate(),T1.DueDate) >= -120 then (T1.Debit-T1.Credit) end '91-120 dias',

CASE

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) < -120 and T3.DebHab = 'D' then T1.Debit-T1.Credit-T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) < -120 and T3.DebHab = 'C' then T1.Debit-T1.Credit+T3.ReconSum

WHEN DATEDIFF(DAY,getdate(),T1.DueDate) < -120 then (T1.Debit-T1.Credit) end '+120 dias',

T1.LineMemo'Comentarios'

into #query1
FROM dbo.OCRD T0

INNER JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode

INNER JOIN dbo.OACT T2 ON T2.AcctCode = T1.Account

INNER JOIN dbo.OJDT T4 ON T4.TransId = T1.TransId

LEFT JOIN (SELECT X0.ShortName 'SN', X0.TransId 'TransId', SUM(X0.ReconSum)'ReconSum', X0.IsCredit 'DebHab', X0.TransRowId 'Linea'

FROM ITR1 X0

INNER JOIN OITR X1 ON X1.ReconNum = X0.ReconNum

WHERE X1.ReconDate <= getdate() AND X1.CancelAbs = ''

GROUP BY X0.ShortName, X0.TransId, X0.IsCredit, X0.TransRowId) T3 ON T3.TransId = T1.TransId AND T3.SN = T1.ShortName AND T3.Linea = T1.Line_ID

WHERE T0.CardType = 'C' AND T1.RefDate <= getdate() /*AND T2.AcctCode = ''*/ AND T1.TransType NOT IN ('-2','-3') AND

(CASE

WHEN T3.DebHab = 'D' THEN (T1.Debit-T1.Credit-T3.ReconSum)

WHEN T3.DebHab = 'C' THEN (T1.Debit-T1.Credit+T3.ReconSum)

ELSE (T1.Debit-T1.Credit)

END) != '0'

select t100.* from #query1 t100 inner join dbo.oslp t101 on t100.vendedor=t101.slpname
where t101.slpname=[%0]

Ayudadita con query casi terminado

Accepted Solutions (1)

Accepted Solutions (1)

Answers (1)

Answers (1)

Cannot create a Single-date Order ECO using a cust...

cancel Differrence on SAP

In which tables are the fields in the master data ...

Screen enhancement for EK92 - Payment plan creatio...

Re: Partial settlement not reversing accruals in S...