[T-SQL] Sum Negative Numbers |
| Posted: 14 Jul 2013 03:53 PM PDT 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 balanceSoOpening: FSMID 113 to 125Closing: FSMID (113 to 125) + 127,128,129,130DDLTable GeneralLedgerGNLID GNLCLIID GNLCOAID GNLFSMID GNLBalanceBase1 1385 576 113 -4845.00002 1385 713 114 -395.85003 3139 39 115 8703.34004 727 39 116 -1321.65005 727 39 117 -8811.00006 3139 713 118 -15416.52007 1480 713 119 18429.92008 3144 39 120 1321.65009 1480 713 121 -10799.000010 3144 576 122 4371.000011 3139 713 123 1619.850012 727 45 124 1786.140013 3139 39 125 46.420014 727 576 127 -12802.400015 1480 713 128 1198.260016 1480 713 129 -1785.000017 3139 713 130 800.0000Table ClientsCLIID CLICode CLIName727 HARK HARK CONSULTING1385 3HPARC 3HARCHITECTS1480 GUYCAR GUY CARPENTER3139 ABNAP ABB PTE LTD3144 SYSACC SYSTEM ACCESSTABLE ChartOfAccountCOAID COANAME1 COACode2Code COACode2NAme2 COAAGPID39 Total Billings RV10 RV10 Billings 145 Prod Billing RV40 RV40 Revenue 2576 Loan FD100G FD100G Financial Debts 3713 Receivables FA301G FA301G Loans Advances 4Table AccountGroupAGPID AGPNAME11 Assets2 Goodwill3 Deferred4 Trade5 CashTable FiscalMonthFSMID FSMNAME1113 Opening Balance 12114 Jan 12115 Feb 12116 Mar 12117 Apr 12118 May 12119 Jun 12120 Jul 12121 Aug 12122 Sep 12123 Oct 12124 Nov 12125 Dec 12127 Opening Balance 13128 Jan 13129 Feb 13130 Mar 13Table ClientOwnerCLOID CLOCLIID CLODIVID1 727 12 1385 23 1480 34 3139 35 3144 46 727 5Table DivisionDIVID DIVName11 Digital2 Zenith3 Stars4 MSL5 LeoExpected ResultsClient 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 lineClosing 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[code="sql"]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 cinner join [vwChartOfAccount] aon a.[COASourceID] = c.[CLISourceID]inner join [vwChartOfAccountGroup] gon g.[AGPID] = a.COAAGPIDinner join [vwGeneralLedger] lon l.GNLCLIID = c.[CLIID]inner join [vwclientOwner] oon o.CLOID = c.CLIID inner join [vwDivision] don d.DIVID = o.CLODIVIDinner join [vwClientType] ton t.CLTID = c.[CLICLTID]inner join [DW].[vwFiscalMonth] mon l.GNLFSMID = m.FSMIDgroup by c.CLIName1, c.CLICode, g.AGPName1, a.COAName1, l.GNLFSMID, d.DIVName1, d.DIVName2, t.CLTName1, a.COACode2Code, a.COACode2Name1[/code] |
| previous week query 0700 - 0700 Posted: 14 Jul 2013 07:34 AM PDT HI all I have been asked to generate reports from my c# package I have created every Monday at 0700hrs, the previous week till Monday 0700hrs.Monday 0700 > 0000Tues 24hrsWed 24hrsThurs 24hrsFri 24hrsSat 24hrsSun 24hrsMon 0000 > 07001 weeks worth of data 0700hrs Monday to Monday can anybody help me make this possible?I am using this but it needs amending for time and I am not sure how!select * from dbo.DocketTB where Docket_EngFinish between (getdate()-7) and getdate()Docket_EngFinish is a DateTime DatatypeMany thanks for help and adviceJay |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment