SQLTeam.com | Weblogs | Forums

SQLtricks

sql2008

#1

Respected All,

May i fortunate to get help on this if possible.

/*

logic

  1. table 1 contain two column
    basepart and likepart

  2. i need to check whether all attributes of basepart are same with attributes of like part.
    if any value present with base part and missing from like part should be in output with message column saying
    'x' value change in respective column

  3. similarly if if any value present with like part and missing from base part should be in output with message column saying
    'x' value not available with base part in respective column

if there is two difference in a columns of single rows then output consists of two records.

*/

declare @Driver table
(
BasePart varchar(50),
likepart varchar (50)
)

insert into @Driver select 'MCNW10','MCNW10__'

insert into @Driver select 'MCN442941','MCN4_2941'

SELECT * FROM @Driver

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','2001','FLOORMAT','CLASS','FRONT'
insert into @Parts select 'MCNW10GR','40','353','2001','FLOORMAT','CLASS','FRONT'
insert into @Parts select 'MCNW10TN','40','8888','2001','FLOORMAT','CLASS','FRONTREAR'
INSERT INTO @Parts SELECT 'MCN442941','65','548','2007','SLE, CREW ','front','V335'
INSERT INTO @Parts SELECT 'MCN442941','65','548','2007','SLE, CREW ','front','V335'
INSERT INTO @Parts SELECT 'MCN442941','67','548','2007','SLE, CREW ','front','V335'
INSERT INTO @Parts SELECT 'MCN442941','65','548','2007','SLT, CREW ','front','V336'
INSERT INTO @Parts SELECT 'MCN442941','65','548','2007','SLE, CREW ','front','V336'
INSERT INTO @Parts SELECT 'MCN442941','65','999','2007','WT, CREW ','front','V336'
INSERT INTO @Parts SELECT 'MCN462941','65','548','2007','SLT, CREW ','front','V336'
INSERT INTO @Parts SELECT 'MCN462941','65','548','2007','SLE, CREW ','front','V336'
INSERT INTO @Parts SELECT 'MCN462941','65','548','2007','WT; Extended','front','V336'
INSERT INTO @Parts SELECT 'MCN462941','65','548','2007','WT; Extended','front','V336'

--expected output
/*
part Make Model Year remarks remarks2 remarks3 BasePart likepart (comment)
MCN462941 65 548 2007 WT; Extended front V336 MCN442941 MCN4_2941 WT; Extended is not available with Base Part
MCN462941 65 548 2007 WT; Extended front V336 MCN442941 MCN4_2941 WT; Extended is not available with Base Part
MCNW10TN 40 8888 2001 FLOORMAT CLASS FRONTREAR MCNW10 MCNW10__ FRONTREAR is not available with Base Part
MCN442941 65 999 2007 WT, CREW front V336 MCN442941 MCN4_2941 WT, CREW is not available with Like Part MCN462941
MCNW10 40 353 2001 FLOORMAT CLASS FRONT MCNW10 MCNW10__ FRONT is not available with Like Part MCNW10TN
MCNW10 40 353 2001 FLOORMAT CLASS FRONT MCNW10 MCNW10__ FRONT is not available with Like Part MCNW10TN
MCN442941 65 548 2007 SLE, CREW front V335 MCN442941 MCN4_2941 V335 is not available with Like Part MCN462941
MCN442941 65 548 2007 SLE, CREW front V335 MCN442941 MCN4_2941 V335 is not available with Like Part MCN462941
MCN442941 67 548 2007 SLE, CREW front V335 MCN442941 MCN4_2941 V335 is not available with Like Part MCN462941
MCNW10TN' 40 8888 2001 FLOORMAT CLASS FRONTREAR' MCNW10 MCNW10__ 8888 is not present with Base Part
MCN442941 65 999 2007 WT, CREW V336-- model 999 is not present with like part MCN462941
'MCN442941','67', '548', '2007','SLE, CREW ','front', 'V335' MCN442941 MCN4_2941 -- make 67 not presented with like part MCN462941

*/


#2
  1. Write SQL select query to retrieve the result and execute after changing the Result to Text Option
  2. in the select query concate all the columns