Break 1 record in 2

[Former Member]

hi,

I'm using Crystal Reports 2008 with SQL 2005.

I have a table with some records containing startdate and enddate. What I need is the following:

I need to calculate how many days in a month an event occured.

like

"disk failure" from 01/02/09 to 10/02/09

disk failure - 10 days in February

but if I have:

"disk failure" from 21/04/09 to 05/05/09

I need:

disk failure - 10 days in april

disk failure - 5 days in may

Can Anyone help me?

Thanks

Daniel de Sousa

[Former Member · Accepted solution]

Daniel,

I have tried something similar in Crystal v10 and this is what worked for me:

1. group your data by date (perhaps start date will work)

2. change your group options to print the section for 'every month'

3. to determine a time period, I inserted two summaries; one to determine a minumum and another to determine a maximum, then find the difference between the two to get a period within the month. You have a start and end date so this step may be different from what you need.

Not sure how this would look in a different version of Crystal, but a similar method might exist in 2008.

Layne

[Former Member]

This is assuming that the dates are held as two seperate columns/fields...

Replace {Command.startdate} and {Command.enddate} with the fields from your datasource for it to work!

Hope it helps - let us know...

//a string variable to hold the output
stringvar output:="";

//a number array to hold the count for each month - one array value per month (12)
numbervar array monthcount:=[0,0,0,0,0,0,0,0,0,0,0,0];

//loop through all the dates between the startdate and enddate
//and add 1 to the number array, using the month number to
//determine which array value to increase
datevar dateloop:=date({Command.startdate});

while dateloop<=date({Command.enddate}) do (
    monthcount[month(dateloop)]:=monthcount[month(dateloop)]+1;
    dateloop:=date(dateadd("d",1,dateloop)));

//loop through the array, and if the value for the month is >0 
//add the value of that array position, plus the month name to an 
//output string
numbervar outputloop:=1;

for outputloop:=1 to 12 do (
    if monthcount[outputloop] > 0
    then output:=output+totext(monthcount[outputloop],0,"")+" day"
    +(if monthcount[outputloop]>1 then "s" else "")
    +" in "+monthname(outputloop)+chr(10););
    
//tidy up the output string if there is a floating carrage return 
//at the end of it
if right(output,1)=chr(10) then left(output, len(output)-1) else output;

Edited by: Jaime Hargreaves on Apr 29, 2009 3:09 PM

As the SAP forum thingy removed the <> that I had put in... grrr.