Solved

finding next previous date

Posted on 2013-01-23
9
286 Views
Last Modified: 2013-02-14
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
0
Comment
Question by:elucero
  • 4
  • 3
  • 2
9 Comments
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 325 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  )


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
 
LVL 92

Expert Comment

by:Patrick Matthews
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

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
 
LVL 26

Expert Comment

by:Chris Luttrell
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

by:elucero
ID: 38812890
Thanks, what if there are more than 3 colums like 8?
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 26

Expert Comment

by:Chris Luttrell
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

by:elucero
ID: 38814701
thanks, what if they are part of the key?
0
 
LVL 26

Expert Comment

by:Chris Luttrell
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 92

Expert Comment

by:Patrick Matthews
ID: 38816218
elucero,

Did you try my suggestion in http:#a38812378 ?

Patrick
0
 

Author Comment

by:elucero
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
)
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now