on 04-29-2009 7:05 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
92 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.