Displaying data for the upcoming 4 quarters

Question

Displaying data for the upcoming 4 quarters

subramani.rudrappa 78855

SSC Eights!

Points: 848
More actions
May 10, 2011 at 3:46 am

#250034

hi,
if i am executing query in jan2011 or feb2011 or mar2011 then i need to consider records till dec31st 2011
if i am executing query in Apr2011 or may2011 or jun2011 then i need to consider records till Mar 31st 2012
if i am executing query in Jul2011 or aug2011 or sep2011 then i need to consider records till jun 31st 2012
if i am executing query in Jul2011 or aug2011 or sep2011 then i need to consider records till jun 31st 2012
if i am executing query in oct2011 or nov2011 or dec2011 then i need to consider records till sep 31st 2012
it means i need to fetch the records of existing quarter and next 3 upcoming quarters only at any cost.
how to write query to pick up the records based on the above condition specified.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you'll want to use the DATEADD and DATEDIFF functions;

here's an example using quarters: this is returning the START, of each of 4 quarters: this quarter, next, two quarters from now, etc.

so you can use the Less than function (<) in a date query and not less than or equal to(<=):

With mySampleDates(TheDate)

AS

(

SELECT CONVERT(datetime,'2011-01-17') UNION ALL

SELECT CONVERT(datetime,'2011-02-24') UNION ALL

SELECT CONVERT(datetime,'2011-03-03') UNION ALL

SELECT CONVERT(datetime,'2011-04-27')

)

select

--First Day of this Quarter

DATEADD(qq, DATEDIFF(qq,0,TheDate), 0) As ThisQtr,

DATEADD(qq,1,DATEADD(qq, DATEDIFF(qq,0,TheDate), 0)) As NextQtr,

DATEADD(qq,2,DATEADD(qq, DATEDIFF(qq,0,TheDate), 0)) As TwoQtrsFromNow,

DATEADD(qq,3,DATEADD(qq, DATEDIFF(qq,0,TheDate), 0)) As ThreeQtrsFromNow

FROM mySampleDates

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

subramani.rudrappa 78855 SSC Eights! Points: 848 More actions · Answer 2

subramani.rudrappa 78855

SSC Eights!

Points: 848

May 10, 2011 at 6:24 am

#1322235

thanks its working!

Ryan007 SSCrazy Eights Points: 8275 More actions · Answer 3

You can use the below query to get the from date and to date.

declare @firstday varchar(20)

declare @quater int

DECLARE @curr_date DATETIME

DECLARE @Fromdate DATETIME

DECLARE @Todate DATETIME

SELECT @curr_date = GETDATE()

select @quater=DATEPART(qq,getdate())

if @quater=1

Begin

set @firstday='01/01/'+convert(varchar(10),DATEPART(YYYY,getdate()))

select @Fromdate=Convert(datetime,@firstday,101)

select @Todate=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Fromdate)+12,0))

end

if @quater=2

Begin

set @firstday='04/01/'+convert(varchar(10),DATEPART(YYYY,getdate()))

select @Fromdate=Convert(datetime,@firstday,101)

select @Todate=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Fromdate)+12,0))

end

if @quater=3

begin

set @firstday='07/01/'+convert(varchar(10),DATEPART(YYYY,getdate()))

select @Fromdate=Convert(datetime,@firstday,101)

select @Todate=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Fromdate)+12,0))

end

if @quater=4

begin

set @firstday='10/01/'+convert(varchar(10),DATEPART(YYYY,getdate()))

select @Fromdate=Convert(datetime,@firstday,101)

select @Todate=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Fromdate)+12,0))

end

Ryan
//All our dreams can come true, if we have the courage to pursue them//