?
Solved

Number of days between 2 dates

Posted on 2003-03-17
2
Medium Priority
?
127 Views
Last Modified: 2013-12-24
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
WHERE (AddID = ::AddID::)
GROUP BY DefCat
ORDER BY DefCat ASC;

Thank - Craig
0
Comment
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
  • Learn & ask questions
2 Comments
 
LVL 4

Accepted Solution

by:
CRagsdell earned 150 total points
ID: 8156086
Add this to your SQL:

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

0
 

Author Comment

by:CraigBFG
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
WHERE (AddID = ::AddID::)
GROUP BY DefCat
ORDER BY DefCat ASC;
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

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

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question