finding next previous date

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

SELECT a.materialid, a.ReceivedDt, a.unitid,nextreceiveddt
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
AND b.nextreceiveddt <= a.ReceivedDt
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
eluceroAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
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  )


SELECT a.materialid, a.ReceivedDt, a.unitid, a.nextreceiveddt 
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
AND b.nextreceiveddt <= a.ReceivedDt
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
)
SELECT C1.materialid, C1.ReceivedDt, C1.unitid, C1.nextreceiveddt, C2.ReceivedDt AS PreviousreceivedDt
FROM CTE C1
LEFT OUTER JOIN CTE C2 ON C1.materialid = C2.materialid AND C1.rn - 1 = C2.rn


DROP TABLE test1;

Open in new window

results look like this
Results
0
 
Patrick MatthewsCommented:
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

SELECT a.materialid, a.Receiveddt, a.unitid, MIN(b.Receiveddt) AS NextReceiveddt
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

SELECT a.materialid, a.Receiveddt, a.unitid, MAX(b.Receiveddt) AS PreviousReceiveddt
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

SELECT a.materialid, a.Receiveddt, a.unitid, MAX(c.Receiveddt) AS PreviousReceiveddt,
    MIN(b.Receiveddt) AS NextReceiveddt
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

Open in new window

0
 
Chris LuttrellSenior Database ArchitectCommented:
oops, in my code above the first select was what you had to start, not part of the solution, just the CTE.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
eluceroAuthor Commented:
Thanks, what if there are more than 3 colums like 8?
0
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
eluceroAuthor Commented:
thanks, what if they are part of the key?
0
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
Patrick MatthewsCommented:
elucero,

Did you try my suggestion in http:#a38812378 ?

Patrick
0
 
eluceroAuthor Commented:
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
)
SELECT C1.materialid, C1.ReceivedDt, C1.unitid, C1.nextreceiveddt, C2.ReceivedDt AS PreviousreceivedDt
FROM CTE C1
LEFT OUTER JOIN CTE C2 ON C1.materialid = C2.materialid AND C1.rn - 1 = C2.rn
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.