Anniversarys within a given quarter

[Snookless]

Hello all,
Got a difficult one here (for me anyway), I am trying to creatre a report to show employees that have an anniversary date of 1,3 or 5 years within any specific quarter of the year, for example within the third quarter of this year (this current quarter) who has an anniversary of 1,3 or 5 years?
Using Crystal 8.5

 

[CTLReporting]

Snookless,

I don't know if the user is going to input the specific quarter or not so here's what you do if the user inputs the specific quarter...

If you don't care about parameters then I would put a string parameter in where the default values are "First", "Second", "Third", and "Fourth" then in your select statement I would put something like:

if {?pm_DateRange} = "First" then
{sample.anniversary_date} in Date (2003, 01, 01) to Date (2003, 03, 31)

else if {?pm_DateRange} = "Second" then
{sample.anniversary_date} in Date (2003, 04, 01) to Date (2003, 06, 30)

else if {?pm_DateRange} = "Third" then
{sample.anniversary_date} in Date (2003, 07, 01) to Date (2003, 09, 30)

else if {?pm_DateRange} = "Fourth" then
{sample.anniversary_date} in Date (2003, 10, 01) to Date (2003, 12, 31)

And also in your select expert select on the field describing which year of anniversary it is, and say field is one of and add 1, 3, and 5 to it.


Now if the user doesn't input a parameter then it's much easier

write a formula and put it in the details section and select on the formula , it would probably look something like this

if {sample.anniversay_date} in Calendar1stQtr then
"1st Quarter Anniversary"

else if {sample.anniversay_date} in Calendar2ndQtr then
"2nd Quarter Anniversary"

else if {sample.anniversay_date} in Calendar3rdQtr then
"3rd Quarter Anniversary"

else if {sample.anniversay_date} in Calendar4thQtr then
"4th Quarter Anniversary"

I hope this helps somewhat


CTL Reports Group,
Running Crystal Reports 8.0 and 8.5
Bentonville, AR
FoodLabReports@ctlusa.net

 

[lbass]

I assumed you didn't already have an anniversary date field. Then I would approach it by using a formula something like this:

if {?Qtr} = "1" then
(if datediff("q",{empl.hiredate},Date({?Year},01,01)) = 4 then "Qtr1 - OneYear" else
if datediff("q",{empl.hiredate},Date({?Year},01,01)) = 12 then "Qtr1 - ThreeYear" else
if datediff("q",{empl.hiredate},Date({?Year},01,01)) = 20 then "Qtr1 - FiveYear&quot else

if {?Qtr} = "2" then
(if datediff("q",{empl.hiredate},Date({?Year},04,01)) = 4 then "Qtr2 - OneYear" else
if datediff("q",{empl.hiredate},Date({?Year},04,01)) = 12 then "Qtr2 - ThreeYear" else
if datediff("q",{empl.hiredate},Date({?Year},04,01)) = 20 then "Qtr2 - FiveYear&quot else

if {?Qtr} = "3" then
(if datediff("q",{empl.hiredate},Date({?Year},07,01)) = 4 then "Qtr3 - OneYear" else
if datediff("q",{empl.hiredate},Date({?Year},07,01)) = 12 then "Qtr3 - ThreeYear" else
if datediff("q",{empl.hiredate},Date({?Year},07,01)) = 20 then "Qtr3 - FiveYear&quot else

if {?Qtr} = "4" then
(if datediff("q",{empl.hiredate},Date({?Year},10,01)) = 4 then "Qtr4 - OneYear" else
if datediff("q",{empl.hiredate},Date({?Year},10,01)) = 12 then "Qtr4 - ThreeYear" else
if datediff("q",{empl.hiredate},Date({?Year},10,01)) = 20 then "Qtr4 - FiveYear&quot else ""

-LB

 

[synapsevampire]

I responded to your other post...