Need some help with my SQL Statement please 1

[grippy]

INSERT INTO Product_SpecificationAttribute_Mapping
(ProductId, SpecificationAttributeOptionId, AllowFiltering, ShowOnProductPage, DisplayOrder)

SELECT Product.Id AS ProductId, SpecificationAttributeOption.Id AS SpecificationAttributeOptionId, '1' AS AllowFiltering, '0' AS ShowOnProductPage, SpecificationAttributeOption.DisplayOrder AS DisplayOrder
FROM Product
INNER JOIN SpecificationAttributeOption ON ' '+Product.Name+' ' LIKE '% ' + SpecificationAttributeOption.Name +' %' 
WHERE SpecificationAttributeId = 7
ORDER BY SpecificationAttributeOption.Name ASC

Ok this is what the data looks like in SpecificationAttributeOption.Name

2002
2001
2000
1999
1998 and so on.

now my Product.Name data looks like:

blah blah Chevy Cavalier 99-03 4 Cyl

so I need the sql statement to trim SpecificationAttributeOption.Name to the last 2 digits and then find recognize the range of years and insert the Id's for 1999,2000,2001,2002 and 2003 all as separate rows for each product. Right now it works for finding the whole year in the product name. Any and all help is appreciated. Thank you!

 

[grippy]

Ok I changed it and it executed successfully but all of the results are not correct. Here is an example of what the product name was:

Exhaust System 99-03 Mazda Miata MX5

and it returned 10 results for that one product which were:

2003, 2002, 2001, 2000, 1999,

2000, 3200, 4000, 4200, 5000, 9000 <----These are all either Mazda or Saab car model names that its pulling. I figured out a way we can make sure it only pulls the years. SpecificationAttributeOption.SpecificationAttributeId field equals 7 for anything that's a year and equals 6 for anything that's a car model and 5 for car make. So I added the AND portion to the end of it and all the results pulled up perfectly. Do I just switch this to an insert statement and its good to go?

SELECT PD.Id  AS ProductId, 
       sao.Id AS SpecificationAttributeOptionId, 
       '1'    AS AllowFiltering, 
       '0'    AS ShowOnProductPage, 
       sao.DisplayOrder AS DisplayOrder
FROM Product as PD
join (
      -- Get product with evaluated previous century / current century ranges.
      select id, 
             name, 
             ymin as pcmin,       
             case when ymax < ymin then 99 else ymax end as pcmax,
             case when ymax < ymin then  0 else ymin end as ccmin,
             ymax as ccmax
      from (       
            -- Product name with year entries as min / max.
            select id, 
                   name, 
                   convert(int, SUBSTRING(name, loc-2, 2)) as ymin,
                   convert(int, SUBSTRING(name, loc+1, 2)) as ymax
            from (       
                  -- Pick those entries where '-' is in the form _XX-YY_
                  select id, 
                         name, 
                         loc
                  from (
                        -- Find the location of the all important '-'.
                        select id, 
                               name,
                               CHARINDEX('-', name) as loc
                        from PRODUCT
                        where CHARINDEX('-', name) > 0
                       ) as tbl
                  where SUBSTRING(name, loc-3, 1) = ' ' 
                    and SUBSTRING(name, loc+3, 1) = ' '
                 ) as rng
            where convert(int, SUBSTRING(name, loc-2,2)) > 0
              and convert(int, SUBSTRING(name, loc+1,2)) > 0
           ) as yrs
     ) as PdRng
on PdRng.id = PD.id
-- Join the specification where the spec is between the computed product ranges.
join (
      -- Only want the entries that are 4 digit years.
      select *
      from SpecificationAttributeOption 
      where isnumeric(Name) > 0
        and len(rtrim(Name)) = 4 AND SpecificationAttributeOption.SpecificationAttributeId = '7'
     ) as sao       
ON convert(int, Right(sao.Name, 2)) between PdRng.pcmin and PdRng.pcmax
OR convert(int, Right(sao.Name, 2)) between PdRng.ccmin and PdRng.ccmax

 

[denimined]

Yup.
Your insert would be:

INSERT INTO Product_SpecificationAttribute_Mapping
(ProductId, SpecificationAttributeOptionId, AllowFiltering, ShowOnProductPage, DisplayOrder)

and would be directly in front of the previous SQL statement.

 

[grippy]

Success!!!!!!!!! It worked out great! Thank you sooooo much for your extended help it is very much appreciated!

 

[grippy]

Hey long time to talk haha. So I just discovered yesterday that this statement is leaving something out. For all products that begin with the year 2000 such as 00-04 it does not input the year at all for any of the years. However 01-04 will work just fine. Turns out this affects 4200 products. Also if I have added products since the last time I ran this SQL statement will it put in duplicate entries for the ones that already have the make, model and years entered in already? If so is there a way to modify it to only add the stuff for the ones that do not yet have that data in there yet? After that I should be able to run this script non stop without any issues and it wont require me to clear all the data first before running it again (which it takes a while and creates downtime on the website)

Thank you so much!