Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
calla
Starting Member
2 Posts |
 Posted - 2012-10-15 : 11:55:43
|
| All-I have a situation where I want to combine values in the YEAR_RANGE field for records with the same values in FIELD_1 and FIELD_2. Example 1:Sample Record 1.1:FIELD_1 = YFIELD_2 = ABC123YEAR_RANGE = 2001-2003;2005;2007-2008;2010-2012Sample Record 1.2:FIELD_1 = YFIELD_2 = ABC123YEAR_RANGE = 2004;2006;2009;2013-2017Expected Results (one record)YEAR_RANGE = 2003-2017SCOPE = YFULL = ABC123I am thinking I need to do this in two parts: First, denormalize each sample record into multiple records by individual YEAR. Secondly, combine records with the same values in FIELD_1 and FIELD_2 and aggregate the years to create a new range.Do I need to first denormalize each of the records somehow and then aggregate them together.Any help would be appreciated!I am listing two more examples, as the end result may not be a clean range of years and I need to account for that. Therefore, I am hoping that I can run the same query (or set of queries) against all records to achieve desired results.Additional Examples:It is also possible that the resulting year range value may not be as "clean" as a single range of values. In this case, I changed the YEAR_RANGE of the first sample record so that 2012 is not present in the YEAR_RANGE FOR either record.Example 2:Sample Record 2.1:FIELD_1 = YFIELD_2 = ABC123YEAR_RANGE = 2001-2003;2005;2007-2008;2010-2011Sample Record 2.2:FIELD_1 = YFIELD_2 = ABC123YEAR_RANGE = 2004;2006;2009;2013-2017Expected Results (one record)YEAR_RANGE = 2003-2011;2013-2017SCOPE = YFULL = ABC123It is also possible that the resulting year range value may not be as "clean" as a single range of values. As in Example 2, 2012 is still not present in the YEAR_RANGE FOR either record. In this example, I added 2019 to the second sample record.Example 3:Sample Record 3.1:FIELD_1 = YFIELD_2 = ABC123YEAR_RANGE = 2001-2003;2005;2007-2008;2010-2011Sample Record 3.2:FIELD_1 = YFIELD_2 = ABC123YEAR_RANGE = 2004;2006;2009;2013-2017;2019Expected Results (one record)YEAR_RANGE = 2003-2011;2012-2017;2019SCOPE = YFULL = ABC123Again, any help would be very much appreciated. Please let me know if I need to clarify the problem description. -Calla |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-15 : 14:39:17
|
quote:
I am thinking I need to do this in two parts: First, denormalize each sample record into multiple records by individual YEAR. Secondly, combine records with the same values in FIELD_1 and FIELD_2 and aggregate the years to create a new range.Do I need to first denormalize each of the records somehow and then aggregate them together.
Your thought process is correct. In order to be able to do this in SQL, you have to normalize the years. (I refer to it as NORMALIZING rather than DENORMALIZING :)Post the code that you have for normalizing it (even if it is not working exactly the way you want it to). Once we normalize and expand it, we can figure out a way to find the ranges that they span. Once you have the ranges, if you really really want to, we can then de-normalize it.It still beats me why someone would store the information on the years in such a denormalized and mangled form into something as beautiful and elegant as a SQL server which would let you store the data in a normalized form enabling you to calculate any sort of information you want easily. |
 |
|
|
calla
Starting Member
2 Posts |
Posted - 2012-10-15 : 15:33:18
|
| Thank you so much for the response. (Normalizing, Denormalizing...I obviously hadn't had my coffee yet!)I don't yet have the code to normalize it...I have to try a few things and/or look for examples for that. I will respond when I have some samples.I agree with you that the format does not make sense. Part of this exercise is an attempt to improve that...so maybe the denormalizing won't be necessary. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-15 : 22:41:42
|
| best thing would be to hold values as individual records with StartYear and EndYear fields representing range rather than in Start-End format. then you can apply logic based on BETWEEN or >,< etc operators to find and collapse overlapping ranges------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Need help combining records and creating range