How to Subtract 2 Dates in a query? 2

[dkmidi]

Hi Everyone,

I have a query filtered down to two records each with a date. The dates are in the same field, one on top of the other. (Not side by side)

Example:
Jan. 1 Project Start
Feb.12 Project End

Does anyone know how I can subtract the two dates to find the number of days between them? I tried using a report but couldn't do it.

Thanks!

Derek

ps. is this poor design? I created a DB with a single Date field then created a second field called Type to distinguish. The purpose was to create a running log. But what happens is that field "Type" has options for a wide range of non-related things. Should I have created a separate field for each date? ie.Initiate Date, Plan Date, Start Date, Close Date, End Date, etc.?

 

[r937]

you don't have to have separate columns for different dates, but in some designs it makes sense

(can't tell without seeing all your tables, your business rules, and the types of applications the databse must support)

as for your running log, this is an acceptable design, and the way that you can get the date difference is by a self-join

select start.name
     , datediff("d"
               ,start.logdate
               ,stop.logdate) as days_diff
  from logfile as start
inner
  join logfile as stop
    on start.name = stop.name              
 where start.type = 'Project Start'   
   and stop.type = 'Project End'

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[dkmidi]

Hi, I created a small table to try the code but it returned no results. What am I missing?

Thanks!

Derek


Table: [T_Logfile]

TableTest_ID Date Type
1 2005/01/01 Project Start
2 2005/02/23 Project End

Query Code:
SELECT Start.Date
, DateDiff("d"
,start.date
,stop.date) AS days_diff
FROM T_LogFile AS Start
INNER JOIN T_LogFile AS Stop
ON Start.Date = Stop.Date
WHERE (((Start.Type)='Project Start')
AND ((Stop.Type)='Project End'));

 

[r937]

you shouldn't join on Date (this explains why there were 0 results) but rather on whatever column you use to identify your projects

you sample table has only one project -- is this true for your real table?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[dkmidi]

Thanks Rudy! It works now.. but not sure if it's good? I kept the code the same but changed

ON Start.Date = Stop.Date to ON Start.Date <= Stop.Date

It worked! 53 days. But I shouldn't JOIN on Date you say? My real table has several different start stops actually and I'll need to calculate the dates between each. I added another column to distinguish it. But i'll try it out.

 

[r937]

My real table has several different start stops actually

then it is imperative that you find something else to match on, otherwise you will get cross join effects as every start date will match up with all later stop dates

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[PHV]

If you can't differentiate the several start stops, but they are chronologically started/stopped, you may consider something like this:
SELECT Start.Date, Min(Stop.Date-Start.Date) AS days_diff
FROM T_LogFile AS Start
INNER JOIN T_LogFile AS Stop ON Start.Date < Stop.Date
WHERE Start.Type='Project Start' AND Stop.Type='Project End'
GROUP BY Start.Date;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dkmidi]

Thanks for the help Rudy! And thanks for the great solution PH, worked like a charm!

Derek