Manipulation of date ranges
in [Programming SQL]
|
Prev: Parameter Sniffing
Next: Best Practice Error Checking
From: Fred on 24 Apr 2010 02:40 Hello, We have to deal with date ranges (start_date, end_date). We wrote some basics table function that return intersection, union, exclusion having two table variable parameters. We also sometime need to clean such a table (so that the ranges won't intersect). It works but I guess it can be improved. Do you know some best practices for this kind of requirement ? -- Fred foleide(a)free.fr
From: Plamen Ratchev on 24 Apr 2010 12:15 An excellent reading on the topic is Developing Time-Oriented Database Applications in SQL by Richard Snodgrass: http://www.cs.arizona.edu/~rts/tdbbook.pdf -- Plamen Ratchev http://www.SQLStudio.com
From: --CELKO-- on 25 Apr 2010 00:18 Also look at: " Managing Time in Relational Databases How to Design, Update and Query Temporal Data" by Tom Johnston and Randall Weis http://www.elsevierdirect.com/product.jsp?isbn=9780123750419
From: Fred on 25 Apr 2010 03:15 Thanks Plamen and Joe, I had a quick look at Richard Snodgrass' book. I didn't buy Tom Johnston and Randall Weis book yet. Chapter 4 looks like what I did (It calls Periods what I was calling Ranges, and predicates a larger set of what I was calling operations). I didn't find yet something about applying the predicates to sets of periods. My solution needs a cursor. Do you think I could post it here so that you can tell me your advice ? (I have to translate and clean it first) -- Fred foleide(a)free.fr
From: Dan Guzman on 25 Apr 2010 10:58
> My solution needs a cursor. Do you think I could post it here so that you > can tell me your advice ? > (I have to translate and clean it first) Yes, I think code will help. Set-based processing can be definitely be used with temporal data. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "Fred" <foleide(a)free.fr.invalid> wrote in message news:ecBiZGF5KHA.620(a)TK2MSFTNGP02.phx.gbl... > Thanks Plamen and Joe, > > I had a quick look at Richard Snodgrass' book. I didn't buy Tom Johnston > and Randall Weis book yet. > Chapter 4 looks like what I did (It calls Periods what I was calling > Ranges, and predicates a larger set of what I was calling operations). > I didn't find yet something about applying the predicates to sets of > periods. > My solution needs a cursor. Do you think I could post it here so that you > can tell me your advice ? > (I have to translate and clean it first) > > -- > Fred > foleide(a)free.fr |