January 19, 2017 at 5:35 am
Hi,
I need to retrieve dates (and other fields) from different rows. How do I accomplish this?
Data looks like:
| plan_num | call_num | package_num | completed_date | next_scheduled_date | job_id | |||||
| 123456 | 1 | 1 | 20130112 | 20151231 | 1000000 | |||||
| 123456 | 2 | 1 | 20140224 | 20160317 | 1000000 | |||||
| 123456 | 3 | 1 | 20141213 | 20170202 | 1000000 | |||||
| 123456 | 4 | 1 | 20150917 | 20171121 | 1000000 | |||||
| 123456 | 5 | 1 | 00000000 | 20181121 | 1000000 | |||||
| 789012 | 1 | 1 | 20150713 | 20151229 | 1000000 | |||||
| 789012 | 2 | 1 | 20160821 | 20160708 | 1000000 | |||||
| 789012 | 3 | 1 | 00000000 | 20170824 | 1000000 |
The user will type in the job_id. I need to return plan_num, package_num, the most recent completed date and the scheduled date from the row following the most recent completed date. So, in this case, the user will type in job_id 1000000 and I need to return all of the following:
123456 1 20150917 20181121
789012 1 20160821 20170824
Thank you!
January 19, 2017 at 5:48 am
wenger.noah - Thursday, January 19, 2017 5:35 AMHi,I need to retrieve dates (and other fields) from different rows. How do I accomplish this?
Data looks like:
plan_num call_num package_num completed_date next_scheduled_date job_id 123456 1 1 20130112 20151231 1000000 123456 2 1 20140224 20160317 1000000 123456 3 1 20141213 20170202 1000000 123456 4 1 20150917 20171121 1000000 123456 5 1 00000000 20181121 1000000 789012 1 1 20150713 20151229 1000000 789012 2 1 20160821 20160708 1000000 789012 3 1 00000000 20170824 1000000 The user will type in the job_id. I need to return plan_num, package_num, the most recent completed date and the scheduled date from the row following the most recent completed date. So, in this case, the user will type in job_id 1000000 and I need to return all of the following:
123456 1 20150917 20181121
789012 1 20160821 20170824Thank you!
You can use the LAG and LEAD windowing functions.
John
January 19, 2017 at 5:49 am
wenger.noah - Thursday, January 19, 2017 5:35 AMHi,I need to retrieve dates (and other fields) from different rows. How do I accomplish this?
Data looks like:
plan_num call_num package_num completed_date next_scheduled_date job_id 123456 1 1 20130112 20151231 1000000 123456 2 1 20140224 20160317 1000000 123456 3 1 20141213 20170202 1000000 123456 4 1 20150917 20171121 1000000 123456 5 1 00000000 20181121 1000000 789012 1 1 20150713 20151229 1000000 789012 2 1 20160821 20160708 1000000 789012 3 1 00000000 20170824 1000000 The user will type in the job_id. I need to return plan_num, package_num, the most recent completed date and the scheduled date from the row following the most recent completed date. So, in this case, the user will type in job_id 1000000 and I need to return all of the following:
123456 1 20150917 20181121
789012 1 20160821 20170824Thank you!
It looks obvious, but you did not mention it: do the returned results need to contain one row per discrete plan_num, for the selected job_id?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 19, 2017 at 7:13 am
Phil Parkin - Thursday, January 19, 2017 5:49 AMwenger.noah - Thursday, January 19, 2017 5:35 AMHi,I need to retrieve dates (and other fields) from different rows. How do I accomplish this?
Data looks like:
plan_num call_num package_num completed_date next_scheduled_date job_id 123456 1 1 20130112 20151231 1000000 123456 2 1 20140224 20160317 1000000 123456 3 1 20141213 20170202 1000000 123456 4 1 20150917 20171121 1000000 123456 5 1 00000000 20181121 1000000 789012 1 1 20150713 20151229 1000000 789012 2 1 20160821 20160708 1000000 789012 3 1 00000000 20170824 1000000 The user will type in the job_id. I need to return plan_num, package_num, the most recent completed date and the scheduled date from the row following the most recent completed date. So, in this case, the user will type in job_id 1000000 and I need to return all of the following:
123456 1 20150917 20181121
789012 1 20160821 20170824Thank you!
It looks obvious, but you did not mention it: do the returned results need to contain one row per discrete plan_num, for the selected job_id?
yes - one row per discrete plan_num for selected job_id
January 19, 2017 at 8:28 am
wenger.noah - Thursday, January 19, 2017 7:13 AMPhil Parkin - Thursday, January 19, 2017 5:49 AMwenger.noah - Thursday, January 19, 2017 5:35 AMHi,I need to retrieve dates (and other fields) from different rows. How do I accomplish this?
Data looks like:
plan_num call_num package_num completed_date next_scheduled_date job_id 123456 1 1 20130112 20151231 1000000 123456 2 1 20140224 20160317 1000000 123456 3 1 20141213 20170202 1000000 123456 4 1 20150917 20171121 1000000 123456 5 1 00000000 20181121 1000000 789012 1 1 20150713 20151229 1000000 789012 2 1 20160821 20160708 1000000 789012 3 1 00000000 20170824 1000000 The user will type in the job_id. I need to return plan_num, package_num, the most recent completed date and the scheduled date from the row following the most recent completed date. So, in this case, the user will type in job_id 1000000 and I need to return all of the following:
123456 1 20150917 20181121
789012 1 20160821 20170824Thank you!
It looks obvious, but you did not mention it: do the returned results need to contain one row per discrete plan_num, for the selected job_id?
yes - one row per discrete plan_num for selected job_id
I figured it out, but if you want to post your solution, it might help be QA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply