Lynn Pettis (3/14/2013)
Sean Lange (3/14/2013)
I haven't seen all the other threads but from what you posted I can't figure out why you are using DATEDIFF at all. It doesn't meet your requirements and it will make your query nonSARGable.I think that something like this should meet your requirements. Of course as Lynn stated this doesn't accomodate NULL values in any of those columns.
[WorkForce_JobPosting].[Job Posting End Date_JP] < [WorkForce_WorkOrder].[Work Order End Date_WO]
AND
[WorkForce_WorkOrder].[Previous Work Order End Date_WO] <= [WorkForce_WorkOrder].[Work Order End Date_WO]
I gave him basically the same answer in another thread. He didn't like the answer because one or the other of the columns can be null. With that said, DATEDIFF suffers the same problem if a column is null.
Having seen your post I went and found the other thread(s). I was laughed that we both posted code that was so nearly identical you can't tell the difference.
Not only does the DATEDIFF version not handle NULLS and render indexes useless, the most important flaw is that it doesn't actually meet the requirements if the dates are on the same day. In other words using datediff we could have [Job Posting End Date_JP] being greater than [Work Order End Date_WO] but the datediff logic would say it is ok.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/