SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need help combining records and creating range
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

calla
Starting Member

2 Posts

Posted - 10/15/2012 :  11:55:43  Show Profile  Reply with Quote
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 = Y
FIELD_2 = ABC123
YEAR_RANGE = 2001-2003;2005;2007-2008;2010-2012

Sample Record 1.2:
FIELD_1 = Y
FIELD_2 = ABC123
YEAR_RANGE = 2004;2006;2009;2013-2017

Expected Results (one record)
YEAR_RANGE = 2003-2017
SCOPE = Y
FULL = ABC123

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.

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 = Y
FIELD_2 = ABC123
YEAR_RANGE = 2001-2003;2005;2007-2008;2010-2011

Sample Record 2.2:
FIELD_1 = Y
FIELD_2 = ABC123
YEAR_RANGE = 2004;2006;2009;2013-2017

Expected Results (one record)
YEAR_RANGE = 2003-2011;2013-2017
SCOPE = Y
FULL = ABC123

It 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 = Y
FIELD_2 = ABC123
YEAR_RANGE = 2001-2003;2005;2007-2008;2010-2011

Sample Record 3.2:FIELD_1 = Y
FIELD_2 = ABC123
YEAR_RANGE = 2004;2006;2009;2013-2017;2019

Expected Results (one record)YEAR_RANGE = 2003-2011;2012-2017;2019
SCOPE = Y
FULL = ABC123

Again, any help would be very much appreciated. Please let me know if I need to clarify the problem description. -Calla

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/15/2012 :  14:39:17  Show Profile  Reply with Quote
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.

Go to Top of Page

calla
Starting Member

2 Posts

Posted - 10/15/2012 :  15:33:18  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/15/2012 :  22:41:42  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000