SQL QUERY HELP!

• Ysa

  Mr or Mrs. 500

  Points: 553

  More actions

  July 26, 2018 at 6:44 pm

  #354951

  Hello

  I need help to build a Yearly Budget, my query bring all the year but I need the total by years as well

  Please help

  **************
  select
  g.YEAR [Year],
  a.ACTNUMST Account,
  g.ACTDESCR [Description],
  c.ACCATDSC Category,
  sum(case g.PERIODID when 0
    then g.PERDBLNC else 0 end) Beginning_Balance,
  sum(case when g.PERIODID <= 1
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jan_Balance,
  sum(case when g.PERIODID <= 2
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Feb_Balance,
  sum(case when g.PERIODID <= 3
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Mar_Balance,
  sum(case when g.PERIODID <= 4
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Apr_Balance,
  sum(case when g.PERIODID <= 5
    then g.DEBITAMT-g.CRDTAMNT else 0 end) May_Balance,
  sum(case when g.PERIODID <= 6
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jun_Balance,
  sum(case when g.PERIODID <= 7
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jul_Balance,
  sum(case when g.PERIODID <= 8
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Aug_Balance,
  sum(case when g.PERIODID <= 9
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Sep_Balance,
  sum(case when g.PERIODID <= 10
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Oct_Balance,
  sum(case when g.PERIODID <= 11
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Nov_Balance,
  sum(case when g.PERIODID <= 12
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Dec_Balance

  from GL11110 g --GL summary data

  inner join GL00102 c --categories
  on g.ACCATNUM = c.ACCATNUM

  inner join GL00105 a --for account number
  on g.ACTINDX = a.ACTINDX

  where g.ACCTTYPE = 1
  and g.YEAR = (select min(YEAR) from GL11110)

  group by g.YEAR, g.ACTDESCR, a.ACTNUMST, c.ACCATDSC

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  July 27, 2018 at 12:27 am

  #1999198

  marin88321 - Thursday, July 26, 2018 6:44 PM

  Hello

  I need help to build a Yearly Budget, my query bring all the year but I need the total by years as well

  Please help

  **************
  select
  g.YEAR [Year],
  a.ACTNUMST Account,
  g.ACTDESCR [Description],
  c.ACCATDSC Category,
  sum(case g.PERIODID when 0
    then g.PERDBLNC else 0 end) Beginning_Balance,
  sum(case when g.PERIODID <= 1
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jan_Balance,
  sum(case when g.PERIODID <= 2
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Feb_Balance,
  sum(case when g.PERIODID <= 3
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Mar_Balance,
  sum(case when g.PERIODID <= 4
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Apr_Balance,
  sum(case when g.PERIODID <= 5
    then g.DEBITAMT-g.CRDTAMNT else 0 end) May_Balance,
  sum(case when g.PERIODID <= 6
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jun_Balance,
  sum(case when g.PERIODID <= 7
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jul_Balance,
  sum(case when g.PERIODID <= 8
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Aug_Balance,
  sum(case when g.PERIODID <= 9
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Sep_Balance,
  sum(case when g.PERIODID <= 10
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Oct_Balance,
  sum(case when g.PERIODID <= 11
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Nov_Balance,
  sum(case when g.PERIODID <= 12
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Dec_Balance

  from GL11110 g --GL summary data

  inner join GL00102 c --categories
  on g.ACCATNUM = c.ACCATNUM

  inner join GL00105 a --for account number
  on g.ACTINDX = a.ACTINDX

  where g.ACCTTYPE = 1
  and g.YEAR = (select min(YEAR) from GL11110)

  group by g.YEAR, g.ACTDESCR, a.ACTNUMST, c.ACCATDSC

  Kindly provide us the sample test data with expected output.

  Saravanan

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  July 27, 2018 at 4:14 am

  #1999211

  You could do this.... not sure if it's what you mean by "Total by Years".

  USE [tempdb]
GO

  -- Test data:

  drop table GL11110
drop table GL00102
drop table GL00105

  create table GL11110 (ACCATNUM int, ACTINDX int, ACCTTYPE int, [Year] char(4), ACTDESCR varchar(50), PERIODID int, PERDBLNC decimal(9,2), DEBITAMT decimal(9,2), CRDTAMNT decimal(9,2))

  create table GL00102 (ACCATNUM int, ACCATDSC varchar(10))

  create table GL00105 (ACTINDX int, ACTNUMST varchar(10))

  insert GL11110
values (1, 1, 1, 2018, 'Test 1', 0, 1000.00, null, null),
        (1, 1, 1, 2018, 'Test 1', 2, null, 1500.00, 350.00),
        (1, 1, 1, 2018, 'Test 1', 3, null, 2000.00, 0.00),
        (1, 1, 1, 2018, 'Test 1', 3, null, 200.00, 0.00),
        (1, 1, 1, 2018, 'Test 1', 12, null, 475.35, 0.00)

  insert GL11110
values (2, 2, 1, 2018, 'Test 2', 0, 500.00, null, null),
        (2, 2, 1, 2018, 'Test 2', 2, null, 1700.00, 225.00),
        (2, 2, 1, 2018, 'Test 2', 3, null, 2000.00, 0.00),
        (2, 2, 1, 2018, 'Test 2', 3, null, 200.00, 0.00),
        (2, 2, 1, 2018, 'Test 2', 11, null, 350.75, 2.50)

  insert GL00102
values (1, 'Category 1'),
        (2, 'Category 2')

  insert GL00105
values (1, 100),
        (2, 202)

  -- Query:

  ;with cte as
(
select
    g.YEAR [Year],
    a.ACTNUMST Account,
    g.ACTDESCR [Description],
    c.ACCATDSC Category,
    sum(case g.PERIODID when 0
    then g.PERDBLNC else 0 end) Beginning_Balance,
    sum(case when g.PERIODID <= 1
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jan_Balance,
    sum(case when g.PERIODID <= 2
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Feb_Balance,
    sum(case when g.PERIODID <= 3
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Mar_Balance,
    sum(case when g.PERIODID <= 4
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Apr_Balance,
    sum(case when g.PERIODID <= 5
    then g.DEBITAMT-g.CRDTAMNT else 0 end) May_Balance,
    sum(case when g.PERIODID <= 6
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jun_Balance,
    sum(case when g.PERIODID <= 7
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jul_Balance,
    sum(case when g.PERIODID <= 8
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Aug_Balance,
    sum(case when g.PERIODID <= 9
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Sep_Balance,
    sum(case when g.PERIODID <= 10
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Oct_Balance,
    sum(case when g.PERIODID <= 11
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Nov_Balance,
    sum(case when g.PERIODID <= 12
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Dec_Balance

  from GL11110 g --GL summary data

  inner join GL00102 c --categories
    on g.ACCATNUM = c.ACCATNUM

  inner join GL00105 a --for account number
    on g.ACTINDX = a.ACTINDX

  where g.ACCTTYPE = 1
    and g.YEAR = (select min(YEAR) from GL11110)

  group by g.YEAR, g.ACTDESCR, a.ACTNUMST, c.ACCATDSC
)
select *
    ,sum(dec_balance) over (partition by [year]) Year_Total
from CTE
order by [Year], Account, [Description], Category

• Ysa

  Mr or Mrs. 500

  Points: 553

  More actions

  July 27, 2018 at 5:39 am

  #1999215

  I will test it

  Thank you!!