Need help on my existing SQL Insert Query

[grippy]

Ok basically what this statement is doing is going through all product names in the product table and finding the years and inserting them into a product specification attribute table. The tricky part is the products are all listed like this:

Exhaust system for 92-95 Honda Civic.

The statement currently works great for that and finds the range of 92, 93, 94 and 95 and then matches it to the specification attributes (which are 4 digit years) and inserts each one as 1992, 1993, 1994, 1995.

The problem I am running into is any product with a year range that begins or ends with 00 does not work. So Exhaust system for 00-06 Honda Civic or 98-00 Honda Civic does not work. It wont even insert the other years either.

However for a year range that has 00 in between it works. So 98-02 Honda Civic will insert all the correct years 1998, 1999, 2000, 2001, 2002.

This is causing a huge issue with my website and I appreciate any help that anyone can provide. Thank you!

INSERT INTO Product_SpecificationAttribute_Mapping
(ProductId, SpecificationAttributeOptionId, AllowFiltering, ShowOnProductPage, DisplayOrder)
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

 

[fredericofonseca]

typical y2k problem.
change this bit so it returns a 4 digit year using a window - e.g. if value > 50 then its 19xx else is 20xx.. simple case statement which you should know how to do.

convert(int, SUBSTRING(name, loc-2, 2)) as ymin,
convert(int, SUBSTRING(name, loc+1, 2)) as ymax

and then change the remaining query so it is looking up 4 digits years instead of just 2

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[grippy]

Thank you for your response. Unfortunately I am not extremely savvy on SQL statements and my knowledge in that subject doesn't get much further than basic inner joins. I googled case statement syntax but am still lost...

 

[lameid]

SQL Server uses Transact SQL or T-SQL. Because there are many SQL variants you need to be specific.
Google... "T-SQL Case"

http://msdn.microsoft.com/en-us/library/ms181765.aspx

You may also have luck with SQL Server and maybe your version number instead of T-SQL. Also you sould try and see if you can't get/find BOL (Books On Line).

BOL, basically is the SQL server help files and I think should be installed by default except maybe with SQL Express.