none
Smart Year Ranges - Group By YEARS, Min/Max Combination RRS feed

  • 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 F-150
    234                 2015                2019                 Ford F-150
    456                 2010                2019                 Ford F-150

    My desired Results:

    Vehicle               Year Range            Part 1        Part 2

    Ford F-150          10-14                   123           456
    Ford F-150          15-19                   234           456

    Since 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

    Monday, September 9, 2019 8:18 PM

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 F-150'),
    (234,2015,2019,'Ford F-150'),
    (456,2010,2019,'Ford F-150')
    
    ;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 F-150   10-14      123     456
    Ford F-150   15-19      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.

    Tuesday, September 10, 2019 2:47 AM
  • Is this a possible case and what result do you expect?

    Part Number    Minimum Year   Maximum Year   Vehicle

    123            2010           2014           Ford F-150

    456            2012           2019           Ford F-150

     

    Tuesday, September 10, 2019 6:34 AM
  • That is possible (not likely) - but should be accounted for.

    The result I would expect:

    Vehicle                  Year Range     Part 1     Part 2 

    Ford F-150            2010-2012     123         (NULL)

    Ford F-150            2012-2014     123         456

    Ford F-150            2015-2019     (NULL)     456

    Thanks!

    Jesse   


    Jesse A. Brandenburg

    Tuesday, September 10, 2019 2:25 PM
  • 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


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, September 10, 2019 5:57 PM
    Moderator
  • Can you post your DDL,DML Scripts along with your sample data and expected results in one shot.
    Tuesday, September 10, 2019 6:20 PM
  • 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

    Tuesday, September 10, 2019 10:09 PM
  • 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 '14-17'
    when vyear between 2007 and 2013 then '07-13'
    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     07-13 205530    205860
    Freightliner     Sprinter 2500     14-17 205530    205930
    Freightliner     Sprinter 3500     07-13 205530    205860
    Freightliner     Sprinter 3500     14-17 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.

    Wednesday, September 11, 2019 2:06 AM
  • Hi,

    Thanks for the information. It is much simpler to discuss now

    Can you clarify why you split '07-13' and '14-17' 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 ignored


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, September 11, 2019 3:10 AM
    Moderator
  • 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 (07-17) 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

    Wednesday, September 11, 2019 10:18 AM
  • I am pretty sure that we can provide you with dynamic solution instead hard-coded 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


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Wednesday, September 11, 2019 1:43 PM
    Moderator
  • 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 (12-15, 16-19) or (11-17, 18-19), etc.  The guard will fit (12-19, 11-19, 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

    Wednesday, September 11, 2019 1:57 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 (12-15, 16-19) or (11-17, 18-19), etc.  The guard will fit (12-19, 11-19, 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 (12-15, 16-19) or (11-17, 18-19), etc.  The guard will fit (12-19, 11-19, etc).

    Why do you use the ranges (12-15, 16-19) or (11-17, 18-19)? 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 ('07-13' and '14-17') and not ('07-11' and '12-17') for example

    We 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 '07-11' and '12-14' and '15-17'

    My question was what is the rules for the ranges in the sample data for example (and in general)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Wednesday, September 11, 2019 7:37 PM
    Moderator
  • 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 pre-requisites for the guards.  If the bracket doesn't fit, the guard cannot.  

    Thanks,

    Jesse


    Jesse A. Brandenburg

    Wednesday, September 11, 2019 7:45 PM
  • 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? 


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Wednesday, September 11, 2019 8:52 PM
    Moderator
  • 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

    Thursday, September 12, 2019 2:14 AM
  • 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 ?!?


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Thursday, September 12, 2019 4:40 AM
    Moderator
  • 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      07-09   205530   205860
    Freightliner     Sprinter 2500      14-16   205530   205930
    Freightliner     Sprinter 3500      07-09   205530   205860
    Freightliner     Sprinter 3500      14-16   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.

    Thursday, September 12, 2019 7:55 AM
  • 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
    Thursday, September 12, 2019 9:04 AM
  • This is exactly the issue here Sabrina.

    It is simple to solve the issue using hard-coded 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 + ((Y-X)/2) - 1
    Second Range: from       X + ((Y-X)/2)          to      Y

    This is totally without any base 😃


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Thursday, September 12, 2019 9:31 AM
    Moderator
  • 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.  

    https://assets.curtmfg.com/masterlibrary/01resources/appguides/ARIES/AdvantEDGE_5.5_Inch_Bull%20Bars_Application_Guide.pdf

    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 10-19, so any brackets that have fitment ranges smaller, the guard would match - 10-12, 10-11, 10-15, etc.  It wouldn't matter, as long as it fell within the range.  


    Jesse A. Brandenburg

    Thursday, September 12, 2019 1:54 PM
  • 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.  

    https://assets.curtmfg.com/masterlibrary/01resources/appguides/ARIES/AdvantEDGE_5.5_Inch_Bull%20Bars_Application_Guide.pdf

    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 10-19, so any brackets that have fitment ranges smaller, the guard would match - 10-12, 10-11, 10-15, 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 10-19, so any brackets that have fitment ranges smaller, the guard would match - 10-12, 10-11, 10-15, 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


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Thursday, September 12, 2019 6:29 PM
    Moderator
  • 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

    Thursday, September 12, 2019 9:08 PM
  • 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    07-13   205530    205860
    Freightliner   Sprinter 2500    14-17   205530    205930
    Freightliner   Sprinter 3500    07-13   205530    205860
    Freightliner   Sprinter 3500    14-17   205530    205930

    I hope this solve your needs

    Off-topic: I HIGHLY recommend you to re-think 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


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Friday, September 13, 2019 11:43 PM
    Moderator
  • Thank you I will try that-

    Please elaborate on your comment - 

    "Off-topic: I HIGHLY recommend you to re-think 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

    Monday, September 16, 2019 6:08 PM