Crystal Date Array Calculations

I currenty an trying to get a datediff of each set of dates in an array

My array is populating with this in a fuction called getarray

Local DateTimeVar Array OpenAy := MakeArray (
if {pr_activity_type.name}="Open" or
{pr_activity_type.name}="More Info Required" or
{pr_activity_type.name}="Initiate NCR" or
{pr_activity_type.name}="Confirm" or
{pr_activity_type.name}="Complete Investigation" or
{pr_activity_type.name}="Close" or
{pr_activity_type.name}="Re-Open" then {pr_activity.date_posted}
);
Local NumberVar o := 1;
Local NumberVar countero := 1;

While o <= ubound(OpenAy) Do
(

//result:= OpenAy[i];
if countero <>o then
(
countero:= o;
);
o := o + 1

);

OpenAy[countero];

I tried to do datediff ("d", OpenAy[countero],OpenAy[countero+1])
however it errors out becuase the function has not populated all of the array yet

Is there away to call OpenAy in anouther formula so it already is populated with all the dates needed.

If you need more explanation let me know.

ThanksI just relized that with the code posted I am not populating more then one row of the Array

Example output

Rec State Date Performed FunctionOutput
1 Open 2002/02/02 12:12:12 2002/02/02 12:12:12
1 Initiate 2002/03/02 12:12:12 2002/03/02 12:12:12
1 Confirm 2002/06/02 12:12:12 2002/06/02 12:12:12
1 Complete 2002/12/02 12:12:12 2002/12/02 12:12:12

these records are one record per details line.

when I get and output my array it outputs the correct dates however It only stores one date at a time. If I out put it in the Page footer I get the last date. If I output the ubound I get 1.

Im not sure how to get this to work correctly. What I want as an output is this.

Rec State Date Performed FunctionOutput
1 Open 2002/02/02 12:12:12
1 Initiate 2002/03/02 12:12:12 1
1 Confirm 2002/06/02 12:12:12 3
1 Complete 2002/12/02 12:12:12 6

days in open 1 days in Initiate 3 days in confirm 6

however I need to do an array becuase It could go back to Open more then once.|||From what I've seen / understood here, I'd probably

1) Group on the Record id (assuming it's unique)
2) Sort on the date performed / date posted (same thing?)
3) Use the Previous function for the date comparison, e.g. a formula like:
whileprintingrecords;
if {table.record_id} = previous({table.record_id}) // to check if in the same record id group
then totext(datediff("d", previous({table.record_id}), {table.record_id}), 0)
else ""|||Thanks Jagan

I spaced the prevous function. With your Idea I was able to get the output I wanted.

Thanks|||With this code is there a way to sum.
I keeps telling me that "this field can not be summarized
However I changed my ouput to a number, Am I missing something.

numberVar DiffDate;

whileprintingrecords;
if {table.record_id} = previous({table.record_id})
then
DiffDate:=datediff("d", previous({table.record_id}), {table.record_id})
else 0

Second Function

SUM({abovefunction});|||Also why can some formulas not be summarized. using the E autosum function in crystal
the function below does not even show up in the list when I try and sum it.

Im guessing its the prevous function. If so is there any other way to do this. OK now I know its the next and Previous function. Is there any other way to sum when I need to use that function.

What I need to do is get the avg days it takes for some one to perform confirm. I am now outputing a datediff for the state of confimed and I have a function that retruns a 1 if the state is Confirm however If I sum if its at confirmed I get every record. I can put and if to see if the record number <> next(record number) because then I could not sum it.

Im not sure what I need to do next. Any help is appreciated.


numberVar output;
numberVar total:=0;
global numberVar som:=0;
global numberVar avg:=0;


If {PR_ACTIVITY_TYPE.NAME}="Confirm" then
(
if {pr.id} = previous({pr.id})
then
output:=tonumber (datediff("d", previous({PR_ACTIVITY.DATE_POSTED}), {PR_ACTIVITY.DATE_POSTED}))
else
output:=0;

if {pr.id} = previous({pr.id}) then
(
if output >=1 then
(
total:=1;

)
else
total:=0;
);

);


total;|||Could anyone look at my last question. This is still causing me issues.



Thanks

Cross tab based on DateDIFF

I'm trying trying to build a crosstab query using a Case statement to count
the number of records in one column with the DateDiff >=0 and the other
column to be a count of the Records with the DateDiff <0. I can do this
easlily in MS Access with the IIF Function in a crosstab query but I can't
get the Sql Statement to work in an MS Sql 2000 View
Geoff,
Is this what you are after?
SELECT
SUM (CASE
WHEN Col1 >= 0 THEN 1
ELSE 0
END) AS NonNegative,
SUM (CASE
WHEN Col1 >= 0 THEN 0
ELSE 1
END) AS Negative
FROM YourTable
--RLF
"Geoff" <Geoff@.discussions.microsoft.com> wrote in message
news:253BF539-8139-4E63-B824-73440A6302E9@.microsoft.com...
> I'm trying trying to build a crosstab query using a Case statement to
> count
> the number of records in one column with the DateDiff >=0 and the other
> column to be a count of the Records with the DateDiff <0. I can do this
> easlily in MS Access with the IIF Function in a crosstab query but I can't
> get the Sql Statement to work in an MS Sql 2000 View
|||How do I put in the condition of wether the DateDiff is >= 0. I was trying
someting similiar to this view below but replacing the Count(CASE ACTCAT WHEN
N'X4' Then ACTCAT END with something to make one column count the number of
records where DATEDIFF(DD, dbo.JobLogTbl.JSTime, dbo.ContActivTbl.AddDate)
AS DATEDIFF >=0 and another column show the count if DATEDIFF(DD,
dbo.JobLogTbl.JSTime, dbo.ContActivTbl.AddDate) AS DATEDIFF < 0 in a view
SELECT TOP 100 PERCENT DATENAME(MM, dbo.JobLogTbl.JSTime) AS Month,
MONTH(dbo.JobLogTbl.JSTime) AS MoNum,
COUNT(CASE ACTCAT WHEN N'X4' THEN ACTCAT END) AS X4,
COUNT(CASE ACTCAT WHEN N'AG' THEN ACTCAT END) AS AG
FROM dbo.ContActivTbl INNER JOIN
dbo.JobLogTbl ON dbo.ContActivTbl.JobNo =
dbo.JobLogTbl.JobNo
WHERE (dbo.ContActivTbl.ActCat = 'X4') OR
(dbo.ContActivTbl.ActCat = 'AG') AND
(dbo.JobLogTbl.JSTime >= 01 / 01 / 07)
GROUP BY DATENAME(MM, dbo.JobLogTbl.JSTime), MONTH(dbo.JobLogTbl.JSTime)
ORDER BY MONTH(dbo.JobLogTbl.JSTime)
I can do this in MS Access with
TRANSFORM Count(ContActivTbl.JobNo) AS CountOfJobNo
SELECT Format([JSTime],"mmmm") AS [Month], Count(ContActivTbl.JobNo) AS
[Total Shipments], JobLogTbl.DLocNo
FROM ContActivTbl RIGHT JOIN JobLogTbl ON ContActivTbl.JobNo = JobLogTbl.JobNo
WHERE (((ContActivTbl.ActCat)="X4") AND ((JobLogTbl.JobStart)>#1/1/2007#)
AND ((JobLogTbl.JSTime) Is Not Null) AND ((JobLogTbl.SoType)="1") AND
((JobLogTbl.ShipStatus)<>"Cancelled"))
GROUP BY Month([JSTime]), Format([JSTime],"mmmm"), ContActivTbl.CoNo,
JobLogTbl.DLocNo, JobLogTbl.SoType, JobLogTbl.ShipStatus
ORDER BY Month([JSTime])
PIVOT IIf([JobLogTbl].JSTime>=[ContActivTbl].AddDate,"Before Arrival","After
Arrival");
"Russell Fields" wrote:

> Geoff,
> Is this what you are after?
> SELECT
> SUM (CASE
> WHEN Col1 >= 0 THEN 1
> ELSE 0
> END) AS NonNegative,
> SUM (CASE
> WHEN Col1 >= 0 THEN 0
> ELSE 1
> END) AS Negative
> FROM YourTable
> --RLF
> "Geoff" <Geoff@.discussions.microsoft.com> wrote in message
> news:253BF539-8139-4E63-B824-73440A6302E9@.microsoft.com...
>
>
|||Geoff,
If I am tracking you correctly then you would want something like:
SELECT
DATENAME(MM, dbo.JobLogTbl.JSTime) AS Month,
MONTH(dbo.JobLogTbl.JSTime) AS MoNum,
SUM (CASE
WHEN DATEDIFF(DAY,dbo.JobLogTbl.JSTime, GETDATE())>= 0 THEN 1
ELSE 0
END) AS NonNegative,
SUM (CASE
WHEN DATEDIFF(DAY,dbo.JobLogTbl.JSTime, GETDATE()) >= 0 THEN 0
ELSE 1
END) AS Negative
FROM YourTable
GROUP BY DATENAME(MM, dbo.JobLogTbl.JSTime),
MONTH(dbo.JobLogTbl.JSTime)
ORDER BY MONTH(dbo.JobLogTbl.JSTime)
I have not put all columns in, but I hope that this helps.
RLF
"Geoff" <Geoff@.discussions.microsoft.com> wrote in message
news:DC89754E-0A4C-4D78-B5C2-75F547C62D67@.microsoft.com...[vbcol=seagreen]
> How do I put in the condition of wether the DateDiff is >= 0. I was trying
> someting similiar to this view below but replacing the Count(CASE ACTCAT
> WHEN
> N'X4' Then ACTCAT END with something to make one column count the number
> of
> records where DATEDIFF(DD, dbo.JobLogTbl.JSTime,
> dbo.ContActivTbl.AddDate)
> AS DATEDIFF >=0 and another column show the count if DATEDIFF(DD,
> dbo.JobLogTbl.JSTime, dbo.ContActivTbl.AddDate) AS DATEDIFF < 0 in a view
>
>
> SELECT TOP 100 PERCENT DATENAME(MM, dbo.JobLogTbl.JSTime) AS Month,
> MONTH(dbo.JobLogTbl.JSTime) AS MoNum,
> COUNT(CASE ACTCAT WHEN N'X4' THEN ACTCAT END) AS X4,
> COUNT(CASE ACTCAT WHEN N'AG' THEN ACTCAT END) AS AG
> FROM dbo.ContActivTbl INNER JOIN
> dbo.JobLogTbl ON dbo.ContActivTbl.JobNo =
> dbo.JobLogTbl.JobNo
> WHERE (dbo.ContActivTbl.ActCat = 'X4') OR
> (dbo.ContActivTbl.ActCat = 'AG') AND
> (dbo.JobLogTbl.JSTime >= 01 / 01 / 07)
> GROUP BY DATENAME(MM, dbo.JobLogTbl.JSTime), MONTH(dbo.JobLogTbl.JSTime)
> ORDER BY MONTH(dbo.JobLogTbl.JSTime)
> I can do this in MS Access with
> TRANSFORM Count(ContActivTbl.JobNo) AS CountOfJobNo
> SELECT Format([JSTime],"mmmm") AS [Month], Count(ContActivTbl.JobNo) AS
> [Total Shipments], JobLogTbl.DLocNo
> FROM ContActivTbl RIGHT JOIN JobLogTbl ON ContActivTbl.JobNo =
> JobLogTbl.JobNo
> WHERE (((ContActivTbl.ActCat)="X4") AND ((JobLogTbl.JobStart)>#1/1/2007#)
> AND ((JobLogTbl.JSTime) Is Not Null) AND ((JobLogTbl.SoType)="1") AND
> ((JobLogTbl.ShipStatus)<>"Cancelled"))
> GROUP BY Month([JSTime]), Format([JSTime],"mmmm"), ContActivTbl.CoNo,
> JobLogTbl.DLocNo, JobLogTbl.SoType, JobLogTbl.ShipStatus
> ORDER BY Month([JSTime])
> PIVOT IIf([JobLogTbl].JSTime>=[ContActivTbl].AddDate,"Before
> Arrival","After
> Arrival");
> "Russell Fields" wrote: