Asked by:
Smart Year Ranges  Group By YEARS, Min/Max Combination
Question

Hi
I am trying to get a sql statement that turns the following raw data into something grouped (logically).
Sample Data:
Part Number Minimum Year Maximum Year Vehicle
123 2010 2014 Ford F150
234 2015 2019 Ford F150
456 2010 2019 Ford F150My desired Results:
Vehicle Year Range Part 1 Part 2
Ford F150 1014 123 456
Ford F150 1519 234 456Since 456 fits 2010  2019, it should show in both groups instead of creating its own unique group.
Any help is appreciated!
Thanks,
Jesse
Jesse A. Brandenburg
All replies

Hi Jesse,
Please try this:
IF OBJECT_ID('test') IS NOT NULL drop table test create table test ( Part_Number varchar(20),Minimum_Year int,Maximum_Year int,Vehicle varchar(20)) go insert into test values (123,2010,2014,'Ford F150'), (234,2015,2019,'Ford F150'), (456,2010,2019,'Ford F150') ;with cte as ( select t1.* from test t1 inner join( select min(Minimum_Year)as a,max(Maximum_Year)as b from test) t2 on t1.Minimum_Year=t2.a and t1.Maximum_Year=t2.b) //'cte' table choose the row whose duration is the largest using max and min function select b1.*, b2.part_number as part2 from ( select vehicle,right(Minimum_Year,2)+''+ right(Maximum_Year,2)as year_range, part_number as part1 from test a1 where part_number not in (select part_number from cte) ) b1 inner join cte b2 on b1.Vehicle=b2.Vehicle //then use 'inner join' to add part_number which is in the range /* vehicle year_range part1 part2     Ford F150 1014 123 456 Ford F150 1519 234 456 */
Regards,
Sabrina
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. 


Hi,
Can you post queries to create the table and insert sample data with more rows so it will be more clear what is the business rule you want to implement?
and please try to explain what is the expected result according to the sample data and how did you got each row from the source
Ronen Ariely
[Personal Site] [Blog] [Facebook] [Linkedin] 

Yes  sorry for the lack of clarity.
Here are the full details:
Create Table PartVehicleTestData (
vPartID varchar(20),
vMake varchar(50),
vModel varchar(50),
vYear int,
vPartType varchar(100)
)
Insert Into PartVehicleTestData
Values
('205530','Freightliner','Sprinter 2500',2010,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2008,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2011,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2012,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2007,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2009,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2013,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2010,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2008,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2011,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2012,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2007,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2007,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2009,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2013,'Tuff Guard Van Grille Guard'),
('205860','Freightliner','Sprinter 2500',2010,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 2500',2008,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 2500',2011,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 2500',2012,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 2500',2007,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 2500',2007,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 2500',2009,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 2500',2013,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 2500',2010,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 2500',2008,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 2500',2011,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 2500',2012,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 2500',2007,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 2500',2009,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 2500',2013,'Tuff Guard Brackets'),
('205530','Freightliner','Sprinter 2500',2015,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2017,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2016,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2014,'Tuff Guard Van Grille Guard'),
('205930','Freightliner','Sprinter 2500',2015,'Tuff Guard Brackets'),
('205930','Freightliner','Sprinter 2500',2017,'Tuff Guard Brackets'),
('205930','Freightliner','Sprinter 2500',2016,'Tuff Guard Brackets'),
('205930','Freightliner','Sprinter 2500',2014,'Tuff Guard Brackets'),
('205930','Freightliner','Sprinter 2500',2015,'Tuff Guard Brackets'),
('205930','Freightliner','Sprinter 2500',2017,'Tuff Guard Brackets'),
('205930','Freightliner','Sprinter 2500',2016,'Tuff Guard Brackets'),
('205930','Freightliner','Sprinter 2500',2014,'Tuff Guard Brackets'),
('205530','Freightliner','Sprinter 2500',2015,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2017,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2016,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 2500',2014,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2015,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2017,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2016,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2014,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2010,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2008,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2011,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2012,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2007,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2009,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2013,'Tuff Guard Van Grille Guard'),
('205930','Freightliner','Sprinter 3500',2015,'Tuff Guard Brackets'),
('205930','Freightliner','Sprinter 3500',2017,'Tuff Guard Brackets'),
('205930','Freightliner','Sprinter 3500',2016,'Tuff Guard Brackets'),
('205930','Freightliner','Sprinter 3500',2014,'Tuff Guard Brackets'),
('205930','Freightliner','Sprinter 3500',2015,'Tuff Guard Brackets'),
('205930','Freightliner','Sprinter 3500',2017,'Tuff Guard Brackets'),
('205930','Freightliner','Sprinter 3500',2016,'Tuff Guard Brackets'),
('205930','Freightliner','Sprinter 3500',2014,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 3500',2010,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 3500',2008,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 3500',2011,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 3500',2012,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 3500',2007,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 3500',2009,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 3500',2013,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 3500',2010,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 3500',2008,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 3500',2011,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 3500',2012,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 3500',2007,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 3500',2009,'Tuff Guard Brackets'),
('205860','Freightliner','Sprinter 3500',2013,'Tuff Guard Brackets'),
('205530','Freightliner','Sprinter 3500',2010,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2008,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2011,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2012,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2007,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2009,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2013,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2015,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2017,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2016,'Tuff Guard Van Grille Guard'),
('205530','Freightliner','Sprinter 3500',2014,'Tuff Guard Van Grille Guard')
The output I would like to have is :
Make Model Years Guard Bracket Freightliner Sprinter 2500 14  17 205530 205930 Freightliner Sprinter 2500 07  13 205530 205860 Freightliner Sprinter 3500 14  17 205530 205930 Freightliner Sprinter 3500 07  13 205530 205860
The output I am currently getting based on the data right now is (I do not want this):
Make Model Years Guard Bracket Freightliner Sprinter 2500 14  17  205930 Freightliner Sprinter 2500 07  17 205530  Freightliner Sprinter 2500 07  13  205860 Freightliner Sprinter 3500 14  17  205930 Freightliner Sprinter 3500 07  17 205530  Freightliner Sprinter 3500 07  13  205860 Jesse A. Brandenburg

Hi Jesse,
Is this what you want?
;with cte1 as ( select * from PartVehicleTestData pivot (max([vpartid])for[vparttype]in ([Tuff Guard Van Grille Guard],[Tuff Guard Brackets]))as pvt) select distinct vMake as Make,vModel as Model,case when vyear between 2014 and 2017 then '1417' when vyear between 2007 and 2013 then '0713' else null end as Years, [Tuff Guard Van Grille Guard] as Guard, [Tuff Guard Brackets]as Bracket from cte1 /* ake Model Years Guard Bracket      Freightliner Sprinter 2500 0713 205530 205860 Freightliner Sprinter 2500 1417 205530 205930 Freightliner Sprinter 3500 0713 205530 205860 Freightliner Sprinter 3500 1417 205530 205930 */
Sabrina
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. 
Hi,
Thanks for the information. It is much simpler to discuss now
Can you clarify why you split '0713' and '1417' and not any other ranges of years?
Is this a hard codes decision (meaning it is not related to the data but to specific ranges which you want to get)
For example, what if you had data with year 2001 or anything before 2007 or after 2017?!? should this data should be ignoredRonen Ariely
[Personal Site] [Blog] [Facebook] [Linkedin] 
Ideally, it would not be hard coded if possible. Id like it to be limited by the “bracket” fitment. Since the guard fits the full range (0717) id like it to be interpreted at a more granular level where applicable. Since the bracket is creating the year break, and the guard fits both year ranges  id like it to conform with the bracket fitments. That said, it shouldn’t if it doesn’t fall within the range. Jesse
Jesse A. Brandenburg

I am pretty sure that we can provide you with dynamic solution instead hardcoded ranges of dates if you can explain what is the logic to find the ranges.
Do you want dynamically use the range between the minimum value of the year and the maximum value?
In this case, which part of the range will be part 1 and which part two
Ronen Ariely
[Personal Site] [Blog] [Facebook] [Linkedin] Edited by pituachMVP, Moderator Wednesday, September 11, 2019 1:51 PM

Thank you for your continued support.
In the example above, the min/max should be dictated by the Tuff Guard Bracket part number. The Tuff Guard Van Grille guard should fall into alignment where it fits the same ranges.
The business logic here is 
We make brackets to fit different vehicles  they can change as the vehicles change, but the guard stays consistent throughout. Because of this, the brackets have broken up year ranges (1215, 1619) or (1117, 1819), etc. The guard will fit (1219, 1119, etc).
The goal is to produce a document that makes it easy for a consumer to find the right products for their particular year/make/model. By keeping things on one line in a result set, it would be the easiest for the consumer rather than looking up multiple lines because their year/make/model occurs multiple times  depending on whether they are looking for the bracket or the guard.
Thanks,
Jesse
Jesse A. Brandenburg

Thank you for your continued support.
In the example above, the min/max should be dictated by the Tuff Guard Bracket part number. The Tuff Guard Van Grille guard should fall into alignment where it fits the same ranges.
The business logic here is 
We make brackets to fit different vehicles  they can change as the vehicles change, but the guard stays consistent throughout. Because of this, the brackets have broken up year ranges (1215, 1619) or (1117, 1819), etc. The guard will fit (1219, 1119, etc).
The goal is to produce a document that makes it easy for a consumer to find the right products for their particular year/make/model. By keeping things on one line in a result set, it would be the easiest for the consumer rather than looking up multiple lines because their year/make/model occurs multiple times  depending on whether they are looking for the bracket or the guard.
Thanks,
Jesse
Jesse A. Brandenburg
Hi,
You are most welcome, but unfortunately I still don't get the same point I asked before
>> Because of this, the brackets have broken up year ranges (1215, 1619) or (1117, 1819), etc. The guard will fit (1219, 1119, etc).
Why do you use the ranges (1215, 1619) or (1117, 1819)? Why not different ranges
In the sample data you gave us we have years between 2007 to 2017
What is the rule to split it into smaller ranges?
Why ('0713' and '1417') and not ('0711' and '1217') for exampleWe need to have a clear rule on how do you select the ranges and I do not understand this rule
Getting the minimum value 07 in this case sample data is simple by taking the MIN value. Same with the MAX value. But why first range is until 13 and not until 11 or 12 or any other year.And does you need always to split into 2 ranges? Why not three ranges '0711' and '1214' and '1517'
My question was what is the rules for the ranges in the sample data for example (and in general)
Ronen Ariely
[Personal Site] [Blog] [Facebook] [Linkedin]
 Edited by pituachMVP, Moderator Wednesday, September 11, 2019 7:38 PM

Those examples are for example only  they could be any year range. The ranges themselves cannot be predicted due to vehicle adjustments. When the 2020 vehicles come out, they will all have new ranges.
The point in the examples was to show that brackets have shorter year ranges and the guards have longer year ranges that are dependent on what the brackets fit. The brackets are prerequisites for the guards. If the bracket doesn't fit, the guard cannot.
Thanks,
Jesse
Jesse A. Brandenburg

I probably did not explain myself well but I do not see how to explain it better...
I understand that the ranges are dynamic!
I do not understand what is the rules to choose what are the ranges according to the existing data. What is the rules to choose the ranges according to the data in the table?
How do you choose the ranges?!?
How did you chose in in the sample data which you provided?
Ronen Ariely
[Personal Site] [Blog] [Facebook] [Linkedin] Edited by pituachMVP, Moderator Wednesday, September 11, 2019 9:57 PM


The ranges should be prioritized by the parts that are categorized by "Tuff Guard Brackets". Whatever the fitment for the parts in that category should dictate what the ranges are.
Thanks,
Jesse
Jesse A. Brandenburg
OK, I am sorry but we go nowhere...
>> The ranges should be prioritized by the parts that are categorized by "Tuff Guard Brackets".
How?!?
How do we use the the parts that are categorized by "Tuff Guard Brackets" in order to get the ranges?!? what is the logic? what is the math to get the ranges out of these values?!?
I keep asking the same question for few days from the start of this thread and you keep going around the question and give no technical information at all. We go nowhere like this...
unless your next explanation is how to get the ranges from the existing data in the table , I will have to redrew from following the thread. I am sorry but I do not see any way to explain better
example of what you do 
let's assume that someone in the forum tell us that he have table with the values 1,2,3 and his expected result is 6. How do we know what is the math/logic to get the value 6 without explanation about the logic/math
the result 6 can be result of 1*2*3 or 1+2+3
If you do not explain how you get the result then someone can come with the idea that you sum all the values in the table, and it will fit you specific demo with the values 1,2,3
but when you will have 1,2,3,4 then the result will be 1+2+3+4=10
And maybe what you actually need is the result of 1*2*3*4=24 ?!?We have no idea what is the logic to get the ranges from the existing data and keep telling us that it came from 1,2,3 but HOW?!? WHAT IS THE LOGIC?!?
is it 1+2+3 or 1*2*3 ?!?
Ronen Ariely
[Personal Site] [Blog] [Facebook] [Linkedin] Edited by pituachMVP, Moderator Thursday, September 12, 2019 4:41 AM

Hi Jesse,
I'm not sure about your logic. But you can change the date range you want by yourself. Please try:
declare @time1 int=2014 /* you can change the year as you need */ declare @time2 int=2007 /* you can change the year as you need */ ;with cte1 as ( select * from PartVehicleTestData pivot (max([vpartid])for[vparttype]in ([Tuff Guard Van Grille Guard],[Tuff Guard Brackets]))as pvt) select distinct vMake as Make,vModel as Model,case when vyear between @time1 and @time1+3 then ''+ right(@time1,2)+''+ right(@time1+2,2)+'' when vyear between @time2 and @time2+6 then ''+ right(@time2,2)+''+ right(@time2+2,2)+'' else null end as Years, [Tuff Guard Van Grille Guard] as Guard, [Tuff Guard Brackets]as Bracket from cte1 /* Make Model Years Guard Bracket      Freightliner Sprinter 2500 0709 205530 205860 Freightliner Sprinter 2500 1416 205530 205930 Freightliner Sprinter 3500 0709 205530 205860 Freightliner Sprinter 3500 1416 205530 205930 */
Sabrina
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. Proposed as answer by pituachMVP, Moderator Friday, September 13, 2019 10:42 PM

Check this experimental script too:
declare @PartVehicleTestData as table ( vPartID varchar(20), vMake varchar(50), vModel varchar(50), vYear int, vPartType varchar(100) ) insert into @PartVehicleTestData values ('205530','Freightliner','Sprinter 2500',2010,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2008,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2011,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2012,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2007,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2009,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2013,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2010,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2008,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2011,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2012,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2007,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2007,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2009,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2013,'Tuff Guard Van Grille Guard'), ('205860','Freightliner','Sprinter 2500',2010,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 2500',2008,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 2500',2011,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 2500',2012,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 2500',2007,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 2500',2007,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 2500',2009,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 2500',2013,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 2500',2010,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 2500',2008,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 2500',2011,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 2500',2012,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 2500',2007,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 2500',2009,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 2500',2013,'Tuff Guard Brackets'), ('205530','Freightliner','Sprinter 2500',2015,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2017,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2016,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2014,'Tuff Guard Van Grille Guard'), ('205930','Freightliner','Sprinter 2500',2015,'Tuff Guard Brackets'), ('205930','Freightliner','Sprinter 2500',2017,'Tuff Guard Brackets'), ('205930','Freightliner','Sprinter 2500',2016,'Tuff Guard Brackets'), ('205930','Freightliner','Sprinter 2500',2014,'Tuff Guard Brackets'), ('205930','Freightliner','Sprinter 2500',2015,'Tuff Guard Brackets'), ('205930','Freightliner','Sprinter 2500',2017,'Tuff Guard Brackets'), ('205930','Freightliner','Sprinter 2500',2016,'Tuff Guard Brackets'), ('205930','Freightliner','Sprinter 2500',2014,'Tuff Guard Brackets'), ('205530','Freightliner','Sprinter 2500',2015,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2017,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2016,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 2500',2014,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2015,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2017,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2016,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2014,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2010,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2008,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2011,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2012,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2007,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2009,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2013,'Tuff Guard Van Grille Guard'), ('205930','Freightliner','Sprinter 3500',2015,'Tuff Guard Brackets'), ('205930','Freightliner','Sprinter 3500',2017,'Tuff Guard Brackets'), ('205930','Freightliner','Sprinter 3500',2016,'Tuff Guard Brackets'), ('205930','Freightliner','Sprinter 3500',2014,'Tuff Guard Brackets'), ('205930','Freightliner','Sprinter 3500',2015,'Tuff Guard Brackets'), ('205930','Freightliner','Sprinter 3500',2017,'Tuff Guard Brackets'), ('205930','Freightliner','Sprinter 3500',2016,'Tuff Guard Brackets'), ('205930','Freightliner','Sprinter 3500',2014,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 3500',2010,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 3500',2008,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 3500',2011,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 3500',2012,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 3500',2007,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 3500',2009,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 3500',2013,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 3500',2010,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 3500',2008,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 3500',2011,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 3500',2012,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 3500',2007,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 3500',2009,'Tuff Guard Brackets'), ('205860','Freightliner','Sprinter 3500',2013,'Tuff Guard Brackets'), ('205530','Freightliner','Sprinter 3500',2010,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2008,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2011,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2012,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2007,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2009,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2013,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2015,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2017,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2016,'Tuff Guard Van Grille Guard'), ('205530','Freightliner','Sprinter 3500',2014,'Tuff Guard Van Grille Guard'), ('111111','Velie','Velie 200',2020, 'Tuff Guard Van Grille Guard'), ('111111','Velie','Velie 200',2021, 'Tuff Guard Van Grille Guard'), ('111111','Velie','Velie 200',2022, 'Tuff Guard Van Grille Guard'), ('111111','Velie','Velie 200',2023, 'Tuff Guard Van Grille Guard'), ('222222','Velie','Velie 200',2020, 'Tuff Guard Brackets'), ('222222','Velie','Velie 200',2021, 'Tuff Guard Brackets'), ('333333','Velie','Velie 200',2022, 'Tuff Guard Brackets'), ('333333','Velie','Velie 200',2023, 'Tuff Guard Brackets') declare @part1 as varchar(100) declare @part2 as varchar(100) set @part1 = 'Tuff Guard Van Grille Guard'  TODO: extract part names from data set @part2 = 'Tuff Guard Brackets' declare @local as table ( id int IDENTITY(1,1), vMake varchar(50), vModel varchar(50), start_year int, end_year int, part1_id varchar(20), part2_id varchar(20) ) declare @vPartID varchar(20), @vMake varchar(50), @vModel varchar(50), @vYear int, @vPartType varchar(100) declare C CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR select distinct vPartID, vMake, vModel, vYear, vPartType from @PartVehicleTestData order by vYear open C while 1=1 begin fetch next from C into @vPartID, @vMake, @vModel, @vYear, @vPartType if @@FETCH_STATUS <> 0 break update L set end_year += 1 from @local as L where vMake = @vMake and vModel = @vModel and end_year = @vYear  1 and ( ( part1_id = @vPartID and (part2_id is null or exists ( select * from @PartVehicleTestData where vMake = @vMake and vModel = @vModel and vPartID = L.part2_id and vYear = L.end_year + 1)) ) or ( part2_id = @vPartID and ( part1_id is null or exists ( select * from @PartVehicleTestData where vMake = @vMake and vModel = @vModel and vPartID = L.part1_id and vYear = L.end_year + 1)) ) ) if @@ROWCOUNT = 0 begin update @local set part1_id = @vPartID where @vPartType = @part1 and vMake = @vMake and vModel = @vModel and @vYear = end_year and ISNULL(part1_id, @vPartID) = @vPartID if @@ROWCOUNT = 0 begin update @local set part2_id = @vPartID where @vPartType = @part2 and vMake = @vMake and vModel = @vModel and @vYear = end_year and ISNULL(part2_id, @vPartID) = @vPartID if @@ROWCOUNT = 0 begin insert into @local ( vMake, vModel, start_year, end_year, part1_id, part2_id ) values ( @vMake, @vModel, @vYear, @vYear, case @vPartType when @part1 then @vPartID end, case @vPartType when @part2 then @vPartID end) end end end end close C deallocate C  select * from @local order by vMake, vModel, start_year /* id vMake vModel start_year end_year part1_id part2_id 1 Freightliner Sprinter 2500 2007 2013 205530 205860 3 Freightliner Sprinter 2500 2014 2017 205530 205930 2 Freightliner Sprinter 3500 2007 2013 205530 205860 4 Freightliner Sprinter 3500 2014 2017 205530 205930 5 Velie Velie 200 2020 2021 111111 222222 6 Velie Velie 200 2022 2023 111111 333333 */
 Edited by Viorel_MVP Thursday, September 12, 2019 2:40 PM

This is exactly the issue here Sabrina.
It is simple to solve the issue using hardcoded dates and there is already a solution, but Jesse say that he want to have it dynamically so we need to understand the logic and I fail in this. I do not see any logic in the example which fit, except one option:
> always have 2 ranges which split the years into 2 "equal" groups that cover together the range between the min year to the max year.
For example if the min year is X and the max year is Y then the ranges should be:
First range: from X to X + ((YX)/2)  1
Second Range: from X + ((YX)/2) to YThis is totally without any base 😃
Ronen Ariely
[Personal Site] [Blog] [Facebook] [Linkedin] 
OK  I will try to explain further.
We have parts that fit specific vehicles. We make new parts and new vehicles come out. These requirements are why it needs to be dynamic. The reason we make new parts is to fit new vehicles that other parts do not fit.
The goal of this report is to show you which parts fit your vehicle.
Here is an example of a finalized / polished report based on production data (different parts and different vehicles)  you can ignore the formatting and focus on just the data that was produced from another set of data similar to what I provided above.
The challenge that exists is due to a bracket only fitting a subset of years while the guard that we want to show on the same line fits a larger group of years. The logic I would like to use would be:
The parts that are 'Tuff Guard Brackets' should be looked at first to determine the year ranges for the output. Once that is determined, match the 'Tuff Guard Van Grille Guards' part numbers where they match indirectly.
When I say 'match indirectly' I mean wider range being equivalent to smaller ranges. One example would be  guard fits 1019, so any brackets that have fitment ranges smaller, the guard would match  1012, 1011, 1015, etc. It wouldn't matter, as long as it fell within the range.
Jesse A. Brandenburg

OK  I will try to explain further.
We have parts that fit specific vehicles. We make new parts and new vehicles come out. These requirements are why it needs to be dynamic. The reason we make new parts is to fit new vehicles that other parts do not fit.
The goal of this report is to show you which parts fit your vehicle.
Here is an example of a finalized / polished report based on production data (different parts and different vehicles)  you can ignore the formatting and focus on just the data that was produced from another set of data similar to what I provided above.
The challenge that exists is due to a bracket only fitting a subset of years while the guard that we want to show on the same line fits a larger group of years. The logic I would like to use would be:
The parts that are 'Tuff Guard Brackets' should be looked at first to determine the year ranges for the output. Once that is determined, match the 'Tuff Guard Van Grille Guards' part numbers where they match indirectly.
When I say 'match indirectly' I mean wider range being equivalent to smaller ranges. One example would be  guard fits 1019, so any brackets that have fitment ranges smaller, the guard would match  1012, 1011, 1015, etc. It wouldn't matter, as long as it fell within the range.
Jesse A. Brandenburg
Hi,
Now we going somewhere (hopefully) 😃👍
>> When I say 'match indirectly' I mean wider range being equivalent to smaller ranges. One example would be  guard fits 1019, so any brackets that have fitment ranges smaller, the guard would match  1012, 1011, 1015, etc. It wouldn't matter, as long as it fell within the range.
What if several brackets years fit the same smaller range? which one of them will be used as Part 2 in the result?
* You can notice that designing a system is not some think simple but can take time and become very complex. You must cover all future cases which you did not thought about originally  Architect is the highest level in the process of developing application
Ronen Ariely
[Personal Site] [Blog] [Facebook] [Linkedin] Edited by pituachMVP, Moderator Thursday, September 12, 2019 8:17 PM

That is a good question  that doesn't occur. We don't have brackets that overlap. If that were to occur, creating another line would be OK.
the business reasoning behind the answer is that it doesn't make sense to produce two brackets that fit the same vehicle (they only serve 1 purpose).
Thanks,
Jesse
Jesse A. Brandenburg

Hi,
Assuming I understood you well and using some assumptions and guessing... please check if the following solve your needs:
;with MyCTE as ( select vMake as Make , vModel as Model, vYear as Year , [Tuff Guard Van Grille Guard] as Guard, [Tuff Guard Brackets] as Bracket from PartVehicleTestData pivot (max([vpartid])for[vparttype]in( [Tuff Guard Van Grille Guard],[Tuff Guard Brackets]) )as pvt ) select distinct Make,Model,Years = RIGHT(MIN(Year),2)+''+RIGHT(MAX(Year),2), Guard, Bracket from MyCTE GROUP BY Make,Model,Guard, Bracket order by Make,Model,Guard, Bracket GO
As you can see the result fits your request
Make Model Years Guard Bracket Freightliner Sprinter 2500 0713 205530 205860 Freightliner Sprinter 2500 1417 205530 205930 Freightliner Sprinter 3500 0713 205530 205860 Freightliner Sprinter 3500 1417 205530 205930
I hope this solve your needs
Offtopic: I HIGHLY recommend you to rethink about your entire system architecture and the database designed. According to the discussion here and the information we have, it seems like you it is really not well done
Ronen Ariely
[Personal Site] [Blog] [Facebook] [Linkedin] Edited by pituachMVP, Moderator Friday, September 13, 2019 11:50 PM

Thank you I will try that
Please elaborate on your comment 
"Offtopic: I HIGHLY recommend you to rethink about your entire system architecture and the database designed. According to the discussion here and the information we have, it seems like you it is really not well done"
Specifically, how would you structure vehicles related to parts with the thought that reports need to be created to produce an easy way to find all of the parts for your vehicle?
Thanks!
Jesse
Jesse A. Brandenburg