Quarter Parameter 1

[Halfcan]

Does anyone have a Date Parameter that prompts with pulldowns with the options of yearly quarters?

I need a parameter that has a specific Start Date,
And
End Date that I can use individually in calculations.
v8.5
Thanks,

Andy

 

[synapsevampire]

Here's some SQL to gen a table of quarters:

declare @StartDate datetime
declare @CurrentQuarter int
select @StartDate = '1/1/2002'
select @CurrentQuarter = 1
create table TheQuarters (
Startdate datetime,
Enddate datetime,
QuarterNumber int)

while @startdate < getdate()+5000
BEGIN
insert into TheQuarters (Startdate, Enddate , QuarterNumber)
values(@StartDate, dateadd(m,3,@Startdate), @currentquarter)
select @StartDate = dateadd(m,3,@Startdate)
if @currentquarter = 4
select @currentquarter = 1
else
select @currentquarter = @currentquarter +1

End

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Ooops, should have been:

declare @StartDate datetime
declare @CurrentQuarter int
select @StartDate = '1/1/2002'
select @CurrentQuarter = 1
drop table TheQuarters
create table TheQuarters (
Startdate datetime,
Enddate datetime,
QuarterNumber int)

while @startdate < getdate()+5000
BEGIN
insert into TheQuarters (Startdate, Enddate , QuarterNumber)
values(@StartDate, dateadd(m,3,@Startdate)-1, @currentquarter)
select @StartDate = dateadd(m,3,@Startdate)
if @currentquarter = 4
select @currentquarter = 1
else
select @currentquarter = @currentquarter +1

End

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Nuffsaid]

Hi,

Why don't you create two date parameters such as {?Begin_Q} and {?End_Q} and set your default values in each?

Nuffsaid

 

[synapsevampire]

These formulas generate the current quarter start and end dates if that is all you need, and you can then use the formulas in other formulas:

Start
If {?Predefined Date Range} = &quot;Current Quarter&quot; Then
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)-1)/3)+1,1,0,0,0)

End
If {?Predefined Date Range} = &quot;Current Quarter&quot; Then
If Month (CurrentDate) > 9 Then
DateTime(Year(CurrentDate),12,31,23,59,59)
Else
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)+2)/3)+1,1,23,59,59)-1

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Halfcan]

synapsevampire,

I'm not sure how or were to use your code,
when I put it in as a formula, I get &quot;The remaining text does not appear to be part of the formula&quot;
The cursor stays at the beginning.

Is declare suppost to be in blue? Is this crystal, or basic syntax, is this written in version of CR that is older than 8.5, and therefore could my problem be that CR 8.5 does not recognize the code?

Confused,

Thanks for trying,
Andy

 

[synapsevampire]

Which code are you attempting to use, the SQL or the last post?

This was written in CR 8.5:

If {?Predefined Date Range} = &quot;Current Quarter&quot; Then
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)-1)/3)+1,1,0,0,0)

It assumes a parameter exists of type String named Predefined Date Range.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Halfcan]

the code:

declare @StartDate datetime........

( just saw your last post...)

 

[Halfcan]

I made a parameter called {?Predefined Date Range}
tried it discrete or range,
I put this in select expert:

If {?Predefined Date Range} = &quot;Current Quarter&quot; Then
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)-1)/3)+1,1,0,0,0)

it errors with &quot;The result of selction formula must be a boolean.&quot;

 

[synapsevampire]

Not in the select expert, place it in a formula and reference the formula from within the select expert.

The other code was a SQL script for use with a SQL database to create a table of quarters in case you wanted all quarters referenced, your post stated you wanted a list of quarters, I posted the second option in case that was of interest.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Halfcan]

Got it. I didn't use the exact syntax, but that was enough to steer me in the right direction.

Thanks again,
Andy