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
Below is my Query but I cannot seem to get it working.
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)
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)
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