?
Solved

Number of days between 2 dates

Posted on 2003-03-17
2
Medium Priority
?
130 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
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 your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…
Suggested Courses

616 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