CODEWITHSUNDEEP
sqlsql-server
Marina Gratiela Stan
Marina Gratiela Stan

Reputation: 59

Find rows with duplicate columns but distinct by one column with specified value

I have this data:

ID  PERSNR  YEARNR  MONTHNR DAYNR   ABSTIME ABSID   ABSCALC TypeLine
---------------------------------------------------------------------
 1  26      2018    12      3       480     3       11      0
 2  26      2018    12      3       480     3       11      1
 5  26      2018    10      1        60     1       31      0
 8  26      2018    10      3        60     1       31      0
13  69      2018    12      3       480     3       11      0
14  69      2018    12      3       480     3       11      1
19  69      2018     9      3        60     3       31      1
22  69      2018     9      3        60     3       31      0
23  69      2018     9      3       420    21       11      0
26  69      2018     9      6       120    21       31      1
29  69      2018     9     10       120    21       31      1
32  69      2018     9      4       480    21       11      1

I need to identify the following situations:

  1. the rows which have TypeLine both 0 and 1 Result Id's : 1 and 2; 13 and 14, 19 and 22

  2. the rows which have only TypeLine only 0 Result Id's: 5; 8; 23

  3. the rows which have only TypeLine only 1 Result Id's: 26, 29, 32

I'm not sure to create these 3 scripts and I couldn't find a solution.

Could you, please, help me?

Upvotes: 0

Views: 69

Answers (3)

Zorkolot
Zorkolot

Reputation: 2017

Assuming the source data was correct, you could run the following 3 queries. #1 is currently answering correctly but #2 and #3 have different DAYNUM's in the current version of the question so you won't return anything using those example values...

--1
SELECT T1.ID AS [T1_ID], T2.ID AS [T2_ID]
  FROM [tablename] T1 INNER JOIN [tablename] T2 ON T1.PERSNR = T2.PERSNR 
  AND T1.YEARNR = T2.YEARNR AND T1.MONTHNR = T2.MONTHNR
  AND T1.DAYNR = T2.DAYNR AND T1.ABSTIME = T2.ABSTIME
  AND T1.ABSID = T2.ABSID AND T1.ABSCALT = T2.ABSCALT
  AND (T1.TypeLine = 0 AND T2.TypeLine = 1
       OR
       T1.TypeLine = 1 AND T2.TypeLine = 0
      )
  AND T1.ID < T2.ID

--2
SELECT T1.ID AS [T1_ID], T2.ID AS [T2_ID]
  FROM [tablename] T1 INNER JOIN [tablename] T2 ON T1.PERSNR = T2.PERSNR 
  AND T1.YEARNR = T2.YEARNR AND T1.MONTHNR = T2.MONTHNR
  AND T1.DAYNR = T2.DAYNR AND T1.ABSTIME = T2.ABSTIME
  AND T1.ABSID = T2.ABSID AND T1.ABSCALT = T2.ABSCALT
  AND T1.TypeLine = 0 AND T2.TypeLine = 0
  AND T1.ID < T2.ID

--3 
SELECT T1.ID AS [T1_ID], T2.ID AS [T2_ID]
  FROM [tablename] T1 INNER JOIN [tablename] T2 ON T1.PERSNR = T2.PERSNR 
  AND T1.YEARNR = T2.YEARNR AND T1.MONTHNR = T2.MONTHNR
  AND T1.DAYNR = T2.DAYNR AND T1.ABSTIME = T2.ABSTIME
  AND T1.ABSID = T2.ABSID AND T1.ABSCALT = T2.ABSCALT
  AND T1.TypeLine = 1 AND T2.TypeLine = 1       
  AND T1.ID < T2.ID

Upvotes: 0

user11116003
user11116003

Reputation:

Try something like this: 

SELECT DISTINCT ID, 
PERSNR, 
YEARNR, 
MONTHNR, 
DAYNR, 
ABSTIME, 
ABSID, 
ABSCALC, 
iif(count(TypeLine) >= 2, 'duplicate', iif(min(TypeLine) = 1, '1', '0')) as status
FROM table
GROUP BY ID, PERSNR, YEARNR, MONTHNR, DAYNR, ABSTIME, ABSID, ABSCALC

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Does this do what you want?

select (case when cnt_type_0 > 0 and cnt_type_1 > 0
             then 'Condition 1'
             when cnt_type_1 = 0
             then 'Condition 2'
             when cnt_type_0 = 0
             then 'Condition 3'
        end) as condition,
       t.*
from (select t.*,
             count(*) over (partition by ID, PERSNR, YEARNR, MONTHNR, DAYNR, ABSTIME, ABSID, ABSCALC) as cnt,
             sum(case when TypeLine = 0 then 1 else 0 end) over (partition by ID, PERSNR, YEARNR, MONTHNR, DAYNR, ABSTIME, ABSID, ABSCALC) as cnt_type_0,             
             sum(case when TypeLine = 1 then 1 else 0 end) over (partition by ID, PERSNR, YEARNR, MONTHNR, DAYNR, ABSTIME, ABSID, ABSCALC) as cnt_type_1
       from t
      ) t
where cnt >= 2;

You can add the conditions into the WHERE clause to get rows of just one type.

Upvotes: 1

Related Questions