Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sum Negative Numbers


Sum Negative Numbers

Author
Message
ringovski
ringovski
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 429
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
GNLID   GNLCLIID   GNLCOAID   GNLFSMID   GNLBalanceBase
1   1385   576    113    -4845.0000
2   1385   713    114    -395.8500
3   3139   39    115    8703.3400
4   727   39    116    -1321.6500
5   727   39    117    -8811.0000
6   3139   713    118    -15416.5200
7   1480   713    119    18429.9200
8   3144   39    120    1321.6500
9   1480   713    121    -10799.0000
10   3144   576    122    4371.0000
11   3139   713    123    1619.8500
12   727   45    124    1786.1400
13   3139   39    125    46.4200
14   727   576    127    -12802.4000
15   1480   713    128    1198.2600
16   1480   713    129    -1785.0000
17   3139   713    130    800.0000

Table Clients
CLIID   CLICode   CLIName
727   HARK   HARK CONSULTING
1385   3HPARC   3HARCHITECTS
1480   GUYCAR   GUY CARPENTER
3139   ABNAP   ABB PTE LTD
3144   SYSACC   SYSTEM ACCESS

TABLE ChartOfAccount
COAID   COANAME1   COACode2Code   COACode2NAme2   COAAGPID
39   Total Billings   RV10    RV10 Billings   1
45   Prod Billing   RV40    RV40 Revenue   2
576   Loan    FD100G    FD100G Financial Debts   3
713   Receivables   FA301G    FA301G Loans Advances   4

Table AccountGroup
AGPID   AGPNAME1
1   Assets
2   Goodwill
3   Deferred
4   Trade
5   Cash

Table FiscalMonth
FSMID   FSMNAME1
113   Opening Balance 12
114   Jan 12
115   Feb 12
116   Mar 12
117   Apr 12
118   May 12
119   Jun 12
120   Jul 12
121   Aug 12
122   Sep 12
123   Oct 12
124   Nov 12
125   Dec 12
127   Opening Balance 13
128   Jan 13
129   Feb 13
130   Mar 13

Table ClientOwner
CLOID   CLOCLIID   CLODIVID
1   727   1
2   1385   2
3   1480   3
4   3139   3
5   3144   4
6   727   5

Table Division
DIVID   DIVName1
1   Digital
2   Zenith
3   Stars
4   MSL
5   Leo


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





Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)

Group: General Forum Members
Points: 477 Visits: 1721
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




 
ringovski
ringovski
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 429
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]
   Wink

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 Balance   Division,   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

Client   Client Code   GL Account Group   GL Account   Closing Balance   Division   GLChart Code 2   GLChart Code 2 Name   Client Type   Opening Balance
HOUSE   HOUSE   Intercompany-AP Trade   Interco Payables (to FR5507 PFS)   -357586723.70   Alpha 245   FD100G   FD100G - IC Financial Debts   House   -175767596.3
LION RE:SOURCES SINGAPORE   SG5865SG   Intercompany-AP Trade   Interco Payables (to FR5507 PFS)   -357586723.70   Alpha 245   FD100G   FD100G - IC Financial Debts   Intercompany   -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.
ringovski
ringovski
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 429
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:
Client   Client Code   GL Account Group   GL Account   Closing Balance    Division   GLChart Code 2   GLChart Code 2 Name   Client 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:

CLIName1   CLICode   GL Account Group   GL Account   Closing Balance   DIVName1   GLChart Code 2   GLChart Code 2 Name   Client Type   Opening 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
None   None   Intercompany-AP Trade   Interco Payables (to FR5507 PFS)   0.0000   ARC Worldwide   FD100G   FD100G - IC Financial Debts   None   0.0000
None   None   Intercompany-AR Trade   Interco Receivables (fr FR5507PFS)   0.0000   ARC Worldwide   FA301G   FA301G - IC loans, advances and deposits   None   0.0000
None   None   Intercompany-AR Trade   Interco Receivables (fr FR5507PFS)   99201731.6100   ARC Worldwide   FA301G   FA301G - IC loans, advances and deposits   None   0.0000
None   None   Intercompany-AR Trade   Interco Receivables (fr FR5507PFS)   99201731.6100   ARC Worldwide   FA301G   FA301G - IC loans, advances and deposits   None   99201731.6100
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)

Group: General Forum Members
Points: 477 Visits: 1721
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




 
MMartin1
MMartin1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1077 Visits: 2009
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.

----------------------------------------------------
How to post forum questions to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search