Solved

# Number of days between 2 dates

Posted on 2003-03-17
Medium Priority
127 Views
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',1)) as Open,
Sum(Right([Status]='closed',1)) as Closed
FROM tblDefect
GROUP BY DefCat
ORDER BY DefCat ASC;

Thank - Craig
0
Question by:CraigBFG
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 4

Accepted Solution

CRagsdell earned 150 total points
ID: 8156086

DateDiff("d", Timestamp, DateClosed) As Days,

0

Author Comment

ID: 8158650
Didn't work straight away, had to change double quotes to single quotes. Also was missing an aggregate function at the beginning of the statement like count or sum, so I plumpled for AVG which is what I wanted.
Thanks.

final query looks like :-

SELECT DefCat as Category,
Count(DefCat) as Total,
Sum(Right([Status]='open',1)) as Open,
Sum(Right([Status]='closed',1)) as Closed,
Avg(DateDiff('d', Timestamp, DateClosed)) as Days
FROM tblDefect
GROUP BY DefCat
ORDER BY DefCat ASC;
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When deciding to adopt any help desk solutions many factors should be explored before taking decisions. This will change from business to another but in general there are some kind of rule of thumb. Here are some quick tips: Do we need only ticket…
In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
###### Suggested Courses
Course of the Month11 days, 13 hours left to enroll