Sum Negative Numbers

  • Hi All,

    I have a column GNLBalance with negative and positive balances. I want to add these numbers together when another column GNLFSMID in the same table is equal to a series of numbers.

    I need the total closing\opening balance for each client with there account, account group & division.

    Balance Buisness Rules:

    2013 Opening balance = Closing balance 2012 (opening 2012 + Jan 12 to Dec 12 balance)

    Closing balance 2013 = Opening + Jan13 to Current month balance

    So

    Opening: FSMID 113 to 125

    Closing: FSMID (113 to 125) + 127,128,129,130

    DDL

    Table GeneralLedger

    GNLIDGNLCLIIDGNLCOAIDGNLFSMIDGNLBalanceBase

    11385576 113 -4845.0000

    21385713 114 -395.8500

    3313939 115 8703.3400

    472739 116 -1321.6500

    572739 117 -8811.0000

    63139713 118 -15416.5200

    71480713 119 18429.9200

    8314439 120 1321.6500

    91480713 121 -10799.0000

    103144576 122 4371.0000

    113139713 123 1619.8500

    1272745 124 1786.1400

    13313939 125 46.4200

    14727576 127 -12802.4000

    151480713 128 1198.2600

    161480713 129 -1785.0000

    173139713 130 800.0000

    Table Clients

    CLIIDCLICodeCLIName

    727HARKHARK CONSULTING

    13853HPARC3HARCHITECTS

    1480GUYCARGUY CARPENTER

    3139ABNAPABB PTE LTD

    3144SYSACCSYSTEM ACCESS

    TABLE ChartOfAccount

    COAIDCOANAME1COACode2CodeCOACode2NAme2COAAGPID

    39Total BillingsRV10 RV10 Billings1

    45Prod BillingRV40 RV40 Revenue2

    576Loan FD100G FD100G Financial Debts3

    713ReceivablesFA301G FA301G Loans Advances4

    Table AccountGroup

    AGPIDAGPNAME1

    1Assets

    2Goodwill

    3Deferred

    4Trade

    5Cash

    Table FiscalMonth

    FSMIDFSMNAME1

    113Opening Balance 12

    114Jan 12

    115Feb 12

    116Mar 12

    117Apr 12

    118May 12

    119Jun 12

    120Jul 12

    121Aug 12

    122Sep 12

    123Oct 12

    124Nov 12

    125Dec 12

    127Opening Balance 13

    128Jan 13

    129Feb 13

    130Mar 13

    Table ClientOwner

    CLOIDCLOCLIIDCLODIVID

    17271

    213852

    314803

    431393

    531444

    67275

    Table Division

    DIVIDDIVName1

    1Digital

    2Zenith

    3Stars

    4MSL

    5Leo

    Expected Results

    Client code(CLICODE),GL Account Group(AGPNAME1),GL Account(COANAME1),

    3HPARC, Deferred, Loan,

    3HPARC, Trade, Receivables,

    HARK, Assests, Total Billings,

    GUYCAR, Trade, Receivables,

    --continue on same line

    Closing balance(Total of GNLBalanceBase when FSMID = 113 to 130),Division(DIVName1),GLChartCode(COACode2Code),

    -4845, Zenith, FD100G

    -395.85, Zenith, FA301G

    -10132.65, Digital, RV10

    7044.18, Stars, FA301G

    select distinct

    c.CLIName1 as 'Client',

    c.CLICode as 'Client Code',

    g.AGPName1 as 'GL Account Group',

    a.COAName1 as 'GL Account',

    CASE WHEN l.GNLFSMID IN(113,114,115,116,117,118,119,120,121,122,123,124,125,127,128,129,130)

    THEN SUM(l.GNLBalanceBase) ELSE '0' END AS [Closing Balance],

    d.DIVName1 as 'Division',

    d.DIVName2 as 'Division (2)',

    t.CLTName1 as 'Client Type',

    a.[COACode2Code] as 'GLChart Code 2',

    a.COACode2Name1 as 'GLChart Code 2 Name',

    CASE WHEN l.GNLFSMID IN(113,114,115,116,117,118,119,120,121,122,123,124,125)

    THEN SUM(l.GNLBalanceBase) ELSE '0' END AS [Opening Balance]

    from vwClient c

    inner join [vwChartOfAccount] a

    on a.[COASourceID] = c.[CLISourceID]

    inner join [vwChartOfAccountGroup] g

    on g.[AGPID] = a.COAAGPID

    inner join [vwGeneralLedger] l

    on l.GNLCLIID = c.[CLIID]

    inner join [vwclientOwner] o

    on o.CLOID = c.CLIID

    inner join [vwDivision] d

    on d.DIVID = o.CLODIVID

    inner join [vwClientType] t

    on t.CLTID = c.[CLICLTID]

    inner join [DW].[vwFiscalMonth] m

    on l.GNLFSMID = m.FSMID

    group by

    c.CLIName1,

    c.CLICode,

    g.AGPName1,

    a.COAName1,

    l.GNLFSMID,

    d.DIVName1,

    d.DIVName2,

    t.CLTName1,

    a.COACode2Code,

    a.COACode2Name1

  • See if any of this helps. I had to make a lot of guesses and changes to the sample data to get it to work.

    ;WITH

    cteGeneralLedger

    (GNLID,GNLCLIID,GNLCOAID,GNLFSMID,GNLBalanceBase)

    AS (

    SELECT 1,1385,576,113,-4845.0000 UNION ALL

    SELECT 2,1385,713,114,-395.8500 UNION ALL

    SELECT 3,3139,39,115,8703.3400 UNION ALL

    SELECT 4,727,39,116,-1321.6500 UNION ALL

    SELECT 5,727,39,117,-8811.0000 UNION ALL

    SELECT 6,3139,713,118,-15416.5200 UNION ALL

    SELECT 7,1480,713,119,18429.9200 UNION ALL

    SELECT 8,3144,39,120,1321.6500 UNION ALL

    SELECT 9,1480,713,121,-10799.0000 UNION ALL

    SELECT 10,3144,576,122,4371.0000 UNION ALL

    SELECT 11,3139,713,123,1619.8500 UNION ALL

    SELECT 12,727,45,124,1786.1400 UNION ALL

    SELECT 13,3139,39,125,46.4200 UNION ALL

    SELECT 14,727,576,127,-12802.4000 UNION ALL

    SELECT 15,1480,713,128,1198.2600 UNION ALL

    SELECT 16,1480,713,129,-1785.0000 UNION ALL

    SELECT 17,3139,713,130,800.0000

    ),

    cteClients

    (CLIID,CLICode,CLIName)

    AS (

    SELECT 727,'HARK','HARK CONSULTING' UNION ALL

    SELECT 1385,'3HPARC','3HARCHITECTS' UNION ALL

    SELECT 1480,'GUYCAR','GUY CARPENTER' UNION ALL

    SELECT 3139,'ABNAP','ABB PTE LTD' UNION ALL

    SELECT 3144,'SYSACC','SYSTEM ACCESS'

    ),

    cteChartOfAccount

    (COAID,COANAME1,COACode2Code,COACode2NAme2,COAAGPID)

    AS (

    SELECT 39,'Total Billings','RV10','RV10 Billings',1 UNION ALL

    SELECT 45,'Prod Billing','RV40','RV40 Revenue',2 UNION ALL

    SELECT 576,'Loan','FD100G','FD100G Financial Debts',3 UNION ALL

    SELECT 713,'Receivables','FA301G','FA301G Loans Advances',4

    ),

    cteAccountGroup

    (AGPID,AGPNAME1)

    AS (

    SELECT 1,'Assets' UNION ALL

    SELECT 2,'Goodwill' UNION ALL

    SELECT 3,'Deferred' UNION ALL

    SELECT 4,'Trade' UNION ALL

    SELECT 5,'Cash'

    ),

    cteFiscalMonth

    (FSMID,FSMNAME1)

    AS (

    SELECT 113,'Opening Balance 12' UNION ALL

    SELECT 114,'Jan 12' UNION ALL

    SELECT 115,'Feb 12' UNION ALL

    SELECT 116,'Mar 12' UNION ALL

    SELECT 117,'Apr 12' UNION ALL

    SELECT 118,'May 12' UNION ALL

    SELECT 119,'Jun 12' UNION ALL

    SELECT 120,'Jul 12' UNION ALL

    SELECT 121,'Aug 12' UNION ALL

    SELECT 122,'Sep 12' UNION ALL

    SELECT 123,'Oct 12' UNION ALL

    SELECT 124,'Nov 12' UNION ALL

    SELECT 125,'Dec 12' UNION ALL

    SELECT 127,'Opening Balance 13' UNION ALL

    SELECT 128,'Jan 13' UNION ALL

    SELECT 129,'Feb 13' UNION ALL

    SELECT 130,'Mar 13'

    ),

    cteClientOwner

    (CLOID,CLOCLIID,CLODIVID)

    AS (

    SELECT 1,727,1 UNION ALL

    SELECT 2,1385,2 UNION ALL

    SELECT 3,1480,3 UNION ALL

    SELECT 4,3139,3 UNION ALL

    SELECT 5,3144,4 UNION ALL

    SELECT 6,727,5

    ),

    cteDivision

    (DIVID,DIVName1)

    AS (

    SELECT 1,'Digital' UNION ALL

    SELECT 2,'Zenith' UNION ALL

    SELECT 3,'Stars' UNION ALL

    SELECT 4,'MSL' UNION ALL

    SELECT 5,'Leo'

    )

    SELECT DISTINCT

    c.CLIName AS 'Client'

    ,c.CLICode AS 'Client Code'

    ,l.GNLID AS 'GL Account ID'

    ,g.AGPName1 as 'GL Account Group'

    ,a.COAName1 AS 'GL Account'

    ,SUM(l.GNLBalanceBase) OVER (PARTITION BY l.GNLFSMID) AS 'Closing Balance'

    ,d.DIVName1 AS 'Division'

    ,a.[COACode2Code] AS 'GLChart Code 2'

    ,a.COACode2NAme2 AS 'GLChart Code 2 Name'

    ,(CASE

    WHEN l.GNLFSMID IN (113,114,115,116,117,118,119,120,121,122,123,124,125)

    THEN SUM(l.GNLBalanceBase) OVER (PARTITION BY l.GNLFSMID)

    ELSE '0'

    END) AS 'Opening Balance'

    FROM

    cteClients c

    INNER JOIN [cteGeneralLedger] l

    ON l.GNLCLIID = c.CLIID

    INNER JOIN [cteChartOfAccount] a

    ON l.GNLCOAID = a.COAID

    INNER JOIN [cteAccountGroup] g

    ON g.AGPID = a.COAAGPID

    INNER JOIN [cteclientOwner] o

    ON o.CLOCLIID = c.CLIID

    INNER JOIN [cteDivision] d

    ON d.DIVID = o.CLODIVID

    INNER JOIN [cteFiscalMonth] m

    ON l.GNLFSMID = m.FSMID

     

  • Many thanks for the reply. I have slightly modified the code for the actual views. But it is still not group the data togther.

    ;WITH

    cteGeneralLedger

    (GNLID,GNLCLIID,GNLCOAID,GNLFSMID,GNLBalanceBase)

    AS (

    SELECT GNLID,GNLCLIID,GNLCOAID,GNLFSMID,GNLBalanceBase

    FROM DW.vwGeneralLedger

    where GNLCOAID in (614,616,618,1019)

    ),

    cteClients

    (CLIID,CLICLTID,CLICode,CLIName)

    AS (

    SELECT CLIID,CLICLTID,CLICode,CLIName1

    FROM DW.vwClient

    ),

    cteChartOfAccount

    (COAID,COANAME1,COACode2Code,COACode2NAme2,COAAGPID)

    AS (

    SELECT COAID,COANAME1,COACode2Code,COACode2NAme2,COAAGPID

    FROM DW.vwChartOfAccount

    where COACode2Code in ('FA301G','FD100G')

    ),

    cteAccountGroup

    (AGPID,AGPNAME1)

    AS (

    SELECT AGPID,AGPNAME1

    FROM DW.vwChartOfAccountGroup

    ),

    cteFiscalMonth

    (FSMID,FSMNAME1)

    AS (

    SELECT FSMID,FSMNAME1

    FROM DW.vwFiscalMonth

    ),

    cteClientOwner

    (CLOID,CLOCLIID,CLODIVID)

    AS (

    SELECT CLOID,CLOCLIID,CLODIVID

    FROM DW.vwClientOwner

    ),

    cteDivision

    (DIVID,DIVName1)

    AS (

    SELECT DIVID,DIVName1

    FROM DW.vwDivision

    ),

    cteClientType

    (CLTID,CLTName1)

    AS(

    SELECT CLTID,CLTName1

    FROM dw.[vwClientType]

    )

    SELECT DISTINCT

    c.CLIName AS 'Client'

    ,c.CLICode AS 'Client Code'

    ,g.AGPName1 as 'GL Account Group'

    ,a.COAName1 AS 'GL Account'

    ,(CASE

    WHEN l.GNLFSMID IN (113,114,115,116,117,118,119,120,121,122,123,124,125,127,128,129,130)

    THEN SUM(l.GNLBalanceBase) OVER (PARTITION BY l.GNLFSMID)

    ELSE '0'

    END) AS 'Closing Balance'

    ,d.DIVName1 AS 'Division'

    ,a.[COACode2Code] AS 'GLChart Code 2'

    ,a.COACode2NAme2 AS 'GLChart Code 2 Name'

    ,t.CLTName1 AS 'Client Type'

    ,(CASE

    WHEN l.GNLFSMID IN (113,114,115,116,117,118,119,120,121,122,123,124,125)

    THEN SUM(l.GNLBalanceBase) OVER (PARTITION BY l.GNLFSMID)

    ELSE '0'

    END) AS 'Opening Balance'

    FROM

    cteClients c

    INNER JOIN [cteGeneralLedger] l

    ON l.GNLCLIID = c.CLIID

    INNER JOIN [cteChartOfAccount] a

    ON l.GNLCOAID = a.COAID

    INNER JOIN [cteAccountGroup] g

    ON g.AGPID = a.COAAGPID

    INNER JOIN [cteclientOwner] o

    ON o.CLOCLIID = c.CLIID

    INNER JOIN [cteDivision] d

    ON d.DIVID = o.CLODIVID

    INNER JOIN [cteFiscalMonth] m

    ON l.GNLFSMID = m.FSMID

    INNER JOIN [cteClientType] t

    ON t.CLTID = c.CLICLTID

    order by d.DIVName1

    Current:

    Client,Client Code,GL Account Group,GL Account,Closing BalanceDivision,GLChart Code 2,GLChart Code 2 Name,Client Type,Opening Balance

    HOUSE,HOUSE,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),-181819127.3600,Alpha 245,FD100G,FD100G - IC Financial Debts,House,0.0000

    HOUSE,HOUSE,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),-175767596.3400,Alpha 245,FD100G,FD100G - IC Financial Debts,House,-175767596.3400

    HOUSE,HOUSE,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),0.0000,Alpha 245,FD100G,FD100G - IC Financial Debts,House,0.0000

    LION RE:SOURCES SINGAPORE,SG5865SG,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),-181819127.3600,Alpha 245,FD100G,FD100G - IC Financial Debts,Intercompany,0.0000

    LION RE:SOURCES SINGAPORE,SG5865SG,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),-175767596.3400,Alpha 245,FD100G,FD100G - IC Financial Debts,Intercompany,-175767596.3400

    LION RE:SOURCES SINGAPORE,SG5865SG,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),0.0000,Alpha 245,FD100G,FD100G - IC Financial Debts,Intercompany,0.0000

    Expected

    ClientClient CodeGL Account GroupGL AccountClosing BalanceDivisionGLChart Code 2GLChart Code 2 NameClient TypeOpening Balance

    HOUSEHOUSEIntercompany-AP TradeInterco Payables (to FR5507 PFS)-357586723.70Alpha 245FD100GFD100G - IC Financial DebtsHouse-175767596.3

    LION RE:SOURCES SINGAPORESG5865SGIntercompany-AP TradeInterco Payables (to FR5507 PFS)-357586723.70Alpha 245FD100GFD100G - IC Financial DebtsIntercompany-175767596.3

    The client, client code, GL account group, gl account, division, gl chart code 2, glchart code 2 name, client type should only appear once with the balances negative and postive numbers added togther.

  • This code returns exactly the right number of rows and the numbers in the 'closing balance' column are right.

    But there is only one calculated field 'closing balance' and when I try to a second calculated field 'opening balance' it all goes wrong. It adds the wrong numbers and the number of rows goes up. See second lot of SQL code with the case statements.

    Can anyone tell me what is wrong with the case statements.

    select DISTINCT

    c.CLIName1,

    c.CLICode,

    g.AGPName1 as [GL Account Group],

    a.COAName1 as [GL Account],

    SUM(l.GNLBalanceBase) AS 'Closing Balance',

    d.DIVName1,

    a.COACode2Code as [GLChart Code 2],

    a.COACode2Name1 as [GLChart Code 2 Name],

    t.CLTName1 as [Client Type]

    from DW.vwChartOfAccount a

    inner join DW.vwChartOfAccountGroup g

    on a.COAAGPID = g.AGPID

    inner join DW.vwGeneralLedger l

    on a.COAID = l.GNLCOAID

    inner join DW.vwClient c

    on l.GNLCLIID = c.CLIID

    inner join DW.vwClientType t

    on c.CLICLTID = t.CLTID

    inner join DW.vwDivision d

    on l.GNLDIVID = d.DIVID

    where COACode2Code in ('FA301G','FD100G')

    and l.GNLFSMID IN (127,128,129,130)

    GROUP BY

    CLIName1

    ,CLICode

    ,AGPName1

    ,COAName1

    ,DIVName1

    ,COACode2Code

    ,COACode2Name1

    ,CLTName1

    order by d.DIVName1

    Correct Samples:

    ClientClient CodeGL Account GroupGL AccountClosing Balance DivisionGLChart Code 2GLChart Code 2 NameClient Type

    None,None,Intercompany-AR Trade,Interco Receivables (fr FR5507PFS),-491296.58,ARC Worldwide,FA301G,FA301G - IC loans, advances and deposits

    None,None,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),5059096.78,ARC Worldwide,FD100G,FD100G - IC Financial Debts

    select DISTINCT

    c.CLIName1

    ,c.CLICode

    ,g.AGPName1 as [GL Account Group]

    ,a.COAName1 as [GL Account]

    ,(CASE

    WHEN l.GNLFSMID IN (127,128,129,130)

    THEN SUM(l.GNLBalanceBase) OVER (PARTITION BY l.GNLCOAID)

    ELSE '0'

    END) AS 'Closing Balance'

    ,d.DIVName1

    ,a.COACode2Code as [GLChart Code 2]

    ,a.COACode2Name1 as [GLChart Code 2 Name]

    ,t.CLTName1 as [Client Type]

    ,(CASE

    WHEN l.GNLFSMID IN (127)

    THEN SUM(l.GNLBalanceBase) OVER (PARTITION BY l.GNLCOAID)

    ELSE '0'

    END) AS 'Opening Balance'

    from DW.vwChartOfAccount a

    inner join DW.vwChartOfAccountGroup g

    on a.COAAGPID = g.AGPID

    inner join DW.vwGeneralLedger l

    on a.COAID = l.GNLCOAID

    inner join DW.vwClient c

    on l.GNLCLIID = c.CLIID

    inner join DW.vwClientType t

    on c.CLICLTID = t.CLTID

    inner join DW.vwDivision d

    on l.GNLDIVID = d.DIVID

    where COACode2Code in ('FA301G','FD100G')

    GROUP BY

    CLIName1

    ,CLICode

    ,AGPName1

    ,COAName1

    ,DIVName1

    ,COACode2Code

    ,COACode2Name1

    ,CLTName1

    ,l.GNLFSMID

    ,l.GNLBalanceBase

    ,l.GNLCOAID

    order by d.DIVName1

    Incorrect Samples:

    CLIName1CLICodeGL Account GroupGL AccountClosing BalanceDIVName1GLChart Code 2GLChart Code 2 NameClient TypeOpening Balance

    None,None,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),-32679585.0300,ARC Worldwide,FD100G,FD100G - IC Financial Debts,None,-32679585.0300

    None,None,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),-32679585.0300,ARC Worldwide,FD100G,FD100G - IC Financial Debts,None,0.0000

    NoneNoneIntercompany-AP TradeInterco Payables (to FR5507 PFS)0.0000ARC WorldwideFD100GFD100G - IC Financial DebtsNone0.0000

    NoneNoneIntercompany-AR TradeInterco Receivables (fr FR5507PFS)0.0000ARC WorldwideFA301GFA301G - IC loans, advances and depositsNone0.0000

    NoneNoneIntercompany-AR TradeInterco Receivables (fr FR5507PFS)99201731.6100ARC WorldwideFA301GFA301G - IC loans, advances and depositsNone0.0000

    NoneNoneIntercompany-AR TradeInterco Receivables (fr FR5507PFS)99201731.6100ARC WorldwideFA301GFA301G - IC loans, advances and depositsNone99201731.6100

  • Well, I'm not sure if these totals are correct but here's another stab at it...I made some significant additions to the opening balance columns (actually one for each year). It's hard for me to know given that I'm not immersed in this data like you are of course.

    ;WITH

    cteGeneralLedger

    (GNLID,GNLCLIID,GNLCOAID,GNLFSMID,GNLBalanceBase)

    AS (

    SELECT 1,1385,576,113,-4845.0000 UNION ALL

    SELECT 2,1385,713,114,-395.8500 UNION ALL

    SELECT 3,3139,39,115,8703.3400 UNION ALL

    SELECT 4,727,39,116,-1321.6500 UNION ALL

    SELECT 5,727,39,117,-8811.0000 UNION ALL

    SELECT 6,3139,713,118,-15416.5200 UNION ALL

    SELECT 7,1480,713,119,18429.9200 UNION ALL

    SELECT 8,3144,39,120,1321.6500 UNION ALL

    SELECT 9,1480,713,121,-10799.0000 UNION ALL

    SELECT 10,3144,576,122,4371.0000 UNION ALL

    SELECT 11,3139,713,123,1619.8500 UNION ALL

    SELECT 12,727,45,124,1786.1400 UNION ALL

    SELECT 13,3139,39,125,46.4200 UNION ALL

    SELECT 14,727,576,127,-12802.4000 UNION ALL

    SELECT 15,1480,713,128,1198.2600 UNION ALL

    SELECT 16,1480,713,129,-1785.0000 UNION ALL

    SELECT 17,3139,713,130,800.0000

    ),

    cteClients

    (CLIID,CLICode,CLIName)

    AS (

    SELECT 727,'HARK','HARK CONSULTING' UNION ALL

    SELECT 1385,'3HPARC','3HARCHITECTS' UNION ALL

    SELECT 1480,'GUYCAR','GUY CARPENTER' UNION ALL

    SELECT 3139,'ABNAP','ABB PTE LTD' UNION ALL

    SELECT 3144,'SYSACC','SYSTEM ACCESS'

    ),

    cteChartOfAccount

    (COAID,COANAME1,COACode2Code,COACode2NAme2,COAAGPID)

    AS (

    SELECT 39,'Total Billings','RV10','RV10 Billings',1 UNION ALL

    SELECT 45,'Prod Billing','RV40','RV40 Revenue',2 UNION ALL

    SELECT 576,'Loan','FD100G','FD100G Financial Debts',3 UNION ALL

    SELECT 713,'Receivables','FA301G','FA301G Loans Advances',4

    ),

    cteAccountGroup

    (AGPID,AGPNAME1)

    AS (

    SELECT 1,'Assets' UNION ALL

    SELECT 2,'Goodwill' UNION ALL

    SELECT 3,'Deferred' UNION ALL

    SELECT 4,'Trade' UNION ALL

    SELECT 5,'Cash'

    ),

    cteFiscalMonth

    (FSMID,FSMNAME1)

    AS (

    SELECT 113,'Opening Balance 12' UNION ALL

    SELECT 114,'Jan 12' UNION ALL

    SELECT 115,'Feb 12' UNION ALL

    SELECT 116,'Mar 12' UNION ALL

    SELECT 117,'Apr 12' UNION ALL

    SELECT 118,'May 12' UNION ALL

    SELECT 119,'Jun 12' UNION ALL

    SELECT 120,'Jul 12' UNION ALL

    SELECT 121,'Aug 12' UNION ALL

    SELECT 122,'Sep 12' UNION ALL

    SELECT 123,'Oct 12' UNION ALL

    SELECT 124,'Nov 12' UNION ALL

    SELECT 125,'Dec 12' UNION ALL

    SELECT 127,'Opening Balance 13' UNION ALL

    SELECT 128,'Jan 13' UNION ALL

    SELECT 129,'Feb 13' UNION ALL

    SELECT 130,'Mar 13'

    ),

    cteClientOwner

    (CLOID,CLOCLIID,CLODIVID)

    AS (

    SELECT 1,727,1 UNION ALL

    SELECT 2,1385,2 UNION ALL

    SELECT 3,1480,3 UNION ALL

    SELECT 4,3139,3 UNION ALL

    SELECT 5,3144,4 UNION ALL

    SELECT 6,727,5

    ),

    cteDivision

    (DIVID,DIVName1)

    AS (

    SELECT 1,'Digital' UNION ALL

    SELECT 2,'Zenith' UNION ALL

    SELECT 3,'Stars' UNION ALL

    SELECT 4,'MSL' UNION ALL

    SELECT 5,'Leo'

    )

    SELECT DISTINCT

    c.CLIName AS 'Client'

    ,c.CLICode AS 'Client Code'

    ,l.GNLID AS 'GL Account ID'

    ,g.AGPName1 as 'GL Account Group'

    ,a.COAName1 AS 'GL Account'

    ,SUM(l.GNLBalanceBase) OVER (PARTITION BY l.GNLFSMID) AS 'Closing Balance'

    ,d.DIVName1 AS 'Division'

    ,a.[COACode2Code] AS 'GLChart Code 2'

    ,a.COACode2NAme2 AS 'GLChart Code 2 Name'

    ,(CASE

    WHEN m.FSMID BETWEEN 113 AND 125

    THEN SUM(l.GNLBalanceBase) OVER (PARTITION BY

    (SELECT SUM(FSMID)

    FROM cteFiscalMonth m1

    INNER JOIN [cteGeneralLedger] l1

    ON l1.GNLFSMID = m1.FSMID

    WHERE

    l1.GNLID = l.GNLID

    AND m1.FSMID BETWEEN 113 AND 125))

    ELSE '0'

    END) AS 'Opening Balance 2012'

    ,(CASE

    WHEN m.FSMID BETWEEN 127 AND 130

    THEN SUM(l.GNLBalanceBase) OVER (PARTITION BY

    (SELECT SUM(FSMID)

    FROM cteFiscalMonth m1

    INNER JOIN [cteGeneralLedger] l1

    ON l1.GNLFSMID = m1.FSMID

    WHERE

    l1.GNLID = l.GNLID

    AND m1.FSMID BETWEEN 127 AND 130))

    ELSE '0'

    END) AS 'Opening Balance 2013'

    FROM

    cteClients c

    INNER JOIN [cteGeneralLedger] l

    ON l.GNLCLIID = c.CLIID

    INNER JOIN [cteChartOfAccount] a

    ON l.GNLCOAID = a.COAID

    INNER JOIN [cteAccountGroup] g

    ON g.AGPID = a.COAAGPID

    INNER JOIN [cteclientOwner] o

    ON o.CLOCLIID = c.CLIID

    INNER JOIN [cteDivision] d

    ON d.DIVID = o.CLODIVID

    INNER JOIN [cteFiscalMonth] m

    ON l.GNLFSMID = m.FSMID

     

  • CASE

    WHEN l.GNLFSMID IN (127)

    THEN SUM(l.GNLBalanceBase) OVER (PARTITION BY l.GNLCOAID)

    ELSE '0'

    See if this might help, try something like ...

    Sum(CASE

    WHEN l.GNLFSMID IN (127)

    THEN l.GNLBalanceBase ELSE 0

    END) as columnA

    where you move the sum to the outside of the case statement.

    ----------------------------------------------------

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply