ORACLE QUERY QUESTION
Hello
Have the following 3 tables :
Table One : Jobs
Job No. Job date
1 10/09/2015
2 11/09/2015
Table Two : Progress
Job No. Progress_code Progress Date
1 1 10/09/2015
1 2 15/09/2015
1 3 16/09/2015
2 1 11/09/2015
2 2 11/09/2015
2 3 12/09/2015
Table Three : Pro_progress
1 Receive
2 Repair
3 Test
Required Query result :
Job No. Job Date 1-Receive Days Between Repair and Receive 2-Repair days betw test/repair 3-Test
1 10/09/2015 10/09/2015 5 15/09/2015 1 16/09/2015
2 11/09/2015 11/09/2015 0 11/09/2015 1 12/09/2015
Have the following 3 tables :
Table One : Jobs
Job No. Job date
1 10/09/2015
2 11/09/2015
Table Two : Progress
Job No. Progress_code Progress Date
1 1 10/09/2015
1 2 15/09/2015
1 3 16/09/2015
2 1 11/09/2015
2 2 11/09/2015
2 3 12/09/2015
Table Three : Pro_progress
1 Receive
2 Repair
3 Test
Required Query result :
Job No. Job Date 1-Receive Days Between Repair and Receive 2-Repair days betw test/repair 3-Test
1 10/09/2015 10/09/2015 5 15/09/2015 1 16/09/2015
2 11/09/2015 11/09/2015 0 11/09/2015 1 12/09/2015
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This gave me your expected result:
SELECT a.job_no,
a.job_date,
b.progress_date "1-Receive",
c.progress_date - b.progress_date "days betw repair/receive",
c.progress_date "2-Repair",
d.progress_date - c.progress_date "days betw test/repair",
d.progress_date "3-Test"
FROM jobs a
left outer join (SELECT e.job_no,
e.progress_date
FROM progress e
join pro_progress f
ON e.progress_code = f.progress_code
WHERE f.progress_desc = 'Receive') b
ON a.job_no = b.job_no
left outer join (SELECT g.job_no,
g.progress_date
FROM progress g
join pro_progress h
ON g.progress_code = h.progress_code
WHERE h.progress_desc = 'Repair') c
ON a.job_no = c.job_no
left outer join (SELECT i.job_no,
i.progress_date
FROM progress i
join pro_progress j
ON i.progress_code = j.progress_code
WHERE j.progress_desc = 'Test') d
ON a.job_no = d.job_no;
I think you're trying to get a SQL statement what will change the columns based on the data.
SQL can't do that.
You can however generate the results your requested by specifying the pro_progress yourself within the sql
SELECT job_no,
job_date,
receipt_date,
repair_date - receipt_date days_betw_repair_and_recei
repair_date,
test_date - repair_date days_betw_test_and_repair,
test_date
FROM ( SELECT j.job_no,
j.job_date,
MAX(CASE WHEN pp.name = 'Receive' THEN p.progress_date END) receipt_date,
MAX(CASE WHEN pp.name = 'Repair' THEN p.progress_date END) repair_date,
MAX(CASE WHEN pp.name = 'Test' THEN p.progress_date END) test_date
FROM jobs j, progress p, pro_progress pp
WHERE j.job_no = p.job_no AND pp.progress_code = p.progress_code
GROUP BY j.job_no, j.job_date);
or same idea, using PIVOT syntax
SELECT job_no,
job_date,
receipt_date,
repair_date - receipt_date days_betw_repair_and_recei
repair_date,
test_date - repair_date days_betw_test_and_repair,
test_date
FROM (SELECT j.job_no,
j.job_date,
p.progress_date,
pp.name
FROM jobs j, progress p, pro_progress pp
WHERE j.job_no = p.job_no AND pp.progress_code = p.progress_code)
PIVOT
(MAX(progress_date) FOR name IN ('Receive' receipt_date, 'Repair' repair_date, 'Test' test_date));
note the MAX isn't important, you need to aggregate the results of the multiple rows into a single row, you could use MIN just as well
SQL can't do that.
You can however generate the results your requested by specifying the pro_progress yourself within the sql
SELECT job_no,
job_date,
receipt_date,
repair_date - receipt_date days_betw_repair_and_recei
repair_date,
test_date - repair_date days_betw_test_and_repair,
test_date
FROM ( SELECT j.job_no,
j.job_date,
MAX(CASE WHEN pp.name = 'Receive' THEN p.progress_date END) receipt_date,
MAX(CASE WHEN pp.name = 'Repair' THEN p.progress_date END) repair_date,
MAX(CASE WHEN pp.name = 'Test' THEN p.progress_date END) test_date
FROM jobs j, progress p, pro_progress pp
WHERE j.job_no = p.job_no AND pp.progress_code = p.progress_code
GROUP BY j.job_no, j.job_date);
or same idea, using PIVOT syntax
SELECT job_no,
job_date,
receipt_date,
repair_date - receipt_date days_betw_repair_and_recei
repair_date,
test_date - repair_date days_betw_test_and_repair,
test_date
FROM (SELECT j.job_no,
j.job_date,
p.progress_date,
pp.name
FROM jobs j, progress p, pro_progress pp
WHERE j.job_no = p.job_no AND pp.progress_code = p.progress_code)
PIVOT
(MAX(progress_date) FOR name IN ('Receive' receipt_date, 'Repair' repair_date, 'Test' test_date));
note the MAX isn't important, you need to aggregate the results of the multiple rows into a single row, you could use MIN just as well
mmm, becuase I have just posted a sample for Pro_progress, it has more than Receive, Repair,Test....other 20 Records
so Need something Dynamic
so Need something Dynamic
>>> so Need something Dynamic
you can't.
As noted above - SQL doesn't work that way.
If you can use pl/sql and generate a report then ok. If you're looking for a sql statement then you'll have to specify the 20 fields
you can't.
As noted above - SQL doesn't work that way.
If you can use pl/sql and generate a report then ok. If you're looking for a sql statement then you'll have to specify the 20 fields
Is it truly dynamic? Could there really be a new status added to the tables without some sort of other significant code change? It would seem to me that while the number is large, it should be fairly static. A lengthy query to write, but certainly possible. What has been posted already is your framework, just keep adding more status codes to it.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial