on 07-16-2009 11:33 PM
Hola buenas tardes tengo algunos problemas con una consulta del reporte de antiguedad de saldos ya que no me cuadra con lo de b1, esta consulta es detallada con sus facturas y la moneda que se refiere la moneda.
haber si me pueden ayudar a reparar este informe
SELECT T0.CardCode AS 'Cod S/N', T0.CardName AS Nombre, T1.[CardFName], T2.PYMNTGROUP, T1.CREDITLINE
,'TotalDoc'= (case when DocCur='USD' then (T0.DocTotalFC) else (DocTotal) end)
, T0.DocNum AS 'N/Doc'
,DOCDATE as 'Fech/Crea',GETDATE() as FechaInforme,
T0.NUMATCARD as Referencia, T0.DOCDUEDATE as 'Fech/Venc.', DATEDIFF(day, docduedate, getdate())
as Días,'Saldo Pendiente'= (case when DocCur='USD' then (T0.DocTotalFC-T0.PaidFC) else (DocTotal-PaidToDate) end)
, DocCur as Moneda
,'Corriente' = (case when (Doccur='MXP' and getdate() <= DocDueDate) then (DocTotal-PaidToDate)
else (case when getdate() <= DocDueDate and Doccur='USD' then (DocTotalFC-PaidFC) else 0 end) end)
,' 0-30' = (case when (Doccur='MXP' and DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF
(day, docduedate, getdate())<=30) then (T0.DocTotal-T0.PaidToDate) else
(case when DATEDIFF(day, docduedate, getdate())>=0
and DATEDIFF(day, docduedate, getdate())<=30 and Doccur='USD'
then (DocTotalFC-PaidFC) else 0 end) end)
,' 31-60' = (case when (DocCur='MXP' and DATEDIFF(day, docduedate, getdate())>=31 and
DATEDIFF(day, docduedate, getdate())<=60) then (T0.DocTotal-T0.PaidToDate) else
(case when DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate
, getdate())<=60 and DocCur='USD' then (DocTotalFC-PaidFc)else 0 end) end)
,' 61-90' = (case when (DocCur='MXP' and DATEDIFF(day, docduedate, getdate())>=61 and
DATEDIFF(day, docduedate, getdate())<=90) then (T0.DocTotal-T0.PaidToDate) else
(case when DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90
and DocCur='USD' then (DocTotalFC-PaidFC) else 0 end) end),
'90+' = (case when (DocCur='MXP' and DATEDIFF(day, docduedate, getdate())>=90)
then (T0.DocTotal-T0.PaidToDate) else (case when (DocCur='USD' and DATEDIFF(day, docduedate, getdate())>=90)
then (T0.DocTotalFC-T0.PaidFC) else 0 end) end), 'Factura' as 'Docto' FROM OINV T0
INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE
INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM
WHERE (T0.DocTotal-T0.PaidToDate <>0 or DocTotalFC-PaidFC <> 0)
union all
SELECT T0.CardCode AS Cliente, T0.CardName AS Nombre, T1.[CardFName], T2.PYMNTGROUP, T1.CREDITLINE,
'TotalDoc'= (case when DocCur='USD' then (T0.DocTotalFC) else (DocTotal) end), T0.DocNum AS NoDocto,DOCDATE as FechaDocto,GETDATE() as FechaInforme,
T0.NUMATCARD as Referencia, T0.DOCDUEDATE as Vencimiento, DATEDIFF(day, docduedate, getdate())
as DíasVencimiento,'Saldo'= (case when DocCur='USD' then (T0.DocTotalFC-T0.PaidFC)-1 else (DocTotal-PaidToDate)-1 end)
, DocCur as Moneda
,'Por Vencer' = (case when (Doccur='MXP' and getdate() <= DocDueDate) then (DocTotal-PaidToDate)*-1
else (case when getdate() <= DocDueDate and Doccur='USD' then (DocTotalFC-PaidFC)*-1 else 0 end) end)
,' 0-30' = (case when (Doccur='MXP' and DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF
(day, docduedate, getdate())<=30) then (T0.DocTotal-T0.PaidToDate)*-1 else
(case when DATEDIFF(day, docduedate, getdate())>=0
and DATEDIFF(day, docduedate, getdate())<=30 and Doccur='USD'
then (DocTotalFC-PaidFC)*-1 else 0 end) end)
,' 31-60' = (case when (DocCur='MXP' and DATEDIFF(day, docduedate, getdate())>=31 and
DATEDIFF(day, docduedate, getdate())<=60) then (T0.DocTotal-T0.PaidToDate)*-1 else
(case when DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate
, getdate())<=60 and DocCur='USD' then (DocTotalFC-PaidFc)*-1 else 0 end) end)
,' 61-90' = (case when (DocCur='MXP' and DATEDIFF(day, docduedate, getdate())>=61 and
DATEDIFF(day, docduedate, getdate())<=90) then (T0.DocTotal-T0.PaidToDate)*-1 else
(case when DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90
and DocCur='USD' then (DocTotalFC-PaidFC)*-1 else 0 end) end),
'90+' = (case when (DocCur='MXP' and DATEDIFF(day, docduedate, getdate())>=90)
then (T0.DocTotal-T0.PaidToDate)*-1 else (case when (DocCur='USD' and DATEDIFF(day, docduedate, getdate())>=90)
then (T0.DocTotalFC-T0.PaidFC)*-1 else 0 end) end) , 'NC' as 'Docto' FROM ORIN T0
INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE
INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM
WHERE (T0.DocTotal-T0.PaidToDate <>0 or DocTotalFC-PaidFC <> 0)
union all
SELECT T0.CardCode AS Cliente, T0.CardName AS Nombre, T1.[CardFName], T2.PYMNTGROUP, T1.CREDITLINE
,'TotalDoc'= (case when DocCurr='USD' then (T0.DocTotalFC) else (DocTotal) end)
, T0.DocNum AS NoDocto,DOCDATE as FechaDocto,GETDATE() as FechaInforme,
T0.CounterRef as Referencia, T0.DOCDUEDATE as Vencimiento, DATEDIFF(day, docduedate, getdate())
as DíasVencimiento,'Saldo'= (case when DocCurr='USD' then (T0.NoDocSumFC * -1) else (T0.NoDocSum * -1) end)
, DocCurr as Moneda
,'Por Vencer' = (case when (DocCurr='MXP' and getdate() <= DocDueDate) then (T0.NoDocSum)
else (case when getdate() <= DocDueDate and DocCurr='USD' then (T0.NoDocSumFC * -1) else 0 end) end)
,' 0-30' = (case when (DocCurr='MXP' and DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF
(day, docduedate, getdate())<=30) then (T0.NoDocSum * -1) else
(case when DATEDIFF(day, docduedate, getdate())>=0
and DATEDIFF(day, docduedate, getdate())<=30 and DocCurr='USD'
then (T0.NoDocSumFC * -1) else 0 end) end)
,' 31-60' = (case when (DocCurr='MXP' and DATEDIFF(day, docduedate, getdate())>=31 and
DATEDIFF(day, docduedate, getdate())<=60) then (T0.NoDocSum * -1) else
(case when DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate
, getdate())<=60 and DocCurr='USD' then (T0.NoDocSumFC * -1)else 0 end) end)
,' 61-90' = (case when (DocCurr='MXP' and DATEDIFF(day, docduedate, getdate())>=61 and
DATEDIFF(day, docduedate, getdate())<=90) then (T0.NoDocSum * -1) else
(case when DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90
and DocCurr='USD' then (T0.NoDocSumFC * -1) else 0 end) end),
'90+' = (case when (DocCurr='MXP' and DATEDIFF(day, docduedate, getdate())>=90)
then (T0.NoDocSum * -1) else (case when (DocCurr='USD' and DATEDIFF(day, docduedate, getdate())>=90)
then (T0.NoDocSumFC * -1) else 0 end) end), 'Cobranza' as 'Docto' FROM ORCT T0
INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE
INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM
WHERE (T0.NoDocSum <> 0 or T0.NoDocSumFC <> 0) AND T0.[DocType] = 'C' AND T0.OpenBal <> 0 AND T0.[JrnlMemo] not like '%%Cancelado%%' ORDER BY T0.CardCode, T0.DocDate
Hola.
Pruebe el código y nos cuenta cómo le fué.
Quedo en espera de sus comentarios.
Saludos.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
union all
SELECT T0.CardCode AS Cliente, T0.CardName AS Nombre, T1.[CardFName], T2.PYMNTGROUP, T1.CREDITLINE ,
'TotalDoc'= (case when DocCurr='USD' then (T0.DocTotalFC) else (DocTotal) end) , T0.DocNum AS NoDocto,
DOCDATE as FechaDocto,GETDATE() as FechaInforme, T0.CounterRef as Referencia, T0.DOCDUEDATE as Vencimiento,
DATEDIFF(day, docduedate, getdate()) as DíasVencimiento,
'Saldo'= (case when DocCurr='USD' then (T0.NoDocSumFC * -1) else (T0.NoDocSum * -1) end) , DocCurr as Moneda ,
'Por Vencer' = (case when (DocCurr=@LOCCUR and getdate() <= DocDueDate) then (T0.NoDocSum) else (case when getdate() <= DocDueDate and DocCurr='USD' then (T0.NoDocSumFC * -1) else 0 end) end) ,
' 0-30' = (case when (DocCurr=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF (day, docduedate, getdate())<=30) then (T0.NoDocSum * -1) else (case when DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF(day, docduedate, getdate())<=30 and DocCurr='USD' then (T0.NoDocSumFC * -1) else 0 end) end) ,
' 31-60' = (case when (DocCurr=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate, getdate())<=60) then (T0.NoDocSum * -1) else (case when DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate , getdate())<=60 and DocCurr='USD' then (T0.NoDocSumFC * -1)else 0 end) end) ,
' 61-90' = (case when (DocCurr=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90) then (T0.NoDocSum * -1) else (case when DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90 and DocCurr='USD' then (T0.NoDocSumFC * -1) else 0 end) end),
'90+' = (case when (DocCurr=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=90) then (T0.NoDocSum * -1) else (case when (DocCurr='USD' and DATEDIFF(day, docduedate, getdate())>=90) then (T0.NoDocSumFC * -1) else 0 end) end),
'Cobranza' as 'Docto'
FROM ORCT T0
INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE
INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM
WHERE (T0.NoDocSum > 0 or T0.NoDocSumFC > 0) AND T0.[DocType] = 'C' AND T0.OpenBal > 0 AND T0.[JrnlMemo] not like '%%Cancelado%%'
ORDER BY T0.CardCode, T0.DocDate
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
DECLARE @LOCCUR VARCHAR(3)
SET @LOCCUR = '$'
SELECT T0.CardCode AS 'Cod S/N', T0.CardName AS Nombre, T1.[CardFName], T2.PYMNTGROUP, T1.CREDITLINE ,
'TotalDoc'= (case when DocCur='USD' then (T0.DocTotalFC) else (DocTotal) end) , T0.DocNum AS 'N/Doc' ,
DOCDATE as 'Fech/Crea',GETDATE() as FechaInforme, T0.NUMATCARD as Referencia, T0.DOCDUEDATE as 'Fech/Venc.',
DATEDIFF(day, docduedate, getdate()) as Días,
'Saldo Pendiente'= (case when DocCur='USD' then (T0.DocTotalFC-T0.PaidFC) else (DocTotal-PaidToDate) end) ,
DocCur as Moneda ,'Corriente' = (case when (Doccur=@LOCCUR and getdate() <= DocDueDate) then (DocTotal-PaidToDate) else (case when getdate() <= DocDueDate and Doccur='USD' then (DocTotalFC-PaidFC) else 0 end) end) ,
' 0-30' = (case when (T0.Doccur=@LOCCUR and DATEDIFF(day, T0.docduedate, getdate())BETWEEN 0 and 30) then (T0.DocTotal-T0.PaidToDate) else (case when DATEDIFF(day, T0.docduedate, getdate())BETWEEN 0 AND 30 and T0.Doccur='USD' then (T0.DocTotalFC-T0.PaidFC) else 0 end) end) ,
' 31-60' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate, getdate())<=60) then (T0.DocTotal-T0.PaidToDate) else (case when DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate , getdate())<=60 and DocCur='USD' then (DocTotalFC-PaidFc)else 0 end) end) ,
' 61-90' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90) then (T0.DocTotal-T0.PaidToDate) else (case when DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90 and DocCur='USD' then (DocTotalFC-PaidFC) else 0 end) end),
'90+' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=90) then (T0.DocTotal-T0.PaidToDate) else (case when (DocCur='USD' and DATEDIFF(day, docduedate, getdate())>=90) then (T0.DocTotalFC-T0.PaidFC) else 0 end) end),
'Factura' as 'Docto'
FROM OINV T0
INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE
INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM
WHERE (T0.DocTotal-T0.PaidToDate > 0 or DocTotalFC-PaidFC > 0)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hola.
Intenta con este query. Lo voy a colocar en tres segmentos separados para que se pueda visualizar bien.
He identado las secciones y cambié las condiciones del where. Ya debe coincidir con el Informe de Antiguedad de saldos del sistema. Hay que colocar el símbolo de moneda correcto en la variable @LOCCUR.
Saludos.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
union all
SELECT T0.CardCode AS Cliente, T0.CardName AS Nombre, T1.[CardFName], T2.PYMNTGROUP, T1.CREDITLINE,
'TotalDoc'= (case when DocCur='USD' then (T0.DocTotalFC) else (DocTotal) end), T0.DocNum AS NoDocto,
DOCDATE as FechaDocto,GETDATE() as FechaInforme, T0.NUMATCARD as Referencia, T0.DOCDUEDATE as Vencimiento,
DATEDIFF(day, docduedate, getdate()) as DíasVencimiento,
'Saldo'= (case when DocCur='USD' then (T0.DocTotalFC-T0.PaidFC)*-1 else (DocTotal-PaidToDate)*-1 end) ,
DocCur as Moneda ,'Por Vencer' = (case when (Doccur=@LOCCUR and getdate() <= DocDueDate) then (DocTotal-PaidToDate)*-1 else (case when getdate() <= DocDueDate and Doccur='USD' then (DocTotalFC-PaidFC)*-1 else 0 end) end) ,
' 0-30' = (case when (Doccur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF (day, docduedate, getdate())<=30) then (T0.DocTotal-T0.PaidToDate)*-1 else (case when DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF(day, docduedate, getdate())<=30 and Doccur='USD' then (DocTotalFC-PaidFC)*-1 else 0 end) end) ,
' 31-60' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate, getdate())<=60) then (T0.DocTotal-T0.PaidToDate)*-1 else (case when DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate , getdate())<=60 and DocCur='USD' then (DocTotalFC-PaidFc)*-1 else 0 end) end) ,
' 61-90' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90) then (T0.DocTotal-T0.PaidToDate)*-1 else (case when DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90 and DocCur='USD' then (DocTotalFC-PaidFC)*-1 else 0 end) end),
'90+' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=90) then (T0.DocTotal-T0.PaidToDate)*-1 else (case when (DocCur='USD' and DATEDIFF(day, docduedate, getdate())>=90) then (T0.DocTotalFC-T0.PaidFC)*-1 else 0 end) end) ,
'NC' as 'Docto'
FROM ORIN T0
INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE
INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM
WHERE (T0.DocTotal-T0.PaidToDate > 0 or DocTotalFC-PaidFC > 0)
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.