getting value from max date - 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)

getting value from max date

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-03 : 13:12:26

Here I have a table called tbl_items

item_id   	emp_id   date    	amount
1		2345	12/12/2004	100
4		2345	01/01/2005	200
5		4567	03/03/2005	500
7		4567	05/05/2005	NULL

Please somebody help me with this query.I need to get the amount for the max date of the same emp_ids
say result shoould be

for 2345 200
for 4567 NULL

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-03 : 13:21:36

will this do?

select t1.*
from tbl_items t1
join (select emp_id, max(date) as date from tbl_items group by empId) t2 on t1.emp_id = t2.emp_id and ti.date = t2.date

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-03 : 13:52:27

I didn't get the result that sqllearner was trying to obtain when I applied spirit1's code.

My results read:

5 4567 0 500
7 4567 0 NULL
1 2345 0 100
4 2345 0 200

what am I not seeing?

Here's my test:

SET NOCOUNT ON

drop table tbl_items

CREATE TABLE tbl_items (item_id int null,
emp_id nvarchar(4) null,
edate nvarchar(10) null,
amount int null)

INSERT INTO tbl_items SELECT 1,2345,12/12/2004,100
INSERT INTO tbl_items SELECT 4,2345,01/01/2005,200
INSERT INTO tbl_items SELECT 5,4567,03/03/2005,500
INSERT INTO tbl_items SELECT 7,4567,05/05/2005,NULL

SELECT * FROM tbl_items
go

select t1.*
from tbl_items t1
join
(
select emp_id, max(edate) as edate
from tbl_items
group by emp_id
)
t2 on (t1.emp_id = t2.emp_id)
and (t1.edate = t2.edate)

Semper fi,

Xerxes, USMC (Ret)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-03 : 14:00:41

you're missing the ':
INSERT INTO tbl_items SELECT 1,2345, '12/12/2004' ,100
INSERT INTO tbl_items SELECT 4,2345, '01/01/2005' ,200
INSERT INTO tbl_items SELECT 5,4567, '03/03/2005' ,500
INSERT INTO tbl_items SELECT 7,4567, '05/05/2005' ,NULL

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-03 : 14:02:05

Thanks....

Semper fi,

Xerxes, USMC (Ret)

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-03 : 14:03:57

Uh, even with that correction, I'm still getting a result different than what I expected:

7 4567 05/05/2005 NULL
1 2345 12/12/2004 100

Hmmm...

Semper fi,

Xerxes, USMC (Ret)

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-03 : 14:05:24

And change edate to datetime to get the desired result otherwise you will get 2345 100 instead of 200

Andy

Edit: Uncanny!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-03 : 14:15:09

quote:
Originally posted by AndyB13

And change edate to datetime to get the desired result otherwise you will get 2345 100 instead of 200

Andy

Edit: Uncanny!

Yep, Andy, that did it!

Semper fi,

Xerxes, USMC (Ret)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources