Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sum Negative Numbers Expand / Collapse
Author
Message
Posted Sunday, July 14, 2013 10:53 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:03 AM
Points: 81, Visits: 315
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




Post #1473440
Posted Monday, July 15, 2013 11:35 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


 
Post #1473810
Posted Tuesday, July 16, 2013 12:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:03 AM
Points: 81, Visits: 315
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 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.
Post #1474001
Posted Wednesday, July 17, 2013 7:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:03 AM
Points: 81, Visits: 315
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
Post #1474853
Posted Wednesday, July 17, 2013 8:28 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


 
Post #1474856
Posted Wednesday, July 31, 2013 10:19 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:59 PM
Points: 464, Visits: 1,074
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.
Post #1479583
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse