Skip to Content
author's profile photo Former Member
Former Member

Query with sub query and sum column

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

Add comment
10|10000 characters needed characters exceeded

2 Answers

  • Posted on Feb 22, 2017 at 02:27 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Posted on Feb 23, 2017 at 01:17 AM

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

    Add comment
    10|10000 characters needed characters exceeded