Trying to select the last date an entry was saved

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trying to select the last date an entry was saved

Author

Topic

Lionheart
Starting Member

41 Posts

Posted - 2008-11-18 : 22:39:50

Hi

I have the following code to try and find the date when a program was created and when work was requested.

use DBName
SELECT     pr.ProgramID, pr.createdate, min(mr.RequestDate) as requestdate
FROM         tbl_program pr
inner join DBName2..tbl_modelrequest mr on mr.programID = pr.programID
inner join DBName2..tbl_ModelerSignoff ms on ms.programID = pr.programID
WHERE Year(pr.createdate) = 2008
group by pr.ProgramID, pr.createdate
order by pr.programID asc

In some cases there are multiple RequestDate values and I would like to take the first. Can anybody tell me how I can modify so that only one result for each prograID is given, which chooses the first value of the RequestDate?

A calculated field that would create a column showing the difference in number of days between createdate and requestdate would be very helpful also.

Thanks

LH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 22:46:29

your posted query looks fine. are you not getting desired result with it?

Lionheart
Starting Member

41 Posts

Posted - 2008-11-18 : 22:49:28

No...I thought it would work, but the following is an example of the results

ProgramID createdate requestdate
18226 2008-01-03 09:20:13.590 2008-01-07 14:50:13.093
18227 2008-01-03 14:22:27.013 2008-01-04 03:21:50.097
18242 2008-01-04 10:08:14.993 2008-01-10 10:19:27.130
18252 2008-01-04 09:41:34.030 2008-01-07 10:09:30.503
18288 2008-01-07 14:09:38.127 2008-01-07 08:11:41.340
18291 2008-01-07 15:46:53.267 2008-01-10 16:42:25.897
18293 2008-01-07 16:06:20.773 2008-02-04 14:57:32.130
18293 2008-01-07 16:06:20.773 2008-10-02 10:55:05.340

Prog 18293 has two entries with different requestdate values.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 22:56:01

thats because you've timepart in createddate. strip off timepart if you want to group by date alone.

Lionheart
Starting Member

41 Posts

Posted - 2008-11-18 : 23:24:28

Changed the query slightly and put in

SELECT     pr.ProgramID, day(mr.RequestDate-pr.createdate) as time

Which gives the # days between the dates. Still the issue with repeats, but there are very few, so can live with it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources