Yes, it would work as Damien describes, but you would have to modify you
table if you wanted to add more than 3 start or finish dates. You really
should denormalize a bit.
How about
:
CREATE TABLE Jobs
(
JobID int,
Junk1 varchar(10),
Junk2 varchar(10)
--etc...
)
CREATE TABLE JobDates
(
JobID int,
StartFinish char(1) CONSTRAINT ckStartFinish CHECK (StartFinish IN('S',
'F')),
JobDate datetime
)
INSERT INTO Jobs VALUES (1, 'asdfad', 'asdfad')
INSERT INTO Jobs VALUES (2, 'asdfad', 'asdfad')
INSERT INTO Jobs VALUES (3, 'asdfad', 'asdfad')
INSERT INTO JobDates VALUES (1, 'S', '20041001')
INSERT INTO JobDates VALUES (1, 'S', '20041002')
INSERT INTO JobDates VALUES (1, 'S', '20041003')
INSERT INTO JobDates VALUES (2, 'S', '20041001')
INSERT INTO JobDates VALUES (2, 'S', '20041002')
INSERT INTO JobDates VALUES (3, 'S', '20041001')
INSERT INTO JobDates VALUES (1, 'F', '20041011')
INSERT INTO JobDates VALUES (1, 'F', '20041006')
INSERT INTO JobDates VALUES (2, 'F', '20041007')
Then you can do something like:
SELECT a.JobID,
StartDate = (SELECT MAX(b.JobDate) FROM JobDates b WHERE b.StartFinish = 'S'
AND b.JobID = a.JobID),
FinishDate = (SELECT MAX(b.JobDate) FROM JobDates b WHERE b.StartFinish =
'F' AND b.JobID = a.JobID)
FROM Jobs a
Just an idea...
-Andy
"Jagdip Ajimal" <js*****@hotmail.com> wrote in message
news:41**********************@news.newsgroups.ws.. .
Does this work even if more than 2 fields are filled?
I need it to select the highest PSnumber. More that one PS field can be
filled.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
