473,583 Members | 3,049 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

selecting the latest date from different columns

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
Jul 20 '05 #1
5 4013
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" <js*****@hotmai l.com> wrote in message
news:c8******** *************** ***@posting.goo gle.com...
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

Jul 20 '05 #2
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!
Jul 20 '05 #3
Jagdip Ajimal <js*****@hotmai l.com> wrote in message news:<41******* *************** @news.newsgroup s.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!


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
Jul 20 '05 #4
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*****@hotmai l.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!

Jul 20 '05 #5
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" <js*****@hotmai l.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!

Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
5116
by: Gleep | last post by:
Hey Guys, I've got a table called Outcomes. With 3 columns and 15 rows 1st col 2nd col 3rdcol outcome date price There are 15 rows for each record, each row accounts for a different type of outcome I'm having trouble with MySQL date comparison. I'm looking for some kind of...
3
9620
by: jas | last post by:
> This is what I want to do with the data table below. > > I only want it to show one id for each issue. ( ie, 4001 only once, > 4002 only once, etc. But only the one with the latest date and time) > > Ie. 4001 should only be in the table once, with the latest date - > which should be : > 4001 09/12/2003 17:12:09 (as I only want to show...
6
2049
by: aaj | last post by:
Hi all I use a data adapter to read numerous tables in to a dataset. The dataset holds tables which in turn holds full details of the records i.e. keys, extra colums etc.. In some cases I need to use parts of the tables in datagrids, and here is where my problem lies
24
19892
by: clare at snyder.on.ca | last post by:
I have a SQL query I need to design to select name and email addresses for policies that are due and not renewed in a given time period. The problem is, the database keeps the information for every renewal in the history of the policyholder. The information is in 2 tables, policy and customer, which share the custid data. The polno changes...
8
2179
by: TGEAR | last post by:
I have an ItemSTDPriceHistory table as below and this is a child table of itemlookup table with one to many relationship. if exists (select * from dbo.sysobjects where id = object_id(N'.') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table . GO
4
1814
by: J | last post by:
I am editing a pre-existing view. This view is already bringing data from 40+ tables so I am to modify it without screwing with anything else that is already in there. I need to (left) join it with a new table that lists deposits and the dates they are due. What I need is to print, for each record in the view, the due date for the next...
1
2271
by: ode | last post by:
I am an infrequent user and my version is Access 2000. I have a list of 122k lines which consits of 3 fields/data points;a product code, a date, and a price code. I want to run a query to select the latest date the product code was entered and return the price code associated with the date. There are entrys for many different dates, none of...
0
228
by: Guido Konsolke | last post by:
"JN" <jirinej@volny.czschrieb im Newsbeitrag news:7e29a94a.0307090205.179bca49@posting.google.com... all Firstl of all: it's not too smart posting your question without enough infos. At least the table definition and a few rows of data... Second: don't try to name your columns using restricted words (date).
1
1698
by: WarcraftNoob | last post by:
This is what I have right now, SELECT Project, S_on, P_on, H_on, A_on, I_on, 1_on, 2_on, 3_on, 4_on, Z_on, D_on, T_on, K_on, F_on, J_on, FINAL_on From DTable WHERE Class='XXX' AND Project like 'Proj%' AND QA= 'ST' AND FINAL_on is not NULL This will query make me a nice big table,
0
7896
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8186
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8329
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7936
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6583
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5377
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3847
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1436
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1160
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.