How to get Existed Key Which is more than 1 count compare to source Table

jn93 651 Reputation points
2023-02-08T10:19:32.6066667+00:00

Hi All, Lets say I have two sets of table which is tb1 and tb2. How can write the SQL query to get entire rows from @tb1 if I comparing the keys(CHDRNO and RSKNO) which the @tb2 and the count is more than 1 matching keys. The keys is CHDRNO and RSKNO. Some of the key have duplicate data.

Please refer the expected output below from @tb1 by applying above condition. I get two rows of (CHDRNO=Z0012725 and RSKNO=1) from @tb1 because this combination field contain more than 1 rows in @tb1 when compared to @tb2. Any combination key count have mismatch with @tb2 (the combination field count in @tb1 more than in @tb2) , the entire record should be query out from @tb1.

DDL and sample data population:

   -- DDL and sample data population, start
   DECLARE @tb1 TABLE (CHDRNO varchar(12), RSKNO int, ENDNLNE varchar(60),DATIME datetime2(7) )
   INSERT INTO @tb1 (CHDRNO, RSKNO, ENDNLNE,DATIME) VALUES
   ('Z0012725','1','JUST','2020-08-03 14:45:02.7549260'),
   ('Z0012725','1','ABC','2020-08-04 14:45:02.7549260'),
   ('Z0012725','2','DEF','2020-08-03 14:45:02.7549260'),
   ('Z0012725','2','RUN','2020-08-03 14:45:02.7549260'),
   ('Z0012725','3','AND','2020-08-03 14:45:02.7549260')
   -- DDL and sample data population, end


      -- DDL and sample data population, start
   DECLARE @tb2 TABLE (CHDRNO varchar(12), RSKNO int, ENDNLNE varchar(60),DATIME datetime2(7) )
   INSERT INTO @tb2 (CHDRNO, RSKNO, ENDNLNE,DATIME) VALUES
   ('Z0012725','1','JUST','2020-08-03 14:45:02.7549260'),
   ('Z0012725','2','DEF','2020-08-03 14:45:02.7549260'),
   ('Z0012725','2','RUN','2020-08-03 14:45:02.7549260'),
   ('Z0012725','3','AND','2020-08-03 14:45:02.7549260')
   -- DDL and sample data population, end

Expected Output:

User's image

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
Accepted answer
  1. Tom Cooper 8,466 Reputation points
    2023-02-08T15:48:16.49+00:00

    One way

    ;With cte1 As

    (Select t1.CHDRNO, t1.RSKNO, Count(*) As tbl1Cnt

    From @tb1 t1

    Group By t1.CHDRNO, t1.RSKNO),

    cte2 As

    (Select t2.CHDRNO, t2.RSKNO, Count(*) As tbl2Cnt

    From @tb2 t2

    Group By t2.CHDRNO, t2.RSKNO)

    Select t1.CHDRNO, t1.RSKNO, t1.ENDNLNE, t1.DATIME

    From @tb1 t1

    Inner Join cte1 c1 On t1.CHDRNO = c1.CHDRNO And t1.RSKNO = c1.RSKNO

    Inner Join cte2 c2 On t1.CHDRNO = c2.CHDRNO And t1.RSKNO = c2.RSKNO

    Where c1.tbl1Cnt > c2.tbl2Cnt;

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jingyang Li 5,891 Reputation points
    2023-02-08T19:52:49.9233333+00:00 
     
 ;with mycte as (
Select t1.CHDRNO, t1.RSKNO, t1.ENDNLNE, t1.DATIME
,count(t1.ENDNLNE) Over(partition by t1.CHDRNO, t1.RSKNO ) cnt1
,count(t2.ENDNLNE)Over  (partition by t2.CHDRNO, t2.RSKNO ) cnt2

From @tb1 t1

left  Join @tb2 t2 On t1.CHDRNO = t2.CHDRNO And t1.RSKNO = t2.RSKNO 
And t1.ENDNLNE = t2.ENDNLNE
)

select CHDRNO,RSKNO,ENDNLNE,DATIME
 from mycte 
Where cnt1>cnt2
    0 comments