Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# finding next previous date

Posted on 2013-01-23
Medium Priority
316 Views
Hi, I'm trying to find the previous date from the most current date. This is what I want

materialid      Receiveddt      unitid      nextrecevieddt
500130      4/12/2012      101896      6/30/2012
500130      6/30/2012      101126      8/31/2012
500130      8/31/2012      105261

This is what I have so far

FROM test1 AS a
LEFT JOIN (SELECT materialid, MIN(ReceivedDt) AS nextreceiveddt
FROM test1 AS a
WHERE materialid = '500130'
GROUP BY vin) b
on b.materialid = a.materialid
WHERE a.vin = '500130'

materialid      Receiveddt      unitid      nextrecevieddt
500130      4/12/2012      101896      4/12/2012
500130      6/30/2012      101126      4/12/2012
500130      8/31/2012      105261      4/12/2012
0
Question by:elucero
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 4
• 3
• 2

LVL 27

Accepted Solution

Chris Luttrell earned 1300 total points
ID: 38812368
Maybe something like this:
``````CREATE TABLE test1 ( materialid INT , ReceivedDt DATE, unitid INT ,nextreceiveddt DATE);
INSERT INTO test1 VALUES
(  500130,      '4/12/2012',      101896,      '6/30/2012'),
(  500130,      '6/30/2012',      101126,      '8/31/2012'),
(  500130,      '8/31/2012',      105261, NULL  )

FROM test1 AS a
LEFT JOIN (SELECT materialid, MIN(ReceivedDt) AS nextreceiveddt
FROM test1 AS a
WHERE materialid = '500130'
GROUP BY materialid) b
on b.materialid = a.materialid
WHERE a.materialid = '500130'

;WITH CTE AS (
SELECT a.materialid, a.ReceivedDt, a.unitid, a.nextreceiveddt , ROW_NUMBER() OVER (PARTITION BY materialid ORDER BY receivedDt) rn
FROM test1 a
)
FROM CTE C1
LEFT OUTER JOIN CTE C2 ON C1.materialid = C2.materialid AND C1.rn - 1 = C2.rn

DROP TABLE test1;
``````
results look like this
0

LVL 93

Expert Comment

ID: 38812378
Assuming you only have those first three columns in your table...

``````CREATE TABLE test1 ( materialid INT , ReceivedDt DATE, unitid INT);
INSERT INTO test1 VALUES
(  500130,      '4/12/2012',      101896),
(  500130,      '6/30/2012',      101126),
(  500130,      '8/31/2012',      105261)

--get next date

FROM test1 a LEFT JOIN
test1 b ON a.materialid = b.materialid AND a.Receiveddt < b.Receiveddt
GROUP BY a.materialid, a.Receiveddt, a.unitid
ORDER BY a.materialid, a.Receiveddt, a.unitid

-- get previous date

FROM test1 a LEFT JOIN
test1 b ON a.materialid = b.materialid AND a.Receiveddt > b.Receiveddt
GROUP BY a.materialid, a.Receiveddt, a.unitid
ORDER BY a.materialid, a.Receiveddt, a.unitid

-- get previous date and next date

FROM test1 a LEFT JOIN
test1 b ON a.materialid = b.materialid AND a.Receiveddt < b.Receiveddt LEFT JOIN
test1 c ON a.materialid = c.materialid AND a.Receiveddt > c.Receiveddt
GROUP BY a.materialid, a.Receiveddt, a.unitid
ORDER BY a.materialid, a.Receiveddt, a.unitid

DROP TABLE test1
``````
0

LVL 27

Expert Comment

ID: 38812478
oops, in my code above the first select was what you had to start, not part of the solution, just the CTE.
0

Author Comment

ID: 38812890
Thanks, what if there are more than 3 colums like 8?
0

LVL 27

Expert Comment

ID: 38813046
The number of columns should not matter in the CTE example I gave. As long as they are not part of the key between rows.
0

Author Comment

ID: 38814701
thanks, what if they are part of the key?
0

LVL 27

Expert Comment

ID: 38814715
then we just need to work them into the JOIN like I did with "ON C1.materialid = C2.materialid."  Just add them something like:
ON C1.materialid = C2.materialid AND C1.KeyColumn2 = C2.KeyColumn2 -- etc
0

LVL 93

Expert Comment

ID: 38816218
elucero,

Did you try my suggestion in http:#a38812378 ?

Patrick
0

Author Comment

ID: 38833375
Hi, thanks this is what I'm looking for, but how can I make it faster. The data is being inserted into a table variable. This is what's making it so slow C1.rn - 1 = C2.rn
Thanks

;WITH CTE AS (
SELECT a.materialid, a.ReceivedDt, a.unitid, a.nextreceiveddt , ROW_NUMBER() OVER (PARTITION BY materialid ORDER BY receivedDt) rn
FROM test1 a
)
FROM CTE C1
LEFT OUTER JOIN CTE C2 ON C1.materialid = C2.materialid AND C1.rn - 1 = C2.rn
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article Iâ€™m going to show you another new feature of SSRS 2008 R2, this time in the visâ€¦
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installatiâ€¦
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a qâ€¦
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastrâ€¦
###### Suggested Courses
Course of the Month10 days, 1 hour left to enroll

#### 722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.