asked
Check group contain similar data in two column
Hello All, Seeking your expertise suggestion here. if possible please help. part|make|model|year|remarks|message 51081|54|2816|2007|TRAIN|o God! here is **PLANE** fix it. 51081|54|2816|2007|PLANE|again fix **BUS** is here 51081|54|2816|2007|BUS| catch **TRAIN** is here too Here when we are grouping based on part,make,model,year contain n number of remarks which is same available in message need to remove from the result set.
Hi Binod, Can you explain a little further. For part 51081 line 1 you would remove line 2 as it contains plane. Line 2 you would remove line 3 because it contains bus then line 3 would remove line 1 as it contains train. This would result in an empty dataset. Correct?
Here's a solution that's no better than @SQLShark for performance, but doesn't need hardcoding declare @YourTable table (part int, make int, model int, [year] int, remarks varchar(max), [message] varchar(max)) insert into @YourTable select 51081,54,2816,2007,'TRAIN','o God! here is PLANE fix it.' insert into @YourTable select 51081,54,2816,2007,'PLANE','again fix BUS is here' insert into @YourTable select 51081,54,2816,2007,'BUS',' catch TRAIN is here too' ;WITH Nbrs_4( n ) AS ( SELECT 1 UNION SELECT 0 ), --2 Nbrs_3( n ) AS ( SELECT 1 FROM Nbrs_4 n1 CROSS JOIN Nbrs_4 n2 ), --2*2 = 4 Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), --4*4 = 16 Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), --16*16 = 256 Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), --256*256 = 65,536 Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ) --65536*65536 = 4,294,967,296 ,iCTE as ( select n FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n) FROM Nbrs ) D ( n ) where n < 500 ), cleantable as ( select part, make, model, year, remarks, [message]+' ' as [message] from @YourTable ), SplitMessage as ( select part,make, model,[year],remarks,substring([message] , N + 1, charindex(' ', [message] , N + 1) - N -1) as Word from cleantable join iCTE on N < len([message] ) where substring([message] , N, 1) = ' ' ) select [@YourTable].* from @YourTable left join ( select part,make, model,[year], remarks from SplitMessage where Word in (select remarks from @YourTable) ) ToRemove on ToRemove.make = [@YourTable].make and ToRemove.model = [@YourTable].model and ToRemove.part = [@YourTable].part and ToRemove.year = [@YourTable].year and ToRemove.remarks = [@YourTable].remarks where ToRemove.part is null I tested it with declare @YourTable table (part int, make int, model int, [year] int, remarks varchar(max), [message] varchar(max)) insert into @YourTable select 51081,54,2816,2007,'TRAIN','o God! here is PLANE fix it.' insert into @YourTable select 51081,54,2816,2007,'PLANE','again fix BUS is here' insert into @YourTable select 51081,54,2816,2007,'CAR',' catch TRAIN is here too' and it produces the row that mentions 'BUS' as there are no remarks with value = 'BUS'. Not sure if this is what you want???
Sorry I should have added more comments about the code - I'll get back to that in a min...
Yes - change line 35 above to where (Word in (select remarks from @YourTable) or Word in (select remarks2 from @YourTable) or Word in (select remarks3 from @YourTable) ) or someother such similar construct that pulls all the values together (maybe a union somewhere)
Right this is terrible for performance and case statements in a where clause are a big no-no however it should do what you want. DECLARE @Example TABLE ( part INT , make INT , model INT , year INT , remarks VARCHAR(100) , message VARCHAR(100) ) INSERT INTO @Example ( part, make, model, year, remarks, message ) VALUES ( 51081, 54, 2816, 2007, 'TRAIN', 'o God! here is PLANE fix it.' ) , ( 51081, 54, 2816, 2007, 'PLANE', 'again fix BUS is here' ) , ( 51081, 54, 2816, 2007, 'BUS', 'catch TRAIN is here too' ) , ( 51082, 54, 2816, 2007, 'PLANE', 'again fix BUS is here' ) , ( 51082, 54, 2816, 2007, 'BUS', 'catch TRAIN is here too' ); WITH cte1 AS ( SELECT part , make , model , year , MAX(CASE WHEN PATINDEX('%TRAIN%', message) > 0 THEN 1 ELSE 0 END) AS 'Train_Check' , MAX(CASE WHEN PATINDEX('%BUS%', message) > 0 THEN 1 ELSE 0 END) AS 'Bus_Check' , MAX(CASE WHEN PATINDEX('%PLANE%', message) > 0 THEN 1 ELSE 0 END) AS 'Plane_Check' FROM @Example GROUP BY part , make , model , year ) SELECT * FROM @Example INNER JOIN cte1 ON cte1.make = [@Example].make AND cte1.model = [@Example].model AND cte1.part = [@Example].part AND cte1.year = [@Example].year WHERE CASE WHEN cte1.Train_Check = 1 AND remarks = 'TRAIN' THEN 1 ELSE 0 END = 0 AND CASE WHEN cte1.Bus_Check = 1 AND remarks = 'BUS' THEN 1 ELSE 0 END = 0 AND CASE WHEN cte1.Plane_Check = 1 AND remarks = 'PLANE' THEN 1 ELSE 0 END = 0
15 People are following this question.
