Issue in updating the latest value

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)

Issue in updating the latest value

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-12-12 : 19:48:47

[code]cc1.dbo.tax_info
----------------
emp_id create_date occurences dis_type dis_due_date dis_amount dis_term
432 12/09/2005 1 212 12/25/2005 100 5
432 12/09/2005 2 213 12/27/2005 200 7

cc2.tax_info
-----------------
emp_id create_date occurences dis_type dis_due_date dis_amount dis_term processed
432 12/09/2005 1 212 12/25/2005 100 5 Y
432 12/09/2005 2 213 12/27/2005 200 7 Y

432 12/10/2005 1 212 12/30/2005 500 8 N

432 12/10/2005 1 212 01/13/2006 800 9
N

432 12/11/2005 1 212 02/25/2006 1000 9 N
432 12/11/2005 2 213 03/27/2006 300 11 N[/code]

Here I am trying to update cc1.dbo.tax_info table with the values from cc2.dbo.tax_info table.I want to
get only the latest records from cc2.dbo.tax_info table which is based on the create_date (max create_date for
emp_id and occurences and dis_type).Please correct my query

UPDATE
cc1.dbo.tax_info
SET
dis_due_date = dti.dis_due_date,
dis_amount =dti.dis_amount,
dis_term = dti.dis_term

FROM
cc1.dbo.tax_info dti
INNER JOIN cc2.dbo.tbl_tax_info ti ON
dti.emp_id=ti.emp_id AND
dti.dis_type=ti.dis_type AND
dti.occurences=ti.occurences AND
ti.processed='N'
INNER JOIN cc1.dbo.emp_detail ld ON
dti.emp_id=ld.emp_id
ld.emp_status='A'

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-12 : 20:02:40

First things first. Are you updating anything?

UPDATE
cc1.dbo.tax_info
SET
dis_due_date = dti.dis_due_date,
dis_amount =dti.dis_amount,
dis_term = dti.dis_term

FROM
cc1.dbo.tax_info dti
INNER JOIN cc2.dbo.tbl_tax_info ti ON
dti.emp_id=ti.emp_id AND
dti.dis_type=ti.dis_type AND
dti.occurences=ti.occurences AND
ti.processed='N'
INNER JOIN cc1.dbo.emp_detail ld ON
dti.emp_id=ld.emp_id
ld.emp_status='A'

Nathan Skerl

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-12-12 : 20:05:50

Sorry instead of 'dti' in the update its 'ti'

UPDATE
cc1.dbo.tax_info
SET
dis_due_date = ti.dis_due_date,
dis_amount =ti.dis_amount,
dis_term = ti.dis_term

FROM
cc1.dbo.tax_info dti
INNER JOIN cc2.dbo.tbl_tax_info ti ON
dti.emp_id=ti.emp_id AND
dti.dis_type=ti.dis_type AND
dti.occurences=ti.occurences AND
ti.processed='N'
INNER JOIN cc1.dbo.emp_detail ld ON
dti.emp_id=ld.emp_id
ld.emp_status='A'

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-12-12 : 20:40:51

How do I get only the latest records from cc2.dbo.tax_info table which is based on the create_date (max create_date for emp_id and occurences and dis_type).Please help me to correct my query

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-13 : 11:13:11

Select the MAX(create_date) and group by the other columns you mentioned.

Put the above select in a subquery and join back to your table on emp_id and create_date.

Does that make sense?

Nathan Skerl

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-12-13 : 14:58:42

I have updated the query and I got the result.Thanks a lot.Iam was just thinking is there any way we can optimize this query or is this the max we can do.

UPDATE
cc1.dbo.tax_info
SET
dis_due_date = ti.dis_due_date,
dis_amount =ti.dis_amount,
dis_term = ti.dis_term

FROM
cc1.dbo.tax_info dti
INNER JOIN cc2.dbo.tbl_tax_info ti ON
dti.emp_id=ti.emp_id AND
dti.dis_type=ti.dis_type AND
dti.occurences=ti.occurences AND
ti.processed='N'
INNER JOIN cc1.dbo.emp_detail ld ON
dti.emp_id=ld.emp_id
ld.emp_status='A'
INNER JOIN
(
SELECT max(create_date)as create_date,emp_id,dis_type,
occurences from tax_info
group by emp_id,dis_type,occurences
) X on X.emp_id=ti.emp_id and
X.create_date=ti.create_date and
X.dis_type=ti.dis_type AND
X.occurences=ti.occurences AND
ti.processed='N'

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-13 : 15:20:54

Which one of these rows would you want to use for your update? And what is the primary key of this table??

432	12/11/2005   1		212	02/25/2006   1000      9	   N
432	12/11/2005   2		213	03/27/2006    300      11	   N

Nathan Skerl

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-12-13 : 15:23:52

Both of them should update to cc1.dbo.tax_info since the occurence and disb_type are different and create date '12/11/2005' is the latest record in both the cases.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-13 : 15:27:15

Ok, I see. I was wondering why you were joining on all those columns!

Then you should be able to simplfy this join as such (youve already inner joined the other fields from ti):

...
INNER JOIN (	SELECT 	max(create_date) as create_date,
			emp_id
		from tax_info
		group by emp_id) X 
	on X.emp_id = ti.emp_id 
	and X.create_date=ti.create_date

Nathan Skerl

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-12-13 : 16:12:10

This is a perfect example of why you should use views!

Create a view that encapsulates the "Last Records" and use it everywhere....

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-13 : 16:17:37

I second that!

Nathan Skerl

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-12-13 : 17:31:48

We are not allowed to use Views and the below shown query what u have send me will give me only a single record
which has the max date for the emp_id but dis_type and occurences also matters.

INNER JOIN ( SELECT max(create_date) as create_date,
emp_id
from tax_info
group by emp_id) X
on X.emp_id = ti.emp_id
and X.create_date=ti.create_date

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-12-13 : 17:39:33

You only need one row in the subquery. You will get the other columns from your ti join. The join to the subquery is only to limit it to the max(date) and emp_id.

432 12/09/2005 1 212 12/25/2005 100 5
432 12/09/2005 2 213 12/27/2005 200 7

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources