Insert & Update with the Max Date

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)

Insert & Update with the Max Date

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-10 : 20:33:41

I have a table called tbl_emp_process where below shown are the data-set.Again I have another master table called tbl_service_master
which stores the master service information.I need to insert the new values to tbl_service master with the record of the latest create date if the service
code is not present and I need to update if its present there with the record of the latest create_date

In this case for service_code '005' there are two records emp_id = 81353 and emp_id=81356.
It can be any of the records that should be inserted to tbl_service_master since both have the same create date
same is the case with update also.

Iam really finding difficult in finding a query.I tried distinct,top with max date but is not working.Please help me..............



tbl_emp_process


emp_id		create_date			service_code	service_state	process
-----------------------------------------------------------------------------------------
81351 		2006-01-09 14:44:26.470 	005		CA 		AUTOMATED     

81352 		2006-01-10 14:44:26.470 	005		SDFF		AUTOMATED    

81353 		2006-01-11 14:44:26.470 	005		SDFF 		AUTOMATED             

81356  		2006-01-11 14:44:26.470 	005		MGIC    	DEFAULTED   

81350  		2006-01-11 14:44:26.470 	003		DRGH        	MANUAL           

81359  		2006-01-11 14:44:26.470 	001		MGIC        	DEFAULTED            

81355  		2006-01-11 14:44:26.470 	001		MGIC        	DEFAULTED     

81345  		2006-01-11 14:44:26.470 	009		KSDF        	MANUAL   





tbl_service_master

service_code	service_state	process
----------------------------------------
009		KSDF        	MANUAL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-10 : 20:44:41

so for service_code '005', which record you want to update to tbl_service_master ? What is your criteria ?

-----------------
'KH'

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-10 : 20:52:11

Since both have the same create date either emp_id = 81353 and emp_id=81356 can be inserted.we can pick the first one that means the empid=81353.
The criteria is to insert and update into the master table from the above given tbl_emp_process
table with the latest service codes and values

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-10 : 22:07:14

Try this

create table #tbl_emp_process
(
	emp_id		int,
	create_date	datetime,
	service_code	varchar(10),
	service_state	varchar(10),
	process		varchar(20)
)

insert into #tbl_emp_process(emp_id, create_date, service_code, service_state, process)
select 81351, '2006-01-09 14:44:26.470','005','CA','AUTOMATED'		union all
select 81352, '2006-01-10 14:44:26.470','005','SDFF','AUTOMATED'	union all
select 81353, '2006-01-11 14:44:26.470','005','SDFF','AUTOMATED'	union all
select 81356, '2006-01-11 14:44:26.470','005','MGIC','DEFAULTED'	union all
select 81350, '2006-01-11 14:44:26.470','003','DRGH','MANUAL'		union all
select 81359, '2006-01-11 14:44:26.470','001','MGIC','DEFAULTED'	union all
select 81355, '2006-01-11 14:44:26.470','001','MGIC','DEFAULTED'	union all
select 81345, '2006-01-11 14:44:26.470','009','KSDF','MANUAL'


create table #tbl_service_master
(
	service_code	varchar(10),
	service_state	varchar(10),
	process		varchar(20)
)

insert into #tbl_service_master(service_code, service_state, process)
select	b.service_code, b.service_state, b.process
from
(       -- get the lastest create date for each service_code
	select 	service_code, emp_id
	from	#tbl_emp_process p
	where	emp_id in (select top 1 emp_id from #tbl_emp_process x where x.service_code = p.service_code order by create_date desc, emp_id)
	group by service_code, emp_id
) as a
inner join
#tbl_emp_process b
on	a.emp_id	= b.emp_id
and	a.service_code	= b.service_code


select 	* 
from 	#tbl_service_master
order by service_code

drop table #tbl_emp_process
drop table #tbl_service_master

-----------------
'KH'

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-11 : 13:12:42

Thanks a lot ....Here this would work fine if the all the emp_id's are unique in the table.There are cases where there are records with the same emp_ids with a different date as

emp_id create_date service_code service_state process
-----------------------------------------------------------------------------------------
81351 2006-01-08 14:44:26.470 005 CA AUTOMATED

81352 2006-01-17 14:44:26.470 005 SDFF AUTOMATED

81351 2006-01-03 14:44:26.470 005 CA AUTOMATED

81352 2006-01-02 14:44:26.470 005 SDFF AUTOMATED

Then the above query gives a different result.....

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-11 : 13:15:45

Thanks a lot ....Here this would work fine if the all the emp_id's are unique in the table.There are cases where there are records with the same emp_ids with a different date as


emp_id     create_date                    service_code      service_state            process
-----------------------------------------------------------------------------------------
81351      2006-01-08 14:44:26.470         005                   CA                   AUTOMATED 

81352      2006-01-17 14:44:26.470         005                   FL                    AUTOMATED 

81351      2006-01-03 14:44:26.470         005                   CA                    AUTOMATED 

81352      2006-01-02 14:44:26.470         005                   FL                    AUTOMATED

Then the above query gives a different result.....

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-11 : 13:51:57

Please help me out....

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-11 : 15:08:18

insert into #tbl_emp_process(emp_id, create_date, service_code, service_state, process)
select 81351, '2006-01-08 14:44:26.470','005','CA','AUTOMATED' union all
select 81352, '2006-01-17 14:44:26.470','005','FL','AUTOMATED' union all
select 81351, '2006-01-03 14:44:26.470','005','CA','AUTOMATED' union all
select 81352, '2006-01-12 14:44:26.470','005','FL','AUTOMATED' union all

Above shown is the insert for the remaining records.Please help me out with the query

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-01-11 : 16:23:18

Might be more complicated than necessary, but see if this works for you:


SELECT emp1.service_code, emp1.service_state, emp1.process 
FROM #tbl_emp_process AS emp1 
JOIN	(SELECT emp2.service_code, emp3.max_create_date, MAX(emp2.emp_id) AS max_emp_id 
	FROM #tbl_emp_process AS emp2 
	JOIN	(SELECT service_code, MAX(create_date) AS max_create_date 
		FROM #tbl_emp_process 
		GROUP BY service_code) AS emp3 
	ON emp2.service_code = emp3.service_code AND emp2.create_date = emp3.max_create_date 
	GROUP BY emp2.service_code, emp3.max_create_date) AS emp4 
ON emp1.service_code = emp4.service_code AND emp1.create_date = emp4.max_create_date AND emp1.emp_id = emp4.max_emp_id

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-11 : 18:11:02

Though complicated it works well..Thanks...a...lot....everyone.......Just a question can we do the same query using top 1 subquery

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources