SQL Server: Returning rows with multiple and distinct values

Question

I've been working on this issue for the last day and a half and just can't seem to find another question on here that works for my code.

I have a table here:

Table_D

Policynumber|   EntryDate   |  BI_Limit   | P remium
------------------------------------------------------
ABCD100001  |   5/1/16      |   15/30     | 919
ABCD100001  |   5/13/16     |   15/30     | 1008 
ABCD100002  |   5/24/16     |   100/300   | 1380
ABCD100003  |   5/30/16     |   25/50     | 1452
ABCD100003  |   6/2/16      |   25/50     | 1372
ABCD100003  |   6/4/16      |   30/60     | 951
ABCD100004  |   6/11/16     |   100/300   | 1038
ABCD100005  |   6/22/16     |   100/300   | 1333
ABCD100005  |   7/2/16      |   50/100    | 1208
ABCD100006  |   7/10/16     |   250/500   | 1345
ABCD100007  |   7/18/16     |   15/30     | 996

in which I'm trying to extract rows in which a policynumber has multiple listings and a different BI_Limit. So the output should be:

Output

Policynumber    | EntryDate  |  BI_Limit  | Premium
---------------------------------------------------
ABCD100003      | 5/30/16    |  25/50     | 1452
ABCD100003      | 6/2/16     |  25/50     | 1372
ABCD100003      | 6/4/16     |  30/60     | 951
ABCD100005      | 6/22/16    |  100/300   | 1333
ABCD100005      | 7/2/16     |  50/100    | 1208

I'm storing Policynumber as VARCHAR(Max), EntryDate as DATE, BI_Limit as VARCHAR(Max), and Premium as INTEGER.

The code I've want to say should work would be something along the lines of:

    SELECT * FROM Table_D
    WHERE BI_Limit IN (
        SELECT BI_Limit
        FROM Table_D
        GROUP BY BI_Limit
        HAVING COUNT(DISTINCT BI_Limit)>1);

But this returns nothing for me. Can anyone help to show me what I'm doing wrong? Thank you.

uzi · Accepted Answer · 2018-04-23 02:34:10Z

3

You could also try exists

select a.*
from Table_D a
where
    exists (
        select 1
        from Table_D b
        where a.Policynumber = b.Policynumber
              and a.BI_Limit <> b.BI_Limit
    )

answered Apr 23, 2018 at 2:34

uzi

4,1561 gold badge18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Squirrel · Accepted Answer · 2018-04-23 02:27:44Z

0

SELECT d.*
FROM   (   -- find the policy number with multiple listing and diff BI_Limit
           SELECT PolicyNumber
           FROM   TableD
           GROUP BY PolicyNumber
           HAVING   count(*) > 1
           AND      MIN (BI_Limit) <> MAX (BI_Limit)
       ) m -- join back the Table_D to for other information
       INNER JOIN Table_D d 
       ON  m.PolicyNumber = d.PolicyNumber

answered Apr 23, 2018 at 2:27

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Collectives™ on Stack Overflow

SQL Server: Returning rows with multiple and distinct values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related