Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DateDiff one Date column of First Row and the other Date column of second row.


DateDiff one Date column of First Row and the other Date column of second row.

Author
Message
KtmGuy
KtmGuy
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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 IsDateDiffLess14
1 123 04/10/2013 04/12/2013 0
2 123 05/10/2013 05/11/2013 1
3 123 05/21/2013 05/25/2013 0
4 112 01/10/2013 01/14/2013 1
5 112 01/27/2013 01/28/2013 0
6 120 03/10/2013 03/12/2013 0


I really appreiciate for any kind of feedback in this regard.

Thanks,
milos.radivojevic
milos.radivojevic
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 Visits: 774
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 YourTable
ORDER 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!
KtmGuy
KtmGuy
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 39
What is LEAD(Date1) ??? 'LEAD' is not a recognized built-in function name.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
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)
milos.radivojevic
milos.radivojevic
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 Visits: 774
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 t1
ORDER BY RowID



___________________________
Do Not Optimize for Exceptions!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search