Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jimmatz
Starting Member
4 Posts |
 Posted - 2015-02-18 : 10:43:48
|
I'm having problems figuring this out. Here is an example table: What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that. Let me know if anyone has any ideas or if more clarification is needed, I've been struggling with how to get started on this!!Thanks very much in advance!JIM |
|
|
jimmatz
Starting Member
4 Posts |
Posted - 2015-02-18 : 10:44:40
|
| My image didn't seem to work, here is the link to the image http://imgur.com/LLGgOMe |
 |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-02-18 : 10:55:10
|
| Instead of posting (or linking to) pictures, just post some sample data here. It's easier to work withThe answer to the question is to use the LEAD windowing function. e.g.case when discontinue_date > LEAD(Effective_date) over(partition by customer_id, part_id order by effective_date) then do something end |
|
|
|
|
|
|
Compare dates between 2 different rows and columns