July 14, 2013 at 10:53 pm
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
July 15, 2013 at 11:35 am
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
July 16, 2013 at 12:49 am
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.
July 17, 2013 at 7:23 pm
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
July 17, 2013 at 8:28 pm
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
July 31, 2013 at 10:19 am
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