detail Info

[former_member193355]

Hi Experts,

Our technical created query bellows:

declare @currblnce1 nvarchar(50) declare @currblnce2 nvarchar(50)
declare @currblnce3 nvarchar(50) declare @currblnce4 nvarchar(50)
declare @currblnce5 nvarchar(50) declare @currblnce11 nvarchar(50)
declare @currblnce21 nvarchar(50) declare @currblnce31 nvarchar(50)
declare @currblnce41 nvarchar(50) declare @currblnce51 nvarchar(50)
declare @acct1 nvarchar(20) declare @acct2 nvarchar(20)
declare @acct3 nvarchar(20) declare @acct4 nvarchar(20)
declare @acct5 nvarchar(20) 
select @acct1 = oact.acctcode from oact where (oact.acctcode = '1310000')
select @acct2 = oact.acctcode from oact where (oact.acctcode = '1320000')
select @acct3 = oact.acctcode from oact where (oact.acctcode = '1330000')
select @acct4 = oact.acctcode from oact where (oact.acctcode = '1340000')
select @acct5 = oact.acctcode from oact where (oact.acctcode = '1350000')
set @currblnce1 = (SELECT sum(-t1.credit+t1.debit) FROM OJDT T2 inner join
jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit > 0 or t1.debit > 0) and
(t1.account = @acct1) and t2.refdate between '01/01/2009'
and '06/30/2009' group by t1.account) set @currblnce2 = (SELECT sum(-t1.credit+t1.debit) 
FROM OJDT T2 inner join jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit > 0 or t1.debit > 0) and
(t1.account = @acct2) and t2.refdate between '01/01/2009' and '06/30/2009' group by t1.account)
set @currblnce3 = (SELECT sum(-t1.credit+t1.debit)  FROM OJDT T2 inner join
jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit > 0 or t1.debit > 0) and
(t1.account = @acct3) and t2.refdate between '01/01/2009' and '06/30/2009' group by t1.account)
set @currblnce4 = (SELECT sum(-t1.credit+t1.debit) FROM OJDT T2 inner join
jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit > 0 or t1.debit > 0) and
(t1.account = @acct4) and t2.refdate between '01/01/2009' and '06/30/2009' group by t1.account)
set @currblnce5 = (SELECT sum(-t1.credit+t1.debit) FROM OJDT T2 inner join
jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit > 0 or t1.debit > 0) and
(t1.account = @acct5) and t2.refdate between '01/01/2009' and '06/30/2009' group by t1.account)
set @currblnce11 = (SELECT sum(-t1.credit+t1.debit) FROM OJDT T2 inner join
jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit > 0 or t1.debit > 0) and  (t1.account = @acct1) and t2.refdate between '01/01/2004' and '06/30/2009' group by t1.account) set @currblnce21 = (SELECT sum(-t1.credit+t1.debit) 
FROM OJDT T2 inner join jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit > 0 or t1.debit > 0) and
(t1.account = @acct2) and t2.refdate between '01/01/2004' and '06/30/2009' group by t1.account)
set @currblnce31 = (SELECT sum(-t1.credit+t1.debit)  FROM OJDT T2 inner join
jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit > 0 or t1.debit > 0) and (t1.account = @acct3) and t2.refdate between '01/01/2004' and '06/30/2009' group by t1.account) set @currblnce41 = (SELECT sum(-t1.credit+t1.debit)  
FROM OJDT T2 inner join jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit > 0 or t1.debit > 0) and
(t1.account = @acct4) and t2.refdate between '01/01/2004' and '06/30/2009' group by t1.account)
set @currblnce51 = (SELECT sum(-t1.credit+t1.debit)  FROM OJDT T2 inner join
jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit > 0 or t1.debit > 0) and 
(t1.account = @acct5) and t2.refdate between '01/01/2004' and '06/30/2009' group by t1.account)
SELECT t1.account, case 
when t1.account = @acct1 then @currblnce11
when t1.account = @acct2 then @currblnce21
when t1.account = @acct3 then @currblnce31
when t1.account = @acct4 then @currblnce41
when t1.account = @acct5 then @currblnce51
else null end as 'O.B. From start of fiscal year',
case 
when t1.account = @acct1 then @currblnce1
when t1.account = @acct2 then @currblnce2
when t1.account = @acct3 then @currblnce3
when t1.account = @acct4 then @currblnce4
when t1.account = @acct5 then @currblnce5
else null end as 'O.B. From start of fiscal year',
sum(t1.debit)"Debit", sum(t1.credit)"Credit", 
case when t1.account = @acct1 then @currblnce1+(sum(t1.debit-t1.credit))
when t1.account = @acct2 then @currblnce2+(sum(t1.debit-t1.credit))
when t1.account = @acct3 then @currblnce3+(sum(t1.debit-t1.credit))
when t1.account = @acct4 then @currblnce4+(sum(t1.debit-t1.credit))
when t1.account = @acct5 then @currblnce5+(sum(t1.debit-t1.credit)) else null end as 'Balance'
FROM OJDT T2 inner join jdt1 t1 on t1.transid = t2.transid WHERE (t1.credit
> 0 or t1.debit > 0) and (t1.account between '1310000' and '1350000') and t2.refdate
between '07/01/2009' and '07/31/2009' group by t1.account order by t1.account asc

it will be used as a trial balance report query and the result would be used to compare it with trial balance report standard SBO 2007A. Is the query correct ? if yes, how to replace detail account code declarations to be one declaration only ? I would like to replace @acct1 until @acct5 to be @acct only ?

Rgd,

Steve

[Former Member · Accepted solution]

Hi Steve,

There is no way to tell if this query is Correct or not because we could not test. However, your requirement to make it only one account may not be doable if you need to get the account balance for those 5 accounts.

Thanks,

Gordon