Joined: 13 Nov 2009
Location: United States
Online Status: Offline
Posts: 9
Topic: Previous Quarter in Record Selection Posted: 08 Feb 2010 at 10:56am
Hello,
I am trying to create a report that I'm going to schedule to run at the beginning of each quarter that will display data for the previous quarter. Any idea how I can do this? Thanks!
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 08 Feb 2010 at 12:16pm
One possible way:
If datepart('q',currentdate)=1 then datepart('q',table.date)=4 and year(table.date)=year(currentdate)-1 else datepart('q',table.date)=datepart('q',currentdate)-1 and year(table.date)=year(currentdate)
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 08 Feb 2010 at 12:47pm
Yep.
try this in your select statement:
If datepart('q',currentdate)=1 then datepart('q',{MCTCase.Case_Open_Date})=4 and year({MCTCase.Case_Open_Date})=year(currentdate)-1 else datepart('q',{MCTCase.Case_Open_Date})=datepart('q',currentdate)-1 and year({MCTCase.Case_Open_Date})=year(currentdate)
Joined: 13 Nov 2009
Location: United States
Online Status: Offline
Posts: 9
Posted: 08 Feb 2010 at 1:54pm
That didn't work because it did not pull back any records. What I ended up doing is the following:
( If Month(CurrentDate)in [1,2,3] then (Month({MCTCase.Case_Open_Date})= [10,11,12] and year({MCTCase.Case_Open_Date})=year(currentdate)-1) else if Month(CurrentDate)in [4,5,6] then (Month({MCTCase.Case_Open_Date})= [1,2,3] and year({MCTCase.Case_Open_Date})=year(currentdate)) else if Month(CurrentDate)in [7,8,9] then (Month({MCTCase.Case_Open_Date})= [4,5,6] and year({MCTCase.Case_Open_Date})=year(currentdate)) else if Month(CurrentDate)in [10,11,12] then (Month({MCTCase.Case_Open_Date})= [7,8,9] and year({MCTCase.Case_Open_Date})=year(currentdate)) )
Thank you for the help though because I wouldn't have gotten the year condition without the help.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
