using max dates and variables or not

[jedder18]

Using Crystal 2008 - NEWBIE

Have cascading table pulling data from
field 1 in table is clientname
field 2 client type
field 3 start date
field 4 end date
field 5 yes/no

{@maxdays}
\\ which is (DateDiff ("d",{fc_invoice.fc_start_date} , maximum ({fc_invoice.fc_end_date}))) +1\

need the dates and person name to show up for the 1 type of client (fld 2) only

what we're looking for is the total number of days a person was with clientname as long as fld 5 is true.
if not true, we have to only count the days said person was with clientname...the date will change on field 2 type

once the type changes, we need the number of days a person is with clientname for that type...again the date changes on field 2 type

we are looking for running totals for the clientname that has the same type.


aside from doing a maximum on the end date, other options??? since we can't do running totals on maximum


I've used variables for the maximum and reset while printing, tried the display in group headers, details, just about everywhere....
keeps running the maxdays even if the client type has changed...

hopefully this is clearer than mud...

Help is so much appreciated....

Jedder

 

[CoSpringsGuy]

show your formula regarding the variable comment

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[jedder18]

whileprintingrecords;
Global NumberVar maxdays;
maxdays := maxdays + {@max_days}


WhilePrintingRecords;
global NumberVar maxdays;


whileprintingrecords;
global NumberVar maxdays := 0;




Jedder

 

[CoSpringsGuy]

my vision of your report LOL (Im sure I have it wrong though)
group by client type
create formula
if field5 is true then (DateDiff ("d",{fc_invoice.fc_start_date} , maximum ({fc_invoice.fc_end_date}))) +1 else 0

sum that field to the group footer

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[jedder18]

I can't sum a max field.
That's why all the trouble.
Why I need variables????

Jedder

 

[CoSpringsGuy]

Geez .. Im off my game today .. I knew that ... get back with you in a bit

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[jedder18]

here's the scenario

Sally goes with type 1 client for 10 days
sally leaves type 1 and goes to type 2 client for 2 days
sally comes back to type 1 client for 10 days

If type 2 client has a yes in field 5 we need to add those 2 days to the other 20 days for sallys' total days
if field 5 no, we need to only show the 20 days for sally

Hope this helps.

Jedder

 

[CoSpringsGuy]

Group 1 by client name (Sally)
Group 2 by client type

add this formula to details
if field5 is true then DateDiff ("d",{fc_invoice.fc_start_date} ,{fc_invoice.fc_end_date}+1) else 0 //i hadnt noticed you had a max in there.. you dont need it

sum that formula to group footer 2 for total time with type
sum to group footer 1 for sallys total

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[jedder18]

But if I don't put max it will only sum the 1st 10 days of sally's stay

the table looks like this

Type 1 client-NAME
row 1 fld 1 Sally fld 2 start date fld 3 end date fld 4 yes
row 2 fld 1 Sally fld 2 start date fld 3 end date fld 4 yes
row 3 fld 1 Sally fld 2 start date fld 3 end date fld 4 yes

row 1 Sally 1/1/13 1/5/13
row 2 sally 1/6/13 1/7/13
row 3 sally 1/8/13 1/31/13

need to count all the days, that's why I used max so in scenario 1 count should be 31

scenario 2 = if row 2 fld 4 is no count should be 29

J

 

[CoSpringsGuy]

what exactly are these two dates then? {fc_invoice.fc_start_date} {fc_invoice.fc_end_date}

I assumed that was the start of the stay and the end of that stay for that record.

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

row 1 Sally 1/1/13 1/5/13 DateDiff ("d",{fc_invoice.fc_start_date} ,{fc_invoice.fc_end_date}+1) would be 5 days
row 2 sally 1/6/13 1/7/13 DateDiff ("d",{fc_invoice.fc_start_date} ,{fc_invoice.fc_end_date}+1) would be 2 days
row 3 sally 1/8/13 1/31/13 DateDiff ("d",{fc_invoice.fc_start_date} ,{fc_invoice.fc_end_date}+1) would be 24 days

summed for that type would be 31 days

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[jedder18]

Sally can leave and go with type 2 client
sometimes the type 2 clients will be included in the invoice and if field 4 is no those dates will not be included
it is a cascading table....

J

 

[CoSpringsGuy]

scenario 2 row 2 is a no
row 1 Sally 1/1/13 1/5/13 if field5 = "yes" then DateDiff ("d",{fc_invoice.fc_start_date} ,{fc_invoice.fc_end_date}+1) else 0 would be 5 days
row 2 sally 1/6/13 1/7/13 if field5 = "yes" then DateDiff ("d",{fc_invoice.fc_start_date} ,{fc_invoice.fc_end_date}+1) else 0 be 0 days
row 3 sally 1/8/13 1/31/13 if field5 = "yes" then DateDiff ("d",{fc_invoice.fc_start_date} ,{fc_invoice.fc_end_date}+1) else 0 would be 24 days

summed for that type would be 29 days

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[jedder18]

this is why I used max for end date....

Not always going to have the same number of rows per name

J