Range problem

[sbelt]

Hello

I've got the following problem: I want to find out if a person is in a daterange. I've got the following formula:

if {Table.Person_id} in Date(2002,01,01) to Date(2002,12,31)
then 1
else 0

This is not possible, because the person_id is not a date.
Is there a way to solve this problem?

Thanks in advance

 

[synapsevampire]

You probably have a date field for the person, use that. An ID is generally a unique identifier for relational purposes.

You've given no indication what this date equates to, birthdate, record date.

Check with your dba, they can probably help you learn which field to use.

-k

 

[sbelt]

I actually want to find out the number of persons who subscribed to a course in 2002 and in 2003. So the person must be in the range of 2002 and in 2003. There is a subscribe date which i can use, but how do I find out if the person subscibed in 2002 and 2003?

I hope I explained my problem better now!

 

[pgtek]

Hi

try this

if {subscribe date } in Date(2002,01,01) to Date(2002,12,31)
then {Table.Person_id}

cheers




pgtek

 

[sbelt]

Hello

I've got this working now, but now I want the subscribedate to be in 2 ranges.
Like this:
if {subscribe date } in Date(2002,01,01) to Date(2002,12,31)
and {subscribe date } in Date(2003,01,01) to Date(2003,12,31)
then {Table.Person_id}

This doesn't work yet. The subscribedate has to be a value in 2002 and in 2003!!Hope you can help me with this too

 

[GMcNamara]

sbelt,
Can you give us an example of a date that is in 2002 and 2003?

 

[pgtek]

hi

try this

if ({subscribe date } in Date(2002,01,01) to Date(2002,12,31)
and {subscribe date } in Date(2003,01,01) to Date(2003,12,31))
then {Table.Person_id}

this way you will get whows in 2002 and 2003 only


cheers






pgtek

 

[GMcNamara]

pgtek,
Your code will never evaluate to true. A date cannot be in 2002 AND in 2003.

sbelt,
I assume you want people who have a subscribe_date in 2002 and also have an ADDITIONAL subscribe_date in 2003. Is this record selection? If so, this will be best handled with an SQL subquery. Try updating the SQL like so:

SELECT
BLAH
BLAH
FROM
TABLES
WHERE
TABLE.SUBSCRIBE_DATE >= '1/1/2003' AND TABLE.SUBSCRIBE_DATE < = '12/31/2003' AND TABLE.PERSON_ID IN (SELECT TABLE.PERSON_ID FROM TABLE WHERE TABLE.SUBSCRIBE_DATE > >= '1/1/2002' AND TABLE.SUBSCRIBE_DATE < = '12/31/2002'

 

[lbass]

Another approach is to group on {table.employeeID} and then write a formula {@in2002and2003}:

if ({subscribe date} in Date(2002,01,01) to Date(2002,12,31) then 1 else
if {subscribe date} in Date(2003,01,01) to Date(2003,12,31))
then 10000 else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@in2002and2003},{table.employeeID}) > 10000 and
remainder(sum({@in2002and2003},{table.employeeID}),10000) <> 0

-LB

 

[sbelt]

Thanks for the help!!
I've got it working now with the advice of lbass.