Date Evaluation in same Data Array twice

[FAYUS]

I'm using CRXI with Oracle 10g and need to evaluate if two subsequent dates can be found twice within the same date range array. For example, guest checks into a room on 1/1/09 and departs 3/1/09 but also checks into a room 2/1/09 and departs 4/1/09. I need to identify the two checkin dates that occured between 1/1/09 and 4/1/09.

Any help will be much appreciated...

 

[CoSpringsGuy]

cant you group by guest and summarize a count? if your date array is 1/1/09 to 4/1/09 the guest should have two records returned based on your scenario

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[FAYUS]

After that, I need to only retireve the guests that are in two rooms at the same time. There's two date parameters at the front of the report to capture check ins within that range. Using a group and count will identify the check ins more than once but what formula can I use to see if the second check in date happened before the depart date of the first check in?

 

[lbass]

Assuming you sort in ascending order by checkin and checkout dates, you can check like this:

{table.checkindate} < previous({table.checkoutdate}) or
{table.checkoutdate} > next({checkindate})

-LB

 

[FAYUS]

Can the 'next' and 'previous' function be used within a specified group? I need to evaluate your formula for each account record. Using the 'next' and 'previous' function in previous attempts would evaluate against other persons account records...

 

[lbass]

(
(
not onlastrecord and
{table.groupfield} = next({table.groupfield}) and
{table.checkoutdate} > next({checkindate})
)
or
(
not onfirstrecord and
{table.groupfield} = previous({table.groupfield}) and
{table.checkindate} < previous({table.checkoutdate})
)
)

-LB

 

[FAYUS]

This works great! Thanks! Now, is there a way to add the results of this formula (T or F) to the Select Expert?

 

[lbass]

No, because it is testing for sequential records. You could use the opposite of this for suppression though.

-LB