CraigBFG
asked on
Number of days between 2 dates
I need to be able to show the number of days between 2 dates, but only for items with Status=Closed.
Days = DateClosed-Timestamp
This is to be incorporated into an existing custom query within FP2002. So ultimately, the resulting table shows me :-
Category - Total - Qty Open - Qty Closed - Avg Days Open
SELECT DefCat as Category,
Count(DefCat) as Total,
Sum(Right([Status]='open',
Sum(Right([Status]='closed
FROM tblDefect
WHERE (AddID = ::AddID::)
GROUP BY DefCat
ORDER BY DefCat ASC;
Thank - Craig
Days = DateClosed-Timestamp
This is to be incorporated into an existing custom query within FP2002. So ultimately, the resulting table shows me :-
Category - Total - Qty Open - Qty Closed - Avg Days Open
SELECT DefCat as Category,
Count(DefCat) as Total,
Sum(Right([Status]='open',
Sum(Right([Status]='closed
FROM tblDefect
WHERE (AddID = ::AddID::)
GROUP BY DefCat
ORDER BY DefCat ASC;
Thank - Craig
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
final query looks like :-
SELECT DefCat as Category,
Count(DefCat) as Total,
Sum(Right([Status]='open',
Sum(Right([Status]='closed
Avg(DateDiff('d', Timestamp, DateClosed)) as Days
FROM tblDefect
WHERE (AddID = ::AddID::)
GROUP BY DefCat
ORDER BY DefCat ASC;