March 28, 2011 at 12:53 pm
Hi All , I am confused in getting the data from Store Procedure to one of my report .
My scenario is need to populate an SSRS Charts fetching last 2 quarters data , current quarter data and next 2 quarters .
I done it for the current year as
select datepart(qq, date col)
when 1 then Q1 when 2 then Q2 when 3 then Q3 when 4 then Q4 END as 'Quarter',
col2,
col3
from Table1 T1 Join Table2 T2 on t1.colx=t2.coly
where year(date col) =year(getdate())
group by
when 1 then Q1 when 2 then Q2 when 3 then Q3 when 4 then Q4 END as 'Quarter',
col2,
col3
I Get the results for all the 4 quarters of the current year . Now i need to do it as to get the data from (current quarter -2 quarters) , Current Quarter and (Current Quarter +2 quarters) in to my charts .
Can any one please help me out on this .
Thanks & Regards
Raje
March 28, 2011 at 2:23 pm
you should be able to get what you want using the dateadd function(http://msdn.microsoft.com/en-us/library/ms186819.aspx)
this should get you started
select DATEADD(qq,-2,getdate()),DATEADD(qq,-1,getdate()),GETDATE(),DATEADD(qq,1,getdate()),DATEADD(qq,2,getdate())
March 28, 2011 at 3:29 pm
Hi Bob , Thanks for your time , I know DATEADD Provides this , But Can you help how can i incorporate this in my SQL given .
Raje
March 29, 2011 at 1:33 pm
I am unable to edit your provided sql because I have no idea about table structure or data contained within. Read the link in my signature on how to post to get the best possible help.
That being said, something like this might work for you.
select cast(year(datecol) as char(4))+'Q'+cast(DATEPART(qq,datecol) as CHAR(1)) as [Quarter],
col2,
col3
from Table1 T1
Join Table2 T2 on t1.colx=t2.coly
where datecol >= DATEADD(qq,-2,getdate())
and datecol <= DATEADD(qq,2,getdate())
group by
cast(year(datecol) as char(4))+'Q'+cast(DATEPART(qq,datecol) as CHAR(1)),
col2,
col3
If you would like a more precise example please read the link and provide ddl, sample data, and desired output.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply