Skip to Content
avatar image
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

  • Get RSS Feed

2 Answers

  • 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

  • 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