Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 DateDiff one Date column of First Row and the other Date column of second row. Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, January 29, 2014 9:53 PM
 Forum Newbie Group: General Forum Members Last Login: Monday, February 10, 2014 5:30 PM Points: 7, Visits: 39
 Is there a efficient way to compare two different columns of 2 different rows in a data set as shown below. For eg: I would like to DateDiff between Date2 of RowID 1 and Date1 of RowID 2 of IDNo 123. After this comparision , if datediff between two dates are <=14 then i want to update 1 else 0 in IsDateDiffLess14 of RowID1 . In below example its 0 because datediff of two dates >=14. So, want to compare the Date2 and Date1 in this sequence for the same IDNo. For RowID 6 there is only 1 row and no other row to compare, in this case IsDateDiffLess14 should be updated with 0. RowID IDNo Date1 Date2 IsDateDiffLess141 123 04/10/2013 04/12/2013 02 123 05/10/2013 05/11/2013 13 123 05/21/2013 05/25/2013 0 4 112 01/10/2013 01/14/2013 15 112 01/27/2013 01/28/2013 06 120 03/10/2013 03/12/2013 0I really appreiciate for any kind of feedback in this regard. Thanks,
Post #1536160
 Posted Thursday, January 30, 2014 2:13 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Today @ 1:23 PM Points: 128, Visits: 569
 Here is a piece of code which calculates if the flag IsDateDiffLess14_Calculation should be set or not.`SELECT RowID, CASE WHEN DATEDIFF(DAY, Date2, LEAD(Date1) OVER(PARTITION BY IDNo ORDER BY RowId)) < 14 THEN 1 ELSE 0 END IsDateDiffLess14_Calculation FROM YourTableORDER BY RowID`I am not sure if it's required to have this flag as persistent column in the table. If it is you should modify the script and use it whenever a new row is added to the table to update the previous row only and not the whole table. ___________________________Do Not Optimize for Exceptions!
Post #1536205
 Posted Thursday, January 30, 2014 11:04 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, February 10, 2014 5:30 PM Points: 7, Visits: 39
 What is LEAD(Date1) ??? 'LEAD' is not a recognized built-in function name.
Post #1536480
 Posted Thursday, January 30, 2014 12:15 PM
 SSChampion Group: General Forum Members Last Login: Today @ 3:31 PM Points: 11,949, Visits: 10,982
 KtmGuy (1/30/2014)What is LEAD(Date1) ??? 'LEAD' is not a recognized built-in function name.That is one of the analytical functions introduced in 2012. Since you posted in a 2012 forum it was assumed your were using 2012. What version of sql server are you using? _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1536511
 Posted Thursday, January 30, 2014 2:29 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Today @ 1:23 PM Points: 128, Visits: 569
 Yep, as Sean said I assumed that you use SQL 2012 and windows offset functions are a good choice in situations where you want to refer to some other row from your row.Anyway, if you cannot use SQL 2012 the following query is logically equivalent to the query I provided in previous post:`SELECT RowID, CASE WHEN DATEDIFF(DAY, Date2, ( SELECT TOP 1 Date1 FROM YourTable t2 WHERE t1.IDNo = t2.IDNo AND t1.RowId < t2.RowId ORDER BY t2.RowId ASC ) ) < 14 THEN 1 ELSE 0 END IsDateDiffLess14_Calculation FROM YourTable t1ORDER BY RowID` ___________________________Do Not Optimize for Exceptions!
Post #1536558

 Permissions

 Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.