Single row from two Selects? - 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)

Single row from two Selects?

Author

Topic

austenx
Starting Member

2 Posts

Posted - 2011-08-16 : 12:30:52

Good afternoon all,

I’m trying to write an SQL select statement for a report written in VB.net (Hence trying to get all the fields within a single row)

I have a table that contained multiple entries based around a common job number (IJN).

Each entry for an IJN has a sequence number, startTime & Endtime (both date/time format)

IE

RecID IJN Sequence StartT EndT
100 1881 10 xx xx
101 1881 20 xx xx
199 1881 30 xx xx
151 1881 40 xx xx (you get the idea)

What I am struggling with is to retrieve the Start time of the first Sequence and the End time of the last Sequence in the same single result.

There is quite a long “where” element to the existing select statement.

The current report only show the end time of the last sequence – my boss is now asking for the start time of the first and the end time of the last on the same row! (*grumble*).

Any help would be most welcome.

I have spent most of the afternoon playing around with Unions, joins etc.... got a headache now.

Thank you

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-16 : 13:39:25

Assuming that start and end times increment/increase along with the sequence:

SELECT IJN, MIN(StartT) StartT, MAX(EndT) EndT FROM jobTable GROUP BY IJN

austenx
Starting Member

2 Posts

Posted - 2011-08-17 : 06:14:48

Thank you Rob for your quick reply.

That is working a treat, top man - If we were at school I would give you a gold star! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources