Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Urgent!! Accumulated Figure

Author  Topic  az_jnara Starting Member 1 Post Posted - 2008-03-26 : 01:03:31 Below is my query to get total up of current month and year to date.. but it's not a real output that I want.. -- Temp Table 1 : Assign Indicator & Timekey, and split Debit & Credit amount DECLARE @TempAE TABLE ( GLAcc varchar(10), CompCode varchar(4), DtPost datetime, DtDocInDoc datetime, FiscalPeriod int, FiscalYear int, ItemTxt varchar(50), AccDocNum varchar(10), Debit decimal(12,2), Credit decimal(12,2), TimeKey numeric(18,0), Indicator varchar(50) ) INSERT INTO @TempAE (GLAcc,CompCode,DtPost,DtDocInDoc,FiscalPeriod,FiscalYear,ItemTxt,AccDocNum,Debit,Credit,Timekey,Indicator) select GLAcc,CompCode,DtPost,DtDocInDoc,FiscalPeriod,FiscalYear,ItemTxt,AccDocNum, CASE IndDebitCredit WHEN 'H' THEN 0 ELSE LocCurrAmt END "Debit", CASE IndDebitCredit WHEN 'S' THEN 0 ELSE LocCurrAmt END "Credit", CASE WHEN FiscalPeriod in(1,2,3,4,5,6,7,8) THEN cast(FiscalYear as varchar) + '0' + cast(FiscalPeriod+1 as varchar) WHEN FiscalPeriod = 12 THEN cast(FiscalYear+1 as varchar) + '01' WHEN FiscalPeriod in (9,10,11) THEN cast(FiscalYear as varchar) + cast(FiscalPeriod+1 as varchar) END "TimeKey", CASE WHEN GLAcc = 8808000 THEN 'Recruitment Fees' WHEN GLAcc in (8818014,8818015,8818999,8899990) THEN 'General Consumption' WHEN GLAcc = 8830000 THEN 'Bad & Doubtful Debts Provision' END "Indicator" from dbo.GL_AccByGLAcc where GLAcc in (8808000,8818014,8818015,8818999,8899990,8830000) and CompCode = 0202 order by GLAcc,FiscalYear desc,FiscalPeriod desc DECLARE @TempAE2 TABLE ( FiscalYear int, TimeKey numeric(18,0), Indicator varchar(50), Net decimal(12,2) ) INSERT INTO @TempAE2 (FiscalYear,Timekey,Indicator,Net) select FiscalYear , TimeKey, Indicator, sum(Debit)-sum(Credit) As Net from @TempAE group by Indicator, FiscalYear, TimeKey order by Indicator, FiscalYear, TimeKey -- Final Table : Indicator, TimeKey, Net and acumulated value for each item SELECT a.Indicator, a.TimeKey, a.Net,COALESCE(SUM(b.Net),0) + a.Net AS TotalYTD FROM @TempAE2 AS a LEFT OUTER JOIN @TempAE2 AS b ON (a.Indicator = b.Indicator) AND (a.FiscalYear = b.FiscalYear) AND (b.TimeKey < a.TimeKey) GROUP BY a.Indicator, a.TimeKey, a.Net having ( CAST(a.TimeKey as varchar) >= 200702 ) ORDER BY a.Indicator, a.TimeKey, a.Net Sample of output : Indicator|TimeKey|Net|TotalYTD Recruitment Fees|200702|487.04|487.04 Recruitment Fees|200705|1,848.19|2,335.23 Recruitment Fees|200708|149.32|2,484.55 Recruitment Fees|200709|165.92|2,650.47 This is output that I want : Indicator| TimeKey|Net|TotalYTD Recruitment Fees|200702|487.04|487.04 Recruitment Fees|200703| 0.00|487.04 Recruitment Fees|200704|0.00|478.04 Recruitment Fees|200705|1,848.19|2,335.23 Recruitment Fees|200706|0.00|2,335.23 Recruitment Fees|200707|0.00|2,335.23 Recruitment Fees|200708|149.32|2,484.55 Recruitment Fees|200709|165.92|2,650.47 Please assist me on how to assign default value to the net which is 0.00, if on that patricular month/timekey eg: 200703 there are no data that meet the specified conditions. I need to display all the data(TimeKey, Indicator, Net and TotalYTD) which is I've higlighted as blue since I have to display accumulated amount(Total Year to Date) for each TimeKey in my report.