question

Binod avatar image
Binod asked

Grouping make life hell

hello sorry......
sql-server-2008t-sqldynamic-sqlattachment
11 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
please put the content into the question - it will make life easier!
1 Like 1 ·
Binod avatar image Binod commented ·
@kev. Hi Kev any suggestion on this. i have written a query but its giving wrong data. shall i share the script with you.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
yes please - the more you share with us the better we can help
0 Likes 0 ·
Binod avatar image Binod commented ·
@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.
0 Likes 0 ·
Binod avatar image Binod commented ·
@kev: Hi Kev, any luck. :)
0 Likes 0 ·
Show more comments
Kev Riley avatar image
Kev Riley answered
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)
15 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Binod avatar image Binod commented ·
Fortunate enough to have friend like you.Shall i test with some more sample data....... Thanks a ton. :)
0 Likes 0 ·
Binod avatar image Binod commented ·
@Kev: Hi Kev, the query works very well, but its checking which not available with base part. can we go one step further to also get Attributes not present with like and present with base. Please Please suggest.
0 Likes 0 ·
Binod avatar image Binod commented ·
for eg:- insert into @Parts select 'MCNW10','40','353','2001','rearseat','CLASS','FRONT' insert into @Parts select 'MCNW10','40','353','2002','frontseat','CLASS','FRONT' The rearseat and frontseat are not available with any of the like part (MCNW10GR or MCNW10TN).
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
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
0 Likes 0 ·
Binod avatar image Binod commented ·
@kev: Hi Kev: Running this query is giving only change not present in like only. do we need to add this in 1st part of the query. but how to frame message for not available with like part
0 Likes 0 ·
Show more comments
Kev Riley avatar image
Kev Riley answered
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
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
edited. Not a nice query at all!!
1 Like 1 ·
Binod avatar image Binod commented ·
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.
0 Likes 0 ·
Binod avatar image Binod commented ·
:)....... and this time if they change requirement, i'll quit Org. :) Thanks a ton Kev. Being a fresher learn a lot from you.
0 Likes 0 ·
Binod avatar image Binod commented ·
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
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
@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.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.