on 03-04-2013 8:42 PM
Buen día expertos!
Quisiera saber si alguien sabe de la existencia de un reporte ya sea nativo de SAP o mediante Query para obtener los saldos finales de "X" cuenta de cada mes.
Tengo una consulta que te da el saldo final de la cuenta pero tu tienes que decirle la fecha, la idea sería que te lo valla mostrando al final de cada més.
Ejemplo (muy burdo)
Bancos
Enero 100
Febrero 150
Marzo 120
.
.
.
La consulta que me sirve para saber el saldo a cierta fecha es:
De antemano, gracias por su ayuda!
Hola Antonio, checa si te puede servir este, lo modifique de acuerdo a lo que pides.
DECLARE @ANIO SMALLINT
SET @ANIO = (SELECT A.Year FROM dbo.OACP A WHERE A.Year='[%1]')
SELECT Distinct P.Cuenta,P.Nombre,
[1] as 'Jan',
[2] as 'Feb',
[3] as 'Mar',
[4] as 'Apr',
[5] as 'May',
[6] as 'Jun',
[7] as 'Jul',
[8] as 'Aug',
[9] as 'Sep',
[10] as 'Oct',
[11] as 'Nov'
FROM
( SELECT Distinct T0.[Account] as Cuenta ,T1.[AcctName] as Nombre,
MONTH(T0.RefDate) 'Month',SUM(T0.Debit-T0.Credit) 'Saldo'
FROM JDT1 T0
INNER JOIN OACT T1 ON T0.Account = T1.AcctCode
WHERE
T0.[Account] in
('_SYS00000000217', '_SYS00000000530', '_SYS00000000531', '_SYS00000000532', '_SYS00000000464')
and Year(T0.RefDate)=@ANIO
GROUP BY T0.[Account],T1.[AcctName],MONTH(T0.refDate)
) P
PIVOT (
sum(saldo)
FOR [month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) P
Basado de link de de Felipe Loyola Rodriguez
Todos los créditos, para el.
Saludos.
Alessandro.
Message was edited by: Alessandro Lopez Santinelli
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Intenta así:
/* Select * from OACP t1 */
Declare @d1 As Nvarchar(100)
SET @d1= /* t1.YEAR */ '[%1]'
select
DATENAME(YYYY, T0.Refdate) As 'Year',
DATENAME(MM, T0.Refdate) As 'Month',
SUM(T0.Debit-T0.Credit) as Saldo
FROM JDT1 T0
INNER JOIN OACT T1 ON T0.Account = T1.AcctCode
Where year(t0.RefDate)=@d1 and T0.Account in (
'_SYS00000000217',
'_SYS00000000530',
'_SYS00000000531',
'_SYS00000000532',
'_SYS00000000464' )
group by DATENAME(YYYY, T0.Refdate),DATENAME(MM, T0.Refdate)
order by DATENAME(YYYY, T0.RefDate) , DATENAME(MM, T0.refdate)
Saludos.
Alessandro.
User | Count |
---|---|
100 | |
11 | |
11 | |
6 | |
6 | |
5 | |
4 | |
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.