Retrieving the latest date from a subquery (with more than one entry) in the Insert statement

Question

I have a table that I need to copy my data from (Log) to another table (BigTable). The log table received the following data:

LogID LogTime JobNumber LogType Description ===== ======= ========= ======= =========== 1 2012-09-01 00:00:01 1 100 Accepted by D#12 2 2012-09-01 00:05:33 1 100 Accepted by D#14 3 2012-09-01 01:00:14 2 107 Message sent 4 2012-09-01 05:00:53 2 100 Accepted by D#78 5 2012-09-01 05:01:55 1 110 POB at Stop 1 6 2012-09-01 05:02:22 3 100 Accepted by D#98 7 2012-09-01 05:03:00 1 110 POB at Stop 2 8 2012-09-01 05:04:00 2 110 POB at Stop 1 9 2012-09-01 05:05:25 3 110 POB at Stop 1 10 2012-09-01 05:15:36 1 200 Completed 11 2012-09-01 05:20:45 2 200 Completed 

The following data is already in BigTable

  JobNumber Accepted_At POB_At Completed ========= =========== ====== ========= 1 NULL NULL NULL 2 NULL NULL NULL 3 NULL NULL NULL 

I am trying to update BigTable with values ​​from the log. Please note that in the case of duplicate entries, such as LogID 1 and 2 (above), I only dial the last date (2012-09-01 00:05:33). The same is necessary for POB, since we are only interested in "POB in Stop 1".

There are millions of lines in the Journal for a large number of job numbers, however I need to get time only for those Jobs that are only in BigTable. The Ideal table (after all updates) will look like this:

  JobNumber Accepted_At POB_At Completed ========= =========== ====== ========= 1 2012-09-01 00:05:33 2012-09-01 05:01:55 2012-09-01 05:15:36 2 2012-09-01 05:00:53 2012-09-01 05:04:00 2012-09-01 05:20:45 3 2012-09-01 05:02:22 2012-09-01 05:05:25 NULL 

Please note that I am new to this field. Any help is appreciated. Thank you in advance. Relations

Answer 1

You can combine the MAX dates in the CTE and then attach it to the BigTable to update:

 ; WITH CTE AS ( SELECT g.JobNumber , Accepted_At = MAX(CASE WHEN LogType = 100 THEN LogTime END) , POB_At = MAX(CASE WHEN LogType = 110 AND [Description] LIKE '%Stop%1' THEN LogTime END) , Completed = MAX(CASE WHEN LogType = 200 THEN LogTime END) FROM [Log] g GROUP BY g.JobNumber, g.LogType ) UPDATE b SET Accepted_At = CTE.Accepted_At , POB_At = CTE.POB_At , Completed = CTE.Completed FROM CTE JOIN BigTable b ON b.JobNumber = CTE.JobNumber 

Answer 2

This will work since you are using mssql server 2008

 merge bigtable t1 using (select jobnumber, min(case when description like 'Accepted by%' then logtime end) Accepted_At, min(case when description like 'POB at%' then logtime end) POB_At, min(case when description like 'Completed' then logtime end) Completed from <table> group by jobnumber) t2 on t1.jobunmber = t2.jobnumber -- the 2 next lines are optional, but they could help in -- situations where jobnumber is not already in the bigtable when not matched then INSERT t1(JobNumber, Accepted_At, POB_At, Completed ) values (t2.JobNumber, t2.Accepted_At, t2.POB_At, t2.Completed) when matched then update set t1.Accepted_At = t2.Accepted_At, t1.POB_At = t2.POB_At t1.Completed = t2.Completed;