question

Hi I am trying to add a column that has YTD total calculation based on another column, below is my stored procedure that gets me my data... USE [test] GO /****** Object: StoredProcedure [dbo].[test] Script Date: 10/25/2012 09:56:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[test] @Ent varchar(25), @BeginTranDate varchar(32), @EndTranDate varchar(32), @EntryDate smalldatetime AS SET NOCOUNT ON; -- exec test 690, '02/01/2012', '03/29/2012', '10/09/2012' set @EntryDate = @EntryDate + ' 23:59:59' declare @OrgFilter VARCHAR(MAX) = '(' + cast(@Ent as varchar(25)) + '=Ent4)' ---------------- IF (SELECT OBJECT_ID('TempDB.dbo.#tmpRevTransRpt')) IS NOT NULL BEGIN DROP TABLE #tmpRevTransRpt END CREATE TABLE [dbo].[#tmpRevTransRpt] ( [InternalID] [varchar] (72) NULL , [DisplayName] [varchar] (72) NULL , [ResidentSys] [int] NULL , [PlanSys] [int] NULL , [ServiceSys] [int] NULL , [TranSys] [int] NOT NULL , [TranComponent] [int] NOT NULL, [TranDate] [datetime] NULL , [EpisodeSys] [int] NULL , [BedSys] [int] NULL , [OrgBreak] [varchar] (20) NULL , [name1] [varchar] (20) NULL , [name2] [varchar] (20) NULL , [name3] [varchar] (20) NULL , [name4] [varchar] (20) NULL , [name5] [varchar] (20) NULL , [name6] [varchar] (20) NULL , [name7] [varchar] (20) NULL , [name8] [varchar] (20) NULL , [name9] [varchar] (20) NULL , [name10] [varchar] (20) NULL , [name11] [varchar] (20) NULL , [name12] [varchar] (20) NULL , [name13] [varchar] (20) NULL , [name14] [varchar] (20) NULL , [name15] [varchar] (20) NULL , [CertSys] [int] NULL , [RevAmt] [money] DEFAULT 0 , [ContractAdjAmt] [money] DEFAULT 0 , [ARAmt] [money] DEFAULT 0 , [CoinsARAmt] [money] DEFAULT 0 , [OtherAmt] [money] DEFAULT 0 , [Units] [money] DEFAULT 0 , [PayerTypeDesc] [varchar] (72) NULL , [ServiceID] [Varchar] (32) DEFAULT ' ' , [ServiceDesc] [varchar] (72) DEFAULT ' ' , [PayerType] [varchar] (8) NULL , [PlanID] [varchar] (32) NULL , [PlanDesc] [varchar] (72) NULL , [ServiceTypeDesc] [varchar] (72) DEFAULT ' ' , [AdmissionID] [varchar] (32) NULL , [CertBegin] [datetime] NULL , [CertEnd] [datetime] NULL ) ON [PRIMARY] EXEC hmxafo_TestCR..prcInsertRevTransReportData 'dbo.#tmpRevTransRpt',@OrgFilter,'1','','','''RO''', @BeginTranDate,@EndTranDate,'','''CR'',''CP''','',@EntryDate,'4','',1 CREATE NONCLUSTERED INDEX [Idx_TranSysTranComponentPlanSys] ON [dbo].[#tmpRevTransRpt]([TranSys],[TranComponent],[PlanSys]) EXEC hmxafo_TestCR..prcUpdRevTransReportServiceData '#tmpRevTransRpt', '' EXEC hmxafo_TestCR..prcUpdRevTransReportRevContraUnitAmountData '#tmpRevTransRpt',@BeginTranDate,@EndTranDate,'',@EntryDate,'',1 EXEC hmxafo_TestCR..prcUpdRevTransReportARCoinsOthAmountData '#tmpRevTransRpt',@BeginTranDate,@EndTranDate,'',@EntryDate,'',1 EXEC hmxafo_TestCR..prcUpdRevTransReportResidentData '#tmpRevTransRpt' EXEC hmxafo_TestCR..prcUpdRevTransReportPayerData '#tmpRevTransRpt' EXEC hmxafo_TestCR..prcUpdRevTransReportAdmissionData '#tmpRevTransRpt' declare @tmp Table( Month varchar(32), DescriptionLine varchar(15), Actual decimal(10,2) ) insert into @tmp select distinct month(Trandate) as Month, 'Average RUG' as DescriptionLine, (SUM(RevAmt) + SUM(ContractAdjAmt)) / (SUM(units))*-1 as Actual from #tmpRevTransRpt where RevAmt<>0 and ContractAdjAmt<>0 and Units<>0 and ServiceID in( 'AAA','CA1','CC1','CD1','ES1','LC1','LD1', 'LE1','PD1','RHA','RHB','RHX','RMB','RMC','RML','RUA','RUB', 'RUC','RUL','RUX','RVA','RVB','RVC','RVX') group by month(trandate) order by montH(TranDate) select * from @tmp and I get the following results..... Month DescriptionLine Actual 2 Average RUG 526.61 3 Average RUG 521.80 Now I am trying to add a column right next to the Actual Column that will count the YTD total based on home many months users run the report for. Thanks in Advance.