SQL Select Statement - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Select Statement

Author

Topic

jj32smith
Starting Member

7 Posts

Posted - 2007-06-17 : 11:04:39

I am new to using SQL Commands.

I have a database with a table that tracks projects.
I am trying to be able to compare to records in the table to get the diffdate between the two records.

Ex.
Table - Project
Fields -ProjectNo, SeqNo, Complete Date

Data
ProjectNo, SeqNo, Complete Date
50,1,01-01-07
50,2,01-03-07
50,3,01-04-07

I would like to see this data returned to Query Analyzer

ProjectNo, Start SeqNo, End SeqNo, Diffdatebetween 2 Dates
50,1,2,2 days
50,2,3,1 day

Any help appreciated. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-17 : 11:20:50

Try this.


select s.ProjectNo, Start_SeqNo = s.SeqNo, End_SeqNo = e.SeqNo, 
       DiffDate = datediff(day, s.CompleteDate, e.CompleteDate)
from   Project s inner join Project e
       on  s.ProjectNo    = e.ProjectNo
       and s.CompleteDate < e.CompleteDate
       and e.CompleteDate = (select min(CompleteDate) from Project x 
                                 where x.ProjectNo    = e.ProjectNo 
                                 and   x.CompleteDate > s.CompleteDate)

jj32smith
Starting Member

7 Posts

Posted - 2007-06-18 : 22:13:31

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources