asked
Grouping make life hell
hello sorry......
please put the content into the question - it will make life easier!
@kev: Hi Kev, i have attached the code.Please help me. Thanks. Problem is if two rows of Base number with value like x and x1 in some column, then my query is checking x of base with x1 of like, and vise-versa, and treating as it is wrong. but that is not the case, because x and x1 both available with base and like assuming no other changes. Please suggest.
Hopefully this is getting towards what you need: declare @Driver table (BasePart varchar(50)) insert into @Driver select 'MCNW10__' insert into @Driver select 'MCN4_001' declare @Parts table (part varchar(50), Make varchar(50), Model varchar(50),Year varchar(50),remarks varchar(50),remarks2 varchar(50),remarks3 varchar(50)) insert into @Parts select 'MCNW10','40','353','2001','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10','40','353','2002','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10','40','353','2003','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10GR','40','353','2001','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10GR','40','353','2002','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10GR','40','353','2003','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10GR','40','353','2001','FLOORMAT','CLASS','REAR' insert into @Parts select 'MCNW10TN','40','353','2001','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10TN','40','353','2002','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10TN','40','353','2009','FLOORMAT','God Save Me','FRONT' insert into @Parts select 'MCNW10TN','40','353','2003','FLOORMAT','CLASS','FRONT' ;with outputrows as ( select parts.*,replace(driver.BasePart,'_','') as BasePart from @Parts parts join @Driver driver on parts.part like driver.basepart except select parts.*,replace(driver.BasePart,'_','') from @Parts parts join @Driver driver on parts.part like driver.basepart join @Parts parts2 on replace(driver.BasePart,'_','') = parts2.part and parts2.Make = parts.Make and parts2.Model = parts.Model and parts2.Year = parts.Year and parts2.remarks = parts.remarks and parts2.remarks2 = parts.remarks2 and parts2.remarks3 = parts.remarks3 ) select *,outputrows.Year + ' is not available with Base Part' from outputrows where outputrows.Year not in (select year from @parts where part = outputrows.BasePart) union all select *,outputrows.remarks + ' is not available with Base Part' from outputrows where outputrows.remarks not in (select remarks from @parts where part = outputrows.BasePart) union all select *,outputrows.remarks2 + ' is not available with Base Part' from outputrows where outputrows.remarks2 not in (select remarks2 from @parts where part = outputrows.BasePart) union all select *,outputrows.remarks3 + ' is not available with Base Part' from outputrows where outputrows.remarks3 not in (select remarks3 from @parts where part = outputrows.BasePart) Edit : added the new requirement declare @Driver table (BasePart varchar(50)) insert into @Driver select 'MCNW10__' insert into @Driver select 'MCN4_001' declare @Parts table (part varchar(50), Make varchar(50), Model varchar(50),Year varchar(50),remarks varchar(50),remarks2 varchar(50),remarks3 varchar(50)) insert into @Parts select 'MCNW10','40','353','2001','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10','40','353','2002','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10','40','353','2003','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10GR','40','353','2001','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10GR','40','353','2002','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10GR','40','353','2003','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10GR','40','353','2001','FLOORMAT','CLASS','REAR' insert into @Parts select 'MCNW10TN','40','353','2001','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10TN','40','353','2002','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10TN','40','353','2009','FLOORMAT','God Save Me','FRONT' insert into @Parts select 'MCNW10TN','40','353','2003','FLOORMAT','CLASS','FRONT' insert into @Parts select 'MCNW10','40','353','2001','rearseat','CLASS','FRONT' insert into @Parts select 'MCNW10','40','353','2002','frontseat','CLASS','FRONT' ;with outputrows as ( select parts.*,replace(driver.BasePart,'_','') as BasePart from @Parts parts join @Driver driver on parts.part like driver.basepart except select parts.*,replace(driver.BasePart,'_','') from @Parts parts join @Driver driver on parts.part like driver.basepart join @Parts parts2 on replace(driver.BasePart,'_','') = parts2.part and parts2.Make = parts.Make and parts2.Model = parts.Model and parts2.Year = parts.Year and parts2.remarks = parts.remarks and parts2.remarks2 = parts.remarks2 and parts2.remarks3 = parts.remarks3 ) ,outputrows2 as ( select Make,Model,Year,remarks,remarks2,remarks3,parts.part from @Parts parts left join @Driver driver on parts.part like driver.basepart where driver.BasePart is null except select Make,Model,Year,remarks,remarks2,remarks3,replace(driver.BasePart,'_','') as BasePart from @Parts parts left join @Driver driver on parts.part like driver.basepart intersect select parts.Make, parts.Model, parts.Year, parts.remarks, parts.remarks2, parts.remarks3,replace(driver.BasePart,'_','') from @Parts parts join @Driver driver on parts.part like driver.basepart join @Parts parts2 on replace(driver.BasePart,'_','') = parts2.part and parts2.Make = parts.Make and parts2.Model = parts.Model and parts2.Year = parts.Year and parts2.remarks = parts.remarks and parts2.remarks2 = parts.remarks2 and parts2.remarks3 = parts.remarks3 ) select *,outputrows.Year + ' is not available with Base Part' from outputrows where outputrows.Year not in (select year from @parts where part = outputrows.BasePart) union all select *,outputrows.remarks + ' is not available with Base Part' from outputrows where outputrows.remarks not in (select remarks from @parts where part = outputrows.BasePart) union all select *,outputrows.remarks2 + ' is not available with Base Part' from outputrows where outputrows.remarks2 not in (select remarks2 from @parts where part = outputrows.BasePart) union all select *,outputrows.remarks3 + ' is not available with Base Part' from outputrows where outputrows.remarks3 not in (select remarks3 from @parts where part = outputrows.BasePart) union all select part,*,outputrows2.Year + ' is not available with like Part' from outputrows2 where outputrows2.Year not in (select year from @Parts parts join @Driver driver on parts.part like driver.basepart) union all select part,*,outputrows2.remarks + ' is not available with like Part' from outputrows2 where outputrows2.remarks not in (select remarks from @Parts parts join @Driver driver on parts.part like driver.basepart) union all select part,*,outputrows2.remarks2 + ' is not available with like Part' from outputrows2 where outputrows2.remarks2 not in (select remarks2 from @Parts parts join @Driver driver on parts.part like driver.basepart) union all select part,*,outputrows2.remarks3 + ' is not available with like Part' from outputrows2 where outputrows2.remarks3 not in (select remarks3 from @Parts parts join @Driver driver on parts.part like driver.basepart)
Something like: select Make,Model,Year,remarks,remarks2,remarks3,parts.part from @Parts parts left join @Driver driver on parts.part like driver.basepart where driver.BasePart is null except select Make,Model,Year,remarks,remarks2,remarks3,replace(driver.BasePart,'_','') as BasePart from @Parts parts left join @Driver driver on parts.part like driver.basepart intersect select parts.Make, parts.Model, parts.Year, parts.remarks, parts.remarks2, parts.remarks3,replace(driver.BasePart,'_','') from @Parts parts join @Driver driver on parts.part like driver.basepart join @Parts parts2 on replace(driver.BasePart,'_','') = parts2.part and parts2.Make = parts.Make and parts2.Model = parts.Model and parts2.Year = parts.Year and parts2.remarks = parts.remarks and parts2.remarks2 = parts.remarks2 and parts2.remarks3 = parts.remarks3
Thanks Kev. but a small concern is which like part does not contain. as we have multiple like part for a base part. so is it possible to include that like part in message column or in run time column call as like part. or Will it be easier if we change Driver table as basepart|likepart MCNW10|MCNW10__ MCN40001| MCN4_001 Kindly suggest.
Hi Kev, Code is working fine with 1st base part MCNW10__, but not with 2nd base part MCN4_001. with MCN4_001, Its considering MCN4001 as base. looks like if we change base table as given below, will it easier to modify our existing query. declare @Driver table ( BasePart varchar (50), likepart varchar (50) ) insert into @Driver select 'MCNW10','MCNW10__' insert into @Driver select 'MCN40001','MCN4_001' I am extremely sorry to bother you again and again. this is very much critical to me and seeking your help. Thanks again.
@Kev: Hi Kev, if time permits, please help me.will it be helpful if i change the driver table with two column base and like . for eg:- declare @Driver table ( BasePart varchar (50), likepart varchar (50) ) insert into @Driver select 'MCNW10','MCNW10__' insert into @Driver select 'MCN40001','MCN4_001'. The present query catch wrong base part. instead of MCN40001, Its taking MCN4001. WE are replacing _ with ''. but thats look wrong. Please suggest.
@Binod - sorry I don't understand the issue - it can be difficult when you are not as immersed in the problem domain as you are - I have no data in @Parts that match on MCN4001 or MCN40001?
OK - think this might be easier if we pre-calculate what is a 'like part' and what is a 'base part' from the @Parts table - this then makes the query easier and easier to read I think: ;with LikeParts as ( select parts.part, parts.Make, parts.Model, parts.Year, parts.remarks, parts.remarks2, parts.remarks3, driver.BasePart, driver.likepart from @Parts parts join @Driver driver on parts.part like driver.likepart and parts.part driver.BasePart ) ,BaseParts as ( select parts.part, parts.Make, parts.Model, parts.Year, parts.remarks, parts.remarks2, parts.remarks3, driver.BasePart, driver.likepart from @Parts parts join @Driver driver on parts.part = driver.BasePart ) select *,LikeParts.Year + ' is not available with Base Part' from LikeParts where LikeParts.Year not in (select distinct Year from BaseParts) union all select *,LikeParts.remarks + ' is not available with Base Part' from LikeParts where LikeParts.remarks not in (select distinct remarks from BaseParts) union all select *,LikeParts.remarks2 + ' is not available with Base Part' from LikeParts where LikeParts.remarks2 not in (select distinct remarks2 from BaseParts) union all select *,LikeParts.remarks3 + ' is not available with Base Part' from LikeParts where LikeParts.remarks3 not in (select distinct remarks3 from BaseParts) union all select baseparts.*, baseparts.remarks + ' is not available with Like Part '+ remarks_exceptions.part from baseparts join ( select allparts.part, allremarks.remarks, allparts.BasePart from (select distinct basepart, part from Likeparts) allparts full join (select basepart, remarks from baseparts) allremarks on allremarks.BasePart = allparts.BasePart except select distinct part, remarks, BasePart from likeparts )remarks_exceptions on remarks_exceptions.BasePart = baseparts.BasePart and remarks_exceptions.remarks = baseparts.remarks union all select baseparts.*, baseparts.Year + ' is not available with Like Part '+ Year_exceptions.part from baseparts join ( select allparts.part, allYear.Year, allparts.BasePart from (select distinct basepart, part from Likeparts) allparts full join (select basepart, Year from baseparts) allYear on allYear.BasePart = allparts.BasePart except select distinct part, Year, BasePart from likeparts )Year_exceptions on Year_exceptions.BasePart = baseparts.BasePart and Year_exceptions.Year = baseparts.Year union all select baseparts.*, baseparts.remarks2 + ' is not available with Like Part '+ remarks2_exceptions.part from baseparts join ( select allparts.part, allremarks2.remarks2, allparts.BasePart from (select distinct basepart, part from Likeparts) allparts full join (select basepart, remarks2 from baseparts) allremarks2 on allremarks2.BasePart = allparts.BasePart except select distinct part, remarks2, BasePart from likeparts )remarks2_exceptions on remarks2_exceptions.BasePart = baseparts.BasePart and remarks2_exceptions.remarks2 = baseparts.remarks2 union all select baseparts.*, baseparts.remarks3 + ' is not available with Like Part '+ remarks3_exceptions.part from baseparts join ( select allparts.part, allremarks3.remarks3, allparts.BasePart from (select distinct basepart, part from Likeparts) allparts full join (select basepart, remarks3 from baseparts) allremarks3 on allremarks3.BasePart = allparts.BasePart except select distinct part, remarks3, BasePart from likeparts )remarks3_exceptions on remarks3_exceptions.BasePart = baseparts.BasePart and remarks3_exceptions.remarks3 = baseparts.remarks3
very close, is it possible to have like part in msg column. if possible. something like 'rearseat is not available with Like Part MCNW10TN' 'rearseat is not available with Like Part MCNW10GR'. Here Rearseat is not available with both the like part. and front seat is also not present with both like part MCNW10TN and MCNW10GR.
Good Morning Kev, Hope you are well. Seeking Your Help once again. :) The query you suggested was working superbly, but there is an small concern. The query will not give records if in row there is changes in two column. it will give only one records with 1 message. i have edited the question with sample data, Please help. everything is coming correctly, only 1 scenario how to handle changes in two column in a single row. Thanks Kev
@Binod, I am unable to provide you with immediate help - I would suggest you post this as a separate question with the sample data and original query. You are more likely to get help from others too.
14 People are following this question.
