on 02-24-2014 9:30 PM
Buenas tardes a todos
alquien podria ayudarme con este query, esta en el foro pero cuando lo ejecuto me da error,
/* Date Filter, Format: 'YYYYMMDD' */
DECLARE @FromDate AS DATETIME SET @FromDate = '[%1]'
DECLARE @ToDate AS DATETIME SET @ToDate = '[%2]'
/*Opening Balance */
SELECT
NULL AS RefDate,-1 as Transtype,
NULL AS TaxDate,
'' AS Ref1,
'' AS BaseRef,
'Opening Balance' AS LineMemo,
-1 AS TransID,
Isnull(Sum(T1.Debit),0) - Isnull(Sum(T1.Credit),0) AS OpeningBalance ,
0 AS Debit,
0 as Credit
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.TransID=T1.TransID
LEFT OUTER JOIN OACT T2 ON T1.Account=T2.AcctCode
INNER JOIN OCRD T3 ON T1.Shortname = T3.CardCode
WHERE T1.Account<>T1.ShortName and T1.ShortName='[%0]' AND
T0.RefDate='[%1]'
GROUP BY T1.Account, T2.AcctName
UNION ALL
/* Journal Rows */
SELECT
T0.RefDate,T0.TransType,
T0.TaxDate,
T0.Ref1,
T1.BaseRef,
T1.LineMemo,
T0.TransID,
Null AS OpeningBalance,
T1.Debit,
T1.Credit
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.TransID=T1.TransID
LEFT OUTER JOIN OACT T2 ON T1.Account=T2.AcctCode
INNER JOIN OCRD T3 ON T1.Shortname = T3.CardCode
WHERE T1.Account<>T1.ShortName and T1.ShortName='[%0]' AND
T0.Refdate Between '[%0]' and '[%1]'
agradecere mucho su ayuda, es un query de estao de cuenta de proveedor,
Prueba asi, saludos!!!
/*Opening Balance */
SELECT
NULL AS RefDate,-1 as Transtype,
NULL AS TaxDate,
'' AS Ref1,
'' AS BaseRef,
'Opening Balance' AS LineMemo,
-1 AS TransID,
Isnull(Sum(T1.Debit),0) - Isnull(Sum(T1.Credit),0) AS OpeningBalance ,
0 AS Debit,
0 as Credit
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.TransID=T1.TransID
LEFT OUTER JOIN OACT T2 ON T1.Account=T2.AcctCode
INNER JOIN OCRD T3 ON T1.Shortname = T3.CardCode
WHERE T1.Account<>T1.ShortName and T1.ShortName='[%0]' AND T0.REFDATE BETWEEN '[%1]' AND '[%2]'
GROUP BY T1.Account, T2.AcctName
UNION ALL
/* Journal Rows */
SELECT
T0.RefDate,T0.TransType,
T0.TaxDate,
T0.Ref1,
T1.BaseRef,
T1.LineMemo,
T0.TransID,
Null AS OpeningBalance,
T1.Debit,
T1.Credit
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.TransID=T1.TransID
LEFT OUTER JOIN OACT T2 ON T1.Account=T2.AcctCode
INNER JOIN OCRD T3 ON T1.Shortname = T3.CardCode
WHERE T1.Account<>T1.ShortName and T1.ShortName='[%0]' AND T0.REFDATE BETWEEN '[%1]' AND '[%2]'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Con ese query, si en la factura de cliente a demás del servicio/ venta, hay algo refacturado ( cómo materiales, portes de transportes...) no te saldrá, tenlo en cuenta.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
12 | |
11 | |
6 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.