zipaway
September 17, 2015, 1:27pm
1
Hi, I have an SQL query that has stumped me.
I have 2 tables, Projects and Tasks.
Projects
project_id
original_proj_id
Tasks
project_id
task_id
finish_date
Each project has an original project ID with original tasks. What select query would produce the following output, with the original_finish_date coming from the the original project id task with the same task_id?
Output
proj_id
task_id
finish_date
original_finish_date
Example with data
Dohsan
September 17, 2015, 1:31pm
2
This isn't too clear what you're trying to achieve. Would you provide some actual data in your worked example?
Dohsan
September 17, 2015, 2:10pm
4
This does the job i think
DECLARE @Projects TABLE
(
project_id INT,
original_proj_id int
);
INSERT INTO @Projects
VALUES (101,NULL),(102,101);
DECLARE @Tasks TABLE
(
project_id INT,
task_id int,
finish_date datetime
);
INSERT INTO @Tasks
VALUES (101,1,'20150910'),
(101,2,'20150911'),
(101,3,'20150912'),
(102,1,'20150910'),
(102,2,'20150912'),
(102,3,'20150914'),
(102,4,'20150915');
SELECT T.project_id,
T.task_id,
T.finish_date,
orig_finish_date = T1.finish_date
FROM @Tasks AS T
INNER
JOIN @Projects AS P
ON T.project_id = P.project_id
LEFT
JOIN @Tasks AS T1
ON T1.project_id = P.original_proj_id
AND T1.task_id = T.task_id;
1 Like