libro mayor y balances

[Former Member]

hola a todos!!!

tengo el siguiente informe para el libro mayor:

/SELECT FROM [dbo].[OINV] P0/

declare @fromdate as datetime

/* WHERE */

set @fromdate = /* P0.DocDate */ '[%0]'

/SELECT FROM [dbo].[OINV] P1/

declare @Todate as datetime

/* WHERE */

set @Todate = /* P1.DocDate */ '[%1]'

/SELECT FROM [dbo].[OACT] P3/

declare @FromAcct as nvarchar(20)

/* WHERE */

set @FromAcct = /* P3.AcctCode */ '[%2]'

/SELECT FROM [dbo].[OACT] P4/

declare @ToAcct as nvarchar(20)

/* WHERE */

set @ToAcct = /* P4.AcctCode */ '[%3]'

Select T10.AcctCode as 'Cuenta_1', T10.AcctName as 'Nombre Cuenta 1',

T8.AcctCode as 'Cuenta_2', T8.AcctName as 'Nombre Cuenta 2',

T7.AcctCode as 'Cuenta_3', T7.AcctName as 'Nombre Cuenta 3',

isnull(SUM(T9.SI),0) as 'saldo Inicial', isnull(SUM(T9.MD),0) as 'Movimiento Debito',

isnull(SUM(T9.MC),0) as 'Movimiento Credito', isnull(SUM(T9.SF),0) as 'Saldo Final'

FROM (

SELECT case when T2.levels=5 then T5.FatherNum when T2.levels=4 then T4.FatherNum when T2.Levels=3

then T3.FatherNum when T2.Levels=2 then T2.FatherNum else T2.AcctCode

end as Cuenta_1,case when T2.levels=5 then T4.FatherNum when T2.levels=4 then T3.FatherNum when T2.Levels=3

then T2.FatherNum else T2.AcctCode

end as Cuenta_2,case when T2.levels=5 then T3.FatherNum when T2.levels=4 then T2.FatherNum else

T2.AcctCode end as Cuenta_3, case when T1.RefDate< @fromdate then SUM(isnull(T1.debit,0)-

isnull(T1.Credit,0)) else 0 end as SI, case when T1.RefDate between @fromdate and @Todate

then SUM(isnull(T1.Credit,0)) else 0 end as MC, case when T1.RefDate between @fromdate and @Todate

then SUM(isnull(T1.Debit,0)) else 0 end as MD, case when T1.RefDate<= @Todate

then SUM(isnull(T1.debit,0)-isnull(T1.Credit,0)) else 0 end as SF

FROM OJDT T0 inner join JDT1 T1 On T0.TransID=T1.TransID inner join

(select TA1.AcctCode, TA1.FatherNum, TA1.Levels from OACT TA1) T2 On T2.AcctCode=T1.Account left join

(select TA2.AcctCode, TA2.FatherNum, TA2.Levels from OACT TA2) T3 On T3.AcctCode=T2.Fathernum left join

(select TA3.AcctCode, TA3.FatherNum, TA3.Levels from OACT TA3) T4 On T4.AcctCode=T3.Fathernum left join

(select TA4.AcctCode, TA4.FatherNum, TA4.Levels from OACT TA4) T5 On T5.AcctCode=T4.Fathernum

group by T0.TRansType, T2.FatherNum, T3.FatherNum, t2.levels,

t3.levels, T2.AcctCode, T4.FatherNum, T3.AcctCode, T5.FatherNum, T1.RefDate) as T9

inner join (select TA5.AcctCode, TA5.AcctName from OACT TA5) T7 on T7.AcctCode=T9.Cuenta_3

inner join (select TA6.AcctCode, TA6.AcctName from OACT TA6) T8 on T8.AcctCode=T9.Cuenta_2

inner join (select TA6.AcctCode, TA6.AcctName from OACT TA6) T10 on T10.AcctCode=T9.Cuenta_1

WHERE T10.AcctCode between @FromAcct and @ToAcct AND T8.AcctCode between @FromAcct and @ToAcct

AND T7.AcctCode between @FromAcct and @ToAcct

group by T7.AcctCode, T7.AcctName, T8.AcctCode, T8.AcctName, T10.AcctCode, T10.AcctName

necesito que lo que me muestra en saldos iniciales me los divida en dos campos debito y credito pero no trayendolos tal cual de la tabla JDT1 el valor credito y debito sino que me muestre netos los valores, si la sumatoria da negativo la muestre en una columna llamada credito pero si por el contrario da la sumatoria positiva entonces la muestre en una columna debito, ya que si las traigo directamente de la tabla JDT1

Edited by: jcarito on Aug 30, 2010 2:39 PM

[Former Member]

listo