x

Calc Quarterly Amounts

I have a SQL query I am using in an SSRS report. I am trying to calc quarterly amount based on the period I have selected. For example:

Period 1 Q1 Amount
Period 2 Q1 Pd1 Amount + Period 2 Amount
Period 3 Q1 Pd1 Amount + Period 2 Amount+ Period 3 Amount
And so on....

Below is my Query but I cannot seem to get it working.

 SELECT  
 #tb_GLFinSummary.GPACCOUNTNO,
 #tb_GLFinSummary.GPACCTDESC,
 #tb_GLFinSummary.FISCALYEAR,
 #tb_GLFinSummary.PERIIODID,
 #tb_GLFinSummary.QUARTER,
 #tb_GLFinSummary.QUARTERAMT,
 #tb_GLFinSummary.AMOUNT,
 
 CASE  WHEN #tb_GLFinSummary.PERIODID = @PeriodID and #tb_GLFinSummary.BUDGETID = 'ACTUAL' THEN #tb_GLFinSummary.AMOUNT  ELSE 0 END AS CurPeriodAmtAct,
 CASE  WHEN #tb_GLFinSummary.PERIODID = @PeriodID and #tb_GLFinSummary.BUDGETID = @BudgetID THEN #tb_GLFinSummary.AMOUNT  ELSE 0 END AS CurPeriodAmtBud,
 CASE  WHEN #tb_GLFinSummary.PERIODID = @PeriodID and #tb_GLFinSummary.PERIODID IN ('1','2','3') and #tb_GLFinSummary.BUDGETID ='ACTUAL' THEN #tb_GLFinSummary.AMOUNT  
 WHEN #tb_GLFinSummary.PERIODID = @PeriodID and #tb_GLFinSummary.PERIODID in ('4','5','6') and #tb_GLFinSummary.BUDGETID = 'ACTUAL' THEN #tb_GLFinSummary.AMOUNT  
 WHEN #tb_GLFinSummary.PERIODID = @PeriodID and #tb_GLFinSummary.PERIODID in ('7','8','9') and #tb_GLFinSummary.BUDGETID = 'ACTUAL' THEN #tb_GLFinSummary.QTR3AMOUNT   
 WHEN #tb_GLFinSummary.PERIODID = @PeriodID and #tb_GLFinSummary.PERIODID in ('10','11','12') and #tb_GLFinSummary.BUDGETID = 'ACTUAL' THEN #tb_GLFinSummary.QTR4AMOUNT  ELSE 0 END AS QtrPeriodAmtAct,
 CASE WHEN (#tb_GLFinSummary.PERIODID BETWEEN 1 AND @PeriodID) and #tb_GLFinSummary.BUDGETID = 'ACTUAL' THEN #tb_GLFinSummary.AMOUNT  ELSE 0 END AS YTDAmtAct,
 CASE WHEN (#tb_GLFinSummary.PERIODID BETWEEN 1 AND @PeriodID)and(#tb_GLFinSummary.BUDGETID = @BudgetID) THEN #tb_GLFinSummary.AMOUNT  ELSE 0 END AS YTDAmtBud
 
                      
 FROM  #tb_GLFinSummary 
                      
 WHERE     (#tb_GLFinSummary.PERIODID BETWEEN 1 AND @PeriodID) 
 AND (#tb_GLFinSummary.FISCALYEAR = @Year) 
 
 AND (#tb_GLFinSummary.SEGMENT4 in (select SECURITYROLEID  from Dynamics..SY10500
 where USERID = @UserID))
 Group By
 #tb_GLFinSummary.GPACCOUNTNO,
 #tb_GLFinSummary.GPACCTDESC,
 #tb_GLFinSummary.SEGMENT4,
 #tb_GLFinSummary.FISCALYEAR,
 #tb_GLFinSummary.PERIODID,
 #tb_GLFinSummary.QUARTER,
 #tb_GLFinSummary.BUDGETID,
 #tb_GLFinSummary.AMOUNT,
 #tb_GLFinSummary.QTR1AMOUNT,
 #tb_GLFinSummary.QTR2AMOUNT,
 #tb_GLFinSummary.QTR3AMOUNT,
 #tb_GLFinSummary.QTR4AMOUNT,
 #tb_GLFinSummary.ANNUALAMT


more ▼

asked Aug 18, 2011 at 01:07 PM in Default

avatar image

user-986 (google)
11 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I am guessing that this is already inside a sproc that you are just calling from SSRS? Here is something to look at to get the amounts, it could be modified further to make it easier but you should get the jist. I would also look into creating a UDF to get the quarter info quickly if it's needed much.

I am unsure of what your columns are that you are using in the temp table, but the QRTR3 and QRTR4 amounts are being called whereas the values for QRTR 1 and 2 are just being called amount :)

Some SQL to look at things differently and populating some values to work with versus the actual data.

create table #Periods (ID int)

declare @i int set @i = 1 while @i < 13 begin insert into #periods (ID) values (@I) set @i = @i + 1 end select * from #Periods

create table #Quarters (QID int , PID int)

insert into #QUARTERS values (1, 1) , (1, 2) , (1, 3) , (2, 4) , (2, 5) , (2, 6) , (3, 7) , (3, 8) , (3, 9) , (4, 10) , (4, 11) , (4, 12)

select from #Periods select from #Quarters

create table #GL (ID int , Year int , periodid int , amount decimal)

insert into #GL values (1, 2012, 8, 3.25) , (2, 2012, 8, 3.25) , (3, 2012, 7, 3.25)

declare @i int , @prd int , @amt decimal if exists (select * from #GL) select @i = max(isnull(id, 0)) + 1 from #GL else set @i = 1 --print @i while @i < 61 begin

     select @PRD = case when @i between 1 and 10 then 1
                 when @i between 11 and 20 then 2
                 when @i between 21 and 30 then 5
                 when @i between 31 and 40 then 6
                 when @i between 41 and 50 then 8
                 when @i between 51 and 60 then 9
                 else 11
             end
     select @amt = @i*1.875

         insert into #GL
             values (@i, 2012
             , @prd
             , @amt
             )
         print @i + @amt + @prd
         set @i = @i + 1
     end

select * from #GL

select sum(QRT1) as QRT1 , sum(QRT2) as QRT2 , sum(QRT3) as QRT3 , sum(QRT4) as QRT4 from ( select distinct -- PRDTotals for Quarters case when periodid between 1 and 3 then sum(amount) over (partition by periodID) end as QRT1 , case when periodid between 4 and 6 then sum(amount) over (partition by periodID) end as QRT2 , case when periodid between 7 and 9 then sum(amount) over (partition by periodID) end as QRT3 , case when periodid between 10 and 12 then sum(amount) over (partition by periodID) end as QRT4 , periodid from #GL ) QRTS

more ▼

answered Aug 22, 2011 at 10:07 AM

avatar image

BradHarker
1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1149
x698

asked: Aug 18, 2011 at 01:07 PM

Seen: 746 times

Last Updated: Aug 18, 2011 at 03:40 PM

Copyright 2017 Redgate Software. Privacy Policy