Group by Consecutuve Dates

[southbean]

Hello All,

I've tried to figure this out (it may not be possible) but waht I need to do is count consecutive dates and then group by that.



For example,


User ID Name Location #of Days Start Date End Date
1234 WAYNE GREEN NY 3 2/15/08 2/17/08
1234 WAYNE GREEN MA 1 2/20/08 2/20/08
1234 WAYNE GREEN NY 5 2/21/08 2/25/08
1234 WAYNE GREEN NY 2 2/27/08 2/28/08


An/all help or suggestions will be greatly appreciated

- TL

 

[lbass]

Are you trying to get the # of days value and then group by that? If so, create a formula:

datediff("d",startdate,enddate)+1

Then group by this formula.

-LB

 

[southbean]

Hi LB,

Thank you for your reply!

I did create the formula that you suggested but, Crystal does not make it available when I try to insert it as a Group. Might you know why this not letting me create a group on this formula?

I’m grouping the report this way:

Group #1 Name
Group #2 Location

In Group Footer #2, I’m summing the count of # of days.

Thank again!

- TL

 

[southbean]

Also,

I forgot to mention that the report’s Select Expert is using the following formula:

IF {table.checkin_date} >= {?Start_Date} and
{table.checkout_date} < {?End_Date} Then True

Thanks!

- TL

 

[lbass]

If you created the formula exactly as I showed you, it would be available for grouping on. Did you add anything to it, like "whileprintingrecords"? If so, remove it.

-LB

 

[southbean]

Hi LB,

Thanks again for replying.

Yes, I wrote it exactly as you indicated.

The only way I was able to get Crystal to allow this to use it as a group was to remove all other groups. Then it would accept it.

However, once I started to add the other groups again I got the following error message for this date formula:

“Group specified on non-recurring field"

Will the (datediff("d",startdate,enddate)+1) formula work with other groupings in the report?

Thanks again!

- TL

 

[lbass]

Please show the exact formula if you continue to have problems, by copying and pasting it into the thread. It sounds like you must have a null, and I'm going to assume it's for the enddate, so try something like this:

if isnull(enddate) then
datediff("d",startdate,currentdate)+1 else
datediff("d",startdate,enddate)+1

The other groupings should have no impact on your ability to group on this formula.

-LB

 

[southbean]

Hi LB,

Thanks for sticking with me on this one.

Here are the formulas I’ve tried so far:

Straight:
datediff("d",Minimum({table01.checkin_date}),Maximum(table01.checkin_date}))+1

Conditional:
datediff("d",
CDate(CDateTime(Minimum({table01.checkin_date},{ table01.location}))),
CDate(CDateTime(Maximum({table01.checkin_date},{ table01.location }))))+1

Neither have worked.

After thinking on it a bit more I realized that I need to use the {table01.checkin_date} for this formula. It is the only consistent (populated) date in the database and each guest gets a new ‘checkin_date’ for each day they stay. ‘checkout_date’ is inconsistent.

Thanks again. I really appreciate your help!

- TL

 

[lbass]

The problem is that you didn't mention that you were using summaries--which you can't group on. Please show sample data using the checkin date. You are now talking about a date difference based on multiple records, and it is doable using SQL expressions, but I need to see how your data looks using this date.

-LB

 

[southbean]

Hi LB,

Thanks again for your assistance!

Here is a sample of my data:

JOE SMITH MA 10/29/2003
JOE SMITH CT 10/30/2003
TOM SMITH CA 10/12/2003
TOM SMITH CA 10/13/2003
TOM SMITH AZ 10/14/2003
ANA SMITH NM 10/10/2003
ANA SMITH AZ 10/11/2003
ANA SMITH AZ 10/13/2003
ANA SMITH AZ 10/14/2003
SUE SMITH OR 10/18/2003
SUE SMITH OR 10/19/2003
SUE SMITH CA 10/20/2003
SUE SMITH OR 10/21/2003

I'm looking to get the report to display it like this:

Name Location # Days From To
JOE SMITH MA 2 10/28/2003 10/29/2003
JOE SMITH CT 1 10/30/2003 10/30/2003
TOM SMITH CA 1 10/12/2003 10/12/2003
TOM SMITH AZ 1 10/13/2003 10/13/2003
ANA SMITH NM 1 10/10/2003 10/10/2003
ANA SMITH AZ 1 10/11/2003 10/11/2003
ANA SMITH AZ 1 10/13/2003 10/13/2003
SUE SMITH OR 2 10/18/2003 10/19/2003
SUE SMITH CA 1 10/20/2003 10/20/2003
SUE SMITH OR 1 10/21/2003 10/21/2003

Thanks again!

- TL

 

[lbass]

All you need to do is group on Name, then Location, and then insert a minimum on the date field for the From field, and maximum for the To field, and place everything on the Group #2 header or footer. Use your earlier "conditional" formula for the # of days.

-LB

 

[southbean]

Hi LB,

Thanks for your reply!

I tried grouping as you suggested but that still does not get the report to group by consecutive dates. What I get is a row at a location with the correct # of days but not consecutive dates.

For example, TOM SMITH stayed in CA for 6 nights:
09/01/08
09/02/08
09/20/08
09/21/08
09/22/08
09/27/08

The report returns 1 row for TOM SMITH in CA:
6 nights from 09/01/08 - 09/27/08


In the above example I would like to have the report return 3 rows for TOM SMITH in CA:
2 nights from 09/01/08 - 09/02/08
3 nights from 09/20/08 - 09/22/08
1 night from 09/27/08 - 09/27/08


I was looking at some code you had posted for another thread. It does the consecutive count I need, but could I apply it to this issue?

WhilePrintingRecords;
NumberVar cnt;

If {table01.checkin_date} = Previous({table01.checkin_date)+ 1 Then
cnt := cnt + 1 Else
cnt := 1;

Again, many thanks for hanging in ther with me!

- TL

 

[southbean]

.

 

[southbean]

Hello All,

I am wondering if what I'm trying to do in this report is at all possible ot should I just abandon this effort.

In the example below, this is what I'm currently getting in my report.

Client	  Location	Hotel	Days of Stay 	From Date	To Date
Smith, John	CA	ACME Hotel	1	          9/23/2007	9/23/2007
Smith, John	OR	ACME Hotel	4	          9/17/2007	1/15/2008
Smith, John	WA	ACME Hotel	24	          9/2/2007	1/15/2008

In the example below, this is what I would like my report to do: group by consecutive dates.

Client	Location	Hotel	Days of Stay 	From Date	To Date
Smith, John	CA	ACME Hotel	1	        9/23/2007	9/23/2007
Smith, John	OR	ACME Hotel	2	        9/17/2007	9/19/2008
Smith, John	OR	ACME Hotel	1	        10/1/2007	10/1/2007
Smith, John	OR	ACME Hotel	1	        1/15/2008	1/15/2008
Smith, John	WA	ACME Hotel	18	        9/2/2007	9/19/2007
Smith, John	WA	ACME Hotel	2	        11/5/2007	11/6/2007
Smith, John	WA	ACME Hotel	4	        1/12/2008	1/15/2008

Any/all help or advice with this would be Greatly appreciated!

Thanks,

- TL

 

[lbass]

I didn't mean to abandon you, but I couldn't think of a way to do this. To assess the sequence requires evaluating across records, which means you can't group by such a formula. You might be able to adapt something from this
thread767-1465150.

-LB

 

[southbean]

Hi LB,

Yes, I was begining to realize that this was not going to be possible.

Thank you very much for all your help!

- Tom