SQL to merge child records by combining parent record

Question

First, let me apologize if this makes no sense. I took the time to write it and understand it myself. I apologize if something is embarrassing. I am new to this and try to explain. I also apologize for the qustion title if it does not represent. If someone knows the best headline, edit or make the necessary changes.

I have two tables JOBS and PHASES.

JOBS Id Job_Type (ex. 0 = import) Title PHASES Id Jobs_Id Phase_Type Title Start_Time End_Time Duration 

Each type of job / job _type can have any number of phases - each phase has a start, end and duration time

I want to select the jobs_id, start_time, end_time and duration fields from the PHASES table and add all my Durations for each job_type parameter (example: job_type = 0, which is an import job)

 SELECT jobs_id, start_time, end_time, duration FROM phases JOIN jobs ON phase.jobs_id = jobs.id WHERE jobs.job_type=0 

return jobs_id return start time as start time return last time at end return total duration of all TotalDuration

Example if the following data was in the JOBS table (Id, Job_Type, Title)

 1, 0, Import 

and the following data was in the PHASES table (id, job_id, phase_type, title, start, end, duration)

 1, 1, 1, 0, Run Preprocessor, 10/18/2012 8:52 PM, 10/18/2012 9:00 PM, 00:08:00 2, 1, 2, 1, Massage Data, 10/18/2012 9:00 PM, 10/18/2012 9:05 PM, 00:05:00 3, 1, 3, 2, Run Postprocessor, 10/18/2012 9:05 PM, 10/18/2012 9:07 PM, 00:02:00 4, 2, 1, 0, Run Preprocessor, 10/18/2012 9:15 PM, 10/18/2012 9:20 PM, 00:05:00 5, 2, 2, 1, Massage Data, 10/18/2012 9:20 PM, 10/18/2012 9:25 PM, 00:05:00 6, 2, 3, 2, Run Postprocessor, 10/18/2012 9:30 PM, 10/18/2012 9:35 PM, 00:05:00 

Above is the image (phase) of two jobs (job_type = 0)

I need an SQL statement that can combine phases and show the beginning and end of the list, and the total duration. I would expect this information to return (Job_id, Start, End, TotalDuration)

  1, 10/18/2012 8:52 PM, 10/18/2012 9:07 PM, 00:05:00 2, 10/18/2012 9:15 PM, 10/18/2012 9:35 PM, 00:15:00 

Answer 1

Assuming your total duration will not exceed 1 day, this will work. If it leaves in one day, an additional expression is required to exceed HH:MM:SS in the duration column.

 select job_id, min(start_time) start_time, max(end_time) end_time, CONVERT(CHAR(8), max(end_time)-min(start_time), 8) totalduration from phases group by job_id -- sample data -- create table phases (job_id int, start_time datetime, end_time datetime); insert phases values (1, '20121019 12:45', '20121019 13:15'); insert phases values (1, '20121019 13:15', '20121019 13:45'); insert phases values (1, '20121019 14:00', '20121019 15:12'); -- result -- job_id start_time end_time totalduration 1 2012-10-19 12:45:00.000 2012-10-19 15:12:00.000 02:27:00 

Answer 2

 DECLARE @jobs TABLE( jobid BIGINT NOT NULL ) INSERT INTO @jobs (jobid) VALUES (1) , (2) , (3) DECLARE @phases TABLE( phaseId BIGINT NOT NULL, jobId BIGINT NOT NULL, startTime datetime not null, endTime datetime not null ) INSERT INTO @phases (phaseId, jobId, startTime, endTime) VALUES (1,1, '10/18/2012 8:52 PM', ' 10/18/2012 9:00 PM') , (2, 1, '10/18/2012 9:00 PM', '10/18/2012 9:05 PM') , (3, 1, '10/18/2012 9:05 PM', '10/18/2012 9:07 PM') , (4, 2, '10/18/2012 9:15 PM', '10/18/2012 9:20 PM') , (5, 2, '10/18/2012 9:20 PM', '10/18/2012 9:25 PM') , (6, 2, '10/18/2012 9:30 PM', '10/18/2012 9:35 PM') SELECT j.jobid, MIN(p.startTime) as startTime , MAX(p.endTime) as endTime , CONVERT(TIME(0),MAX(p.endTime)-MIN(p.startTime)) as duration FROM @phases p JOIN @jobs j ON j.jobid = p.jobId GROUP BY j.jobid