asked on

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

ASKER CERTIFIED SOLUTION

Chris Luttrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

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

Chris Luttrell

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

elucero

ASKER

Thanks, what if there are more than 3 colums like 8?

Chris Luttrell

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.

elucero

ASKER

thanks, what if they are part of the key?

Chris Luttrell

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

Patrick Matthews

elucero,

Did you try my suggestion in http:#a38812378 ?

Patrick

elucero

ASKER

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