Query with sub query and sum column

[Former Member]

Hi I've got this here query which I’ve cut down significantly and works fine:

Declare @StartDate as Datetime declare @EndDate as DAtetime Set @StartDate = '2017/01/01' Set @EndDate = '2017/01/31' SELECT AcctCode, AcctName, Postable , isnull(( Select Sum(Debit - Credit) From JDT1 where Account = AcctCode and Refdate < @StartDate),0) as [Opening Balance] FROM dbo.OACT where postable = 'Y' order By AcctCode

Now I’m just trying to add in the following fields profitcode, ocrcode2 but keep failing. Tried the below and a number of other variations of it.

Declare @StartDate as Datetime declare @EndDate as DAtetime Set @StartDate = '2017/01/01' Set @EndDate = '2017/01/31' SELECT AcctCode, AcctName, Postable , isnull((( Select ProfitCode, OcrCode2, Sum(Debit - Credit) From JDT1 where Account = AcctCode and Refdate < @StartDate),0) group by ProfitCode, OcrCode2) as [Opening Balance] FROM dbo.OACT where postable = 'Y' order By AcctCode

Can you spot where it is I am going wrong? I'm sure it is something simple I am missing here that I can't find.

Thanks in advance

[kothandaraman_nagarajan]

Only possible to get one field from sub query. May be try to link OATC and JDT1.

[kothandaraman_nagarajan]

Hi,

Try this query,

Declare @StartDate as Datetime declare @EndDate as DAtetime Set @StartDate = '20170101' Set @EndDate = '20170131' SELECT T1.AcctCode, T1.AcctName, T1.Postable , ISNULL(( Select Sum(T0.Debit - T0.Credit) From JDT1 T0 inner join OACT Ta on Ta.AcctCode = T0.Account Where Refdate < @StartDate),0) as [Opening Balance] FROM OACT T1 where T1.postable = 'Y' order By T1.AcctCode

Regards,

Nagarajan

[Former Member]

Hi Nagarajan,

that query unfortunately did not work. It is returning, but it has 0 balance in the Opening Balance which is incorrect and does not include the fields I am trying to add profitcode, ocrcode2. What I am trying to do is on the below query add the two new fields at the start of the query and group them together.

ORIGINAL:

Declare @StartDate as Datetime declare @EndDate as DAtetime Set @StartDate = '2017/01/01' Set @EndDate = '2017/01/31' SELECT AcctCode, AcctName, Postable , isnull(( Select Sum(Debit - Credit) From JDT1 where Account = AcctCode and Refdate < @StartDate),0) as [Opening Balance] FROM dbo.OACT where postable = 'Y' order By AcctCode

FAILED ADJUSTMENT:

Declare @StartDate as Datetime declare @EndDate as DAtetime Set @StartDate = '2017/01/01' Set @EndDate = '2017/01/31' SELECT AcctCode, AcctName, Postable , isnull((( Select ProfitCode, OcrCode2, Sum(Debit - Credit) From JDT1 where Account = AcctCode and Refdate < @StartDate),0) group by ProfitCode, OcrCode2) as [Opening Balance] FROM dbo.OACT where postable = 'Y' order By AcctCode

Any more ideas Nagarajan? Thanks again