Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Maverick_
Posting Yak Master
107 Posts |
 Posted - 2010-02-01 : 09:30:16
|
Hi thereI am trying to figure out how to use SQL to calculate how late a outstanding job is today - from when its due.For example:A job was created to install a new lamp on a street lamp. The Job was entered on 01-01-2010, its estimated completion date was 14-01-2010.The job is still outstanding, and has not been completed (even today).I want the query to use the latest time everytime the query is run.Any idea how to do this?Has it got anything to do with the date diff function? I tried using it, but I don't know how to insert a column that has the 'estimated completion date' into the datediff statement.  My SELECT statement looks something like this:job.job_number,job.job_location,job.job_creation_date,job.est_start_date,job.estimated_completion_date,FROMjobI want the datediff result to be included in my results.Any help would be greatly appreciated  |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-02-01 : 10:00:54
|
| You're going to want something likejob.job_number,job.job_location,job.job_creation_date,job.est_start_date,job.estimated_completion_date,DATEDIFF(day,estimated_completion_date,getdate()) as DaysOverdueFROMjobDaysOverdue will be > 0 if it is overdue, and < 0 if it is notJimEveryday I learn something that somebody else already knew |
 |
|
|
Maverick_
Posting Yak Master
107 Posts |
Posted - 2010-02-01 : 10:31:21
|
Wow thanks Pete that worked.P.S. That quote at the end of your posts is pretty witty!  |
|
|
|
|
|
|
|
|
A query that calculates how late a job is?