Selecting min(date) question. - 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 2000 Forums

Transact-SQL (2000)

Selecting min(date) question.

Author

Topic

Crespo24
Village Idiot

144 Posts

Posted - 2003-01-08 : 05:27:53

OK.... I will try and explain this as best as I could.

I have a table consisting of two columns, member_key and a date. Each record is unique combination of member_key and a date.

I also have a second table which hold multiple records for each member_key. For example,

Table1

Member_key | Date1
1000 | 01/01/2001

Table2
Member_key | Contribution_date
1000 | 01/01/1998
1000 | 01/01/1978
1000 | 01/01/1993
1000 | 01/01/2002

What I need to do is update the second table and replace the min(contribution_date) with the date that is in table1. So in other words, replace 01/01/1978 with 01/01/2001 which is the date in Table1.

Any ideas?

Thanks.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-01-08 : 05:35:37

Hi Crespo

OK, basically you need to use this sort of a technique but with a subquery.

Something like this (untested)



UPDATE Table2

SET contribution_date = date1

FROM
Table2
INNER JOIN Table1 ON Table1.Member_Key = Table2.Member_Key

WHERE
contibution_date = (

SELECT MIN(contribution_date) FROM table2 t2
WHERE member_key = Table2.member_key
)

Give that a try.

Damian

Edited by - merkin on 01/08/2003 05:36:03

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-01-08 : 05:38:11

you need a correlated UPDATE statement....

a forum search on "update, from ,where" all-words option trns up loads of examples....

the following link is one
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22543

have a look at the structure of the UPDATE statement in ValterBorges reply....ignore the CASE part...not relevent for you i believe....you may have to play around with the "where" clause.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-08 : 06:07:00

update Table2
set Contribution_date = Table1.Date1
where Table1.Member_key = Table2.Member_key
and Contribution_date =
(select min(Contribution_date) from Table2 t2 where t2.Member_key = Table2.Member_key)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Crespo24
Village Idiot

144 Posts

Posted - 2003-01-08 : 06:07:19

Thanks all... it works

stevep
Starting Member

17 Posts

Posted - 2003-01-12 : 21:22:33

Can I add my own complication to a similar query.

I've a table listing project deliverables (id, name, etc) and a table
listing versions of those deliverables (description, date submitted, etc).

Using the a sub-query suggested in the above discussion I've got a query that gives me the most recent version of each deliverable for a given project:

SELECT deliverables.deliverableID, deliverables.deliverableName, deliverableVersions.dvVersion, deliverableVersions.dvDescription, deliverableVersions.dvSubmittedDate
FROM deliverables
LEFT JOIN deliverableVersions ON deliverables.deliverableID = deliverableVersions.dvDeliverable
WHERE (((deliverableVersions.dvSubmittedDate)=(SELECT MAX(dvSubmittedDate) FROM deliverableVersions DV2 WHERE dv2.dvDeliverable = deliverables.deliverableID))
AND ((deliverables.deliverableProject)=10));

Now the bit that has be stumped is how to include in the query results the names of deliverables that do not have a version recorded yet. However I define the join between deliverables and versions I cannot seem to retrieve details of a deliverable that has no versions.

Ta for help.

Steve

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-13 : 07:35:10

Never use a WHERE on the outer table of an outer join -- it turns your outer join into an inner join.

Example:

SELECT blah blah
FROM A
LEFT OUTER JOIN B
ON A.ID = B.ID
WHERE B.Value > 0

is the same as

SELECT blah blah
FROM A
INNER JOIN B
ON A.ID = B.ID
WHERE B.Value > 0

Because the WHERE eliminates Nulls.

The solution is either

SELECT Blah blah
FROM A
LEFT OUTER JOIN B
ON A.ID = B.ID AND B.Value > 0

or

SELECT blah blah
FROM A
LEFT OUTER JOIN
(SELECT * FROM B WHERE Value > 0) B
ON A.ID = B.ID

Does that help? Try using the above as a guideline to rewrite your SQL. If you are still stuck, let me know. But I find deriving the answer yourself is usually more useful because then you really know what's going on.

- Jeff

stevep
Starting Member

17 Posts

Posted - 2003-01-13 : 19:06:37

Jeff,

Ta muchly, both for the suggestion and the explanation of why my version did not work. Have managed to get half of it to work:

SELECT B.deliverableID, B.deliverableName, A.dvVersion, A.dvDescription, A.dvSubmittedDate
FROM deliverableVersions AS A RIGHT JOIN [SELECT * FROM deliverables WHERE deliverableProject = 10]. AS B ON A.dvDeliverable = B.deliverableID

Now I'm guessing to include the part of the original WHERE that included only the most recent version I need to change that into an a single SQL statement that brings back the required table of versions. Am now working that bit of it out (slowly).

Thanks again.

Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources