selecting the latest date from different columns

[Jagdip Singh Ajimal]

I have 6 columns, all with dates within them, i.e.

Proposed Start Date 1
Proposed Start Date 2
Proposed Start Date 3
Proposed Finish Date 1
Proposed Finish Date 2
Proposed Finish Date 3

What I need to do is narrow this down into two fields:
Start Date
Finish Date

So I need to find the newest value from the columns. i.e.
If PS1 filled PS2 and PS3 empty, then Start Date = PS1
If PS3 empty and PS2 filled, then Start Date = PS2
If PS3 filled then PS3

and similarly for Proposed Finish Dates.

Anyone knew how I can do this.

(Maybe the following will help for the programmers out there:
If PS3 <> null
Then StartDate = PS3
Else if PS2 <> null
Then StartDate = PS2
Else
StartDate = PS1
End IF

Jagdip

[Andy Williams]

Re: selecting the latest date from different columns

Jagdip,

Look up COALESCE in BOL.

Something like this?

SELECT
StartDate = COALESCE(Propos edStartDate3, ProposedStartDa te2,
ProposedStartDa te1),
FinishDate = COALESCE(Propos edFinishDate3, ProposedFinishD ate2,
ProposedFinishD ate1)
FROM MyTable

-Andy

"Jagdip Singh Ajimal" <jsa1981@hotmai l.com> wrote in message
news:c84eb1b0.0 410040608.52dd5 f22@posting.goo gle.com...[color=blue]
>I have 6 columns, all with dates within them, i.e.
>
> Proposed Start Date 1
> Proposed Start Date 2
> Proposed Start Date 3
> Proposed Finish Date 1
> Proposed Finish Date 2
> Proposed Finish Date 3
>
> What I need to do is narrow this down into two fields:
> Start Date
> Finish Date
>
> So I need to find the newest value from the columns. i.e.
> If PS1 filled PS2 and PS3 empty, then Start Date = PS1
> If PS3 empty and PS2 filled, then Start Date = PS2
> If PS3 filled then PS3
>
> and similarly for Proposed Finish Dates.
>
> Anyone knew how I can do this.
>
> (Maybe the following will help for the programmers out there:
> If PS3 <> null
> Then StartDate = PS3
> Else if PS2 <> null
> Then StartDate = PS2
> Else
> StartDate = PS1
> End IF
>
> Jagdip[/color]

[Jagdip Ajimal]

Re: selecting the latest date from different columns

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!

[Damien]

Re: selecting the latest date from different columns

Jagdip Ajimal <jsa1981@hotmai l.com> wrote in message news:<416272de$ 0$26090$c397aba @news.newsgroup s.ws>...[color=blue]
> 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![/color]

COALESCE returns the first non-NULL argument supplied to it, so
provided you pass the columns in order of most desired - least
desired, it'll work as wanted

[Andy Williams]

Re: selecting the latest date from different columns

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" <jsa1981@hotmai l.com> wrote in message
news:416272de$0 $26090$c397aba@ news.newsgroups .ws...[color=blue]
> 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![/color]

[David Rawheiser]

Re: selecting the latest date from different columns

Firstly bad database design - as your dates are not normalized in that they
appear across a row and not down in their own columns.
But I am assuming you are needing to work with the bad design of others, so
I am taking pity on you, as I have been there before.
If you made this design yourself, please don't infect others and check
yourself into a Data Modeling class as soon as possible..

The aggregate function max doesn't work unless it has a table or result set
to process.
So we would have to create one from your individual selects thru a union
(reverse pivot your table) and assign that an alias so it looks like a real
table.

select max( dDate )
from
(
select start1 as dDate ...
union all
select start1 ...
union all
select finish2 ...
union all
select finish1 ...
) as Dates

replace the '...' above with the rest of your from and where clause to
generate.


"Jagdip Ajimal" <jsa1981@hotmai l.com> wrote in message
news:416272de$0 $26090$c397aba@ news.newsgroups .ws...[color=blue]
> 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![/color]