Retrieve Dates (and other data) From Different Rows

Question

Post reply

Retrieve Dates (and other data) From Different Rows

wenger.noah

Mr or Mrs. 500

Points: 589

January 19, 2017 at 5:36 am

#317789

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!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please don't post multiple threads for the same question.
No replies here. Replies to https://www.sqlservercentral.com/Forums/1852028/Retrieve-Dates-from-Different-Rows

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003888 More actions · Answer 2

I recommend you post your data according to the methods depicted at the first link in my signature line below under "Helpful Links". People will produce a coded response for you that has actually been tested.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)