CODEWITHSUNDEEP
sqlsql-server
Muhammad Naseeruddin
Muhammad Naseeruddin

Reputation: 3

Query similar rows with different value in one column

My table as per below.

Sub_JS_Number Issued_weight Defect_type Defect_weight Month
2121830/1-001A 7.58 Crack 6.43 December
2121830/1-001A 7.58 Crack 6.43 January
2121830/1-001A 7.58 Clamping crack 1.134 January
2122032/2-001A 4.52 Crack 4.50 January
2122032/2-001A 4.52 Clamping crack 2.92 January
2121880/1-001A 7.78 Crack 7.78 February
2121880/1-001A 7.78 Crack 7.78 January

I want to query out rows with same Sub_JS_Number and Month but different in Defect_type and the sum of the Defect_weight for that particular rows is greater than Issued_weight.

Expected Result:

Sub_JS_Number Issued_weight Defect_type Defect_weight Month
2122032/2-001A 4.52 Crack 4.50 January
2122032/2-001A 4.52 Clamping crack 2.92 January

There are two Sub_JS_Number with the same Month which are 2121830/1-001A and 2122032/2-001A.

However only Sub_JS_Number 2122032/2-001A rows are shown because sum of Defect_weight for those two rows exceed the Issued_weight.

If it is not possible, I would like to query out just rows with same Sub_JS_Number and Month but different in Defect_type without the aforementioned condition. Thank you in advance.

Upvotes: 0

Views: 52

Answers (1)

allmhuran
allmhuran

Reputation: 4474

I believe this is what you're after.

I use a CTE to find rows with the same Sub_JS_Number, and month. I also group by issue_weight, which is implied to be safe by your comment because it is depdenent upon Sub_JS_Number. Grouping on it allows me to compare it to the sum of defect_weight. I only get rows where there is more than one distinct defect_type.

Once I have found the Sub_JS_Numbers matching your conditions I just join back to the original table to get the full original rows, per your expected output.


create table #mytable
(
   sub_js_number varchar(20),
   issued_weight decimal(9,3),
   defect_type varchar(20),
   defect_weight decimal(9, 3),
   [month] varchar(20)
);

insert #mytable 
(
   sub_js_number, 
   issued_weight, 
   defect_type, 
   defect_weight, 
   [month]
)
values
   ('2121830/1-001A',  7.58,'Crack          ', 6.43  , 'December'),
   ('2121830/1-001A',  7.58,'Crack          ', 6.43  , 'January '),
   ('2121830/1-001A',  7.58,'Clamping crack ', 1.134 , 'January '),
   ('2122032/2-001A',  4.52,'Crack          ', 4.50  , 'January '),
   ('2122032/2-001A',  4.52,'Clamping crack ', 2.92  , 'January '),
   ('2121880/1-001A',  7.78,'Crack          ', 7.78  , 'February'),
   ('2121880/1-001A',  7.78,'Crack          ', 7.78  , 'January ');

with matches as
(
   select   distinct    
            sub_js_number 
   from     #mytable
   group by sub_js_number, 
            issued_weight, 
            [month]
   having   count(distinct defect_type) > 1 
            and sum(defect_weight) > issued_weight
)
select t.*
from   #mytable t
join   matches  m on m.sub_js_number = t.sub_js_number;

Upvotes: 1

Related Questions