Compare dates between 2 different rows and columns
-
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
-
This looks like a Job for the LEAD/LAG functions
something like this
WITH CTE_LEAD
AS
(
SELECT
RowId
,Customer_ID
,PartId
,EffectiveDate
,DISCONTINUE_DATE
,ISNULL
(LEAD(Effective_DATE,1)
OVER (PARTITION BY CustomerId,PartId ORDER BY EffectiveDATE)
,'2900-Jan-01'
) AS NextEFDate
FROM
aTABLE
)
SELECT *
FROM CTE_LEAD
WHERE DISCONTINUE_DATE>NextEFDATE
You could use Lag as well but that would get the previous date.
I don't have the raw data in a table but it should be give you an idea.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Thanks for the reply Jason! Unfortunately I get an error with the LEAD function that it isn't a recognized built-in function name. I had thought about that too but couldn't get LEAD to work
-
my first guess, based on what you posted;
the second query is assuming you want to compare just the "previous" row, whcih would be based on the effectivedate.
SELECT * FROM MyTable T1
INNER JOIN MyTable T2
ON T1.CustomerId = T2.CostmerID
AND T1.ROWID < T2.ROWID
WHERE T1.Discontinue_date > T2.Effective_Date
;WITH MyCTE
AS
(
SELECT row_number() over(partition by customerid order by effectivedate) As RW,*
FROM MyTable
)
SELECT * FROM MyCTE T1
INNER JOIN MyCTE T2
ON T1.CustomerId = T2.CostmerID
AND T1.ROWID +1 = T2.ROWID
WHERE T1.Discontinue_date > T2.Effective_Date
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks Lowell I will try that! I think I have the topic in the wrong spot, the database is a 2008r2 database, I put my question in 2012 T-SQL. I will fix that!
-
matzj (2/18/2015)
Thanks for the reply Jason! Unfortunately I get an error with the LEAD function that it isn't a recognized built-in function name. I had thought about that too but couldn't get LEAD to workSorry, with it being in the SQL 2012 forum I assumed that these functions where available, Lowell's solution does the same as the LEAD/LAG function, just uses join instead of the new functions.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Hey Lowell, I think that 2nd query you gave me will work, but how to do I exclude the first row? The first row will always have a discontinue date that is greater than the effective date so I'd like to somehow exclude that row
-
I've just noticed that you will probably have to include the PartID in Lowells joins and in the ROW_NUMBER() window function.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Like this?:
;WITH MyCTE
AS
(
SELECT row_number() over(partition by customer_id, part_id order by effective_date) As RW,*
FROM CUST_PRICE_EFFECT
)
SELECT * FROM MyCTE T1
INNER JOIN MyCTE T2
ON T1.Customer_Id = T2.CUSTOMER_ID
AND T1.PART_ID = T2.PART_ID
AND T1.ROWID +1 = T2.ROWID
WHERE T1.Discontinue_date > T2.Effective_Date
-
Deleting a rather dumb idea, hopefully before anyone saw it.:-D
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Forgot to mention. You might want to take a look at this article.
Creating a Date Range from Multiple Rows Based on a Single Date[/url]
It covers what I'd prefer as the correct structuring of data such as this, to avoid the issues you are now finding yourself faced with.
I realize that you may not be at liberty to change at this time.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Hey everyone thanks again for all the responses! I was able to get this working late yesterday and wanted to share my code. here is what I did:
SELECT
ROWID,
CUSTOMER_ID,
PART_ID,
EFFECTIVE_DATE,
DISCONTINUE_DATE
FROM CUST_PRICE_EFFECT
WHERE DISCONTINUE_DATE > (SELECT TOP 1 EFFECTIVE_DATE
FROM CUST_PRICE_EFFECT AS sub
WHERE sub.CUSTOMER_ID = CUST_PRICE_EFFECT.CUSTOMER_ID AND sub.PART_ID = CUST_PRICE_EFFECT.PART_ID
AND sub.EFFECTIVE_DATE > CUST_PRICE_EFFECT.EFFECTIVE_DATE
ORDER BY EFFECTIVE_DATE)
ORDER BY CUST_PRICE_EFFECT.PART_ID
-
matzj (2/19/2015)
Hey everyone thanks again for all the responses! I was able to get this working late yesterday and wanted to share my code. here is what I did:SELECT
ROWID,
CUSTOMER_ID,
PART_ID,
EFFECTIVE_DATE,
DISCONTINUE_DATE
FROM CUST_PRICE_EFFECT
WHERE DISCONTINUE_DATE > (SELECT TOP 1 EFFECTIVE_DATE
FROM CUST_PRICE_EFFECT AS sub
WHERE sub.CUSTOMER_ID = CUST_PRICE_EFFECT.CUSTOMER_ID AND sub.PART_ID = CUST_PRICE_EFFECT.PART_ID
AND sub.EFFECTIVE_DATE > CUST_PRICE_EFFECT.EFFECTIVE_DATE
ORDER BY EFFECTIVE_DATE)
ORDER BY CUST_PRICE_EFFECT.PART_ID
Thanks for posting that. You'd be surprised at how many people come to this forum seeking help, get it and then never get back to us letting us know what worked for them.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Assuming that you've fixed your data anomalies by now, you might want to take a look at this article:
Self-maintaining, Contiguous Effective Dates in Temporal Tables[/url]
It gives you a way to add some T-SQL code and constraints to your application to avoid such anomalies from occurring again in the future.
After I saw this, I started looking into the problem a little more (it is not an uncommon problem) and that article is the culmination of what I came up with.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
And then of course there is this article to help you make informed choices about what approach is the best from a performance perspective for identifying your date anomalies:
Identifying Start Dates not Aligned with End Dates on a Prior Row[/url]
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply