How to create a flag based on partitioned column values in SQL

Question

I have a table with 2 columns ,So for each of the loans Either Letter A, Letter B or both of them is being sent to the Loan applicant.

Base Table I need to create a new column that sets a Flag =1 ( If Letter A is sent ) , 2 ( If letter B is sent) and 3 ( If both the Letter A and Letter B is sent) as below

Result Table

I have tried a method to generate a row number for each loan for the specific letter as below. I need to then find those rows where the no of times the ROW_NUM =1 is GREATER THAN 1 and assign a flag

SELECT *, 
       ROW_NUMBER() OVER(PARTITION BY LOAN_NO, Letter ORDER BY LOAN_NO) AS ROW_NUM
FROM TableA

Can someone help me to proceed in this method or any alternative method to set the flags.

What if a non LetterA/LetterB is sent? What is the flag value then? — Bart McEndree
– Bart McEndree, Commented Jul 16, 2024 at 18:26
Don't use images for data. Simply write your data in proper columns. highlight and click {}. — jarlh
– jarlh, Commented Jul 16, 2024 at 18:29
Which dbms are you using? My answer isn't supported by all products. — jarlh
– jarlh, Commented Jul 16, 2024 at 18:42

Bart McEndree · Accepted Answer · 2024-07-16 18:47:35Z

0

One solution uses a CTE:

WITH CTE as
(  
SELECT LOAN_NO, Count(*) as LoanCount, Max(Letter) as MaxLetter
FROM TableA
GROUP BY LOAN_NO
)
SELECT A.LOAN_NO, A.Letter, 
       CASE LoanCount
       WHEN 1 THEN
          CASE MaxLetter 
          WHEN 'LetterA' THEN 1
          WHEN 'LetterB' THEN 2
          ELSE 0 END
       ELSE 3 END as Flag
          
FROM CTE C
INNER JOIN TableA A ON A.LOAN_NO=C.LOAN_NO

SQL-Server fiddle

LOAN_NO	Letter	Flag
123	LetterA	3
123	LetterA	3
123	LetterB	3
234	LetterA	1
345	LetterB	2

edited Jul 16, 2024 at 18:47

answered Jul 16, 2024 at 18:28

Bart McEndree

3,6041 gold badge12 silver badges20 bronze badges

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

5 Comments

Amrit Over a year ago

Thanks for sharing your idea . I have tried it but all the accounts are returning the value 3 (generating both Letter A and Letter B)

Bart McEndree Over a year ago

@Amrit Please verify that your Letter column values match 'LetterA' and 'LetterB'

Amrit Over a year ago

Yes it matches but for all the records the Flag column is returning value 3 even though there are instances where 1 and 2 should appear

Amrit Over a year ago

So why are you keeping the CASE condition to be loanCount is 1 beause for each of loans there can be multiple Letters like 234 Loan can also have 2 instances of Letter A

Amrit Over a year ago

I think keeping the condition of CASE WHEN loancount =1 is failing the records . There are instances in the table where a loan 234 will have multiples instances of sending Letter A itself . So how can we tackle that by modifying the code . I uploaded the example instance in Base Table

Bart McEndree · Accepted Answer · 2024-07-16 19:02:43Z

0

Using Dense_Rank() as per Partition Function COUNT() OVER possible using DISTINCT

SELECT *,
CASE dense_rank() over (partition by [LOAN_NO] order by [Letter]) 
+ dense_rank() over (partition by [LOAN_NO] order by [Letter] desc) 
- 1 WHEN 1 THEN
   CASE Letter
   WHEN 'LetterA' THEN 1
   WHEN 'LetterB' THEN 2
   ELSE 0 END
  ELSE 3 
  END
  AS Flag  
FROM TableA

SQL-Server fiddle

LOAN_NO	Letter	Flag
123	LetterA	3
123	LetterA	3
123	LetterB	3
234	LetterA	1
345	LetterB	2

answered Jul 16, 2024 at 19:02

Bart McEndree

3,6041 gold badge12 silver badges20 bronze badges

Comments

jarlh · Accepted Answer · 2024-07-31 19:20:21Z

0

Use SUM() window function, and use a case expression to sum the distinct values 1 or 2 depending on LetterA or LetterB. Partition by LOAN_NO.

SELECT loan_no, letter, 
       SUM(DISTINCT CASE Letter WHEN 'LetterA' THEN 1
                                WHEN 'LetterB' THEN 2
                         ELSE 0 END)
           OVER (PARTITION BY LOAN_NO) flag
FROM TableA

Demo: https://dbfiddle.uk/xbcneWCO (Oracle used here)

SQL>create table tablea (loan_no int, letter varchar(10));
SQL>--
SQL>insert into tablea values
SQL&  (123, 'LetterA'),
SQL&  (123, 'LetterA'),
SQL&  (123, 'LetterB'),
SQL&  (234, 'LetterA'),
SQL&  (345, 'LetterB');

                  5 rows inserted

SQL>--
SQL>SELECT loan_no, letter,
SQL&       SUM(DISTINCT CASE Letter WHEN 'LetterA' THEN 1
SQL&                                WHEN 'LetterB' THEN 2
SQL&                         ELSE 0 END)
SQL&           OVER (PARTITION BY LOAN_NO) flag
SQL&FROM TableA;
    loan_no letter                     flag
=========== ========== ====================
        123 LetterA                       3
        123 LetterA                       3
        123 LetterB                       3
        234 LetterA                       1
        345 LetterB                       2

                  5 rows found

(Mimer SQL used here)

ANSI/ISO SQL-2023 compliant, using the optional features:

F561, Full value expressions
T611, Elementary OLAP operations

edited Jul 31, 2024 at 19:20

answered Jul 16, 2024 at 18:36

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

2 Comments

Bart McEndree Over a year ago

I tried this and got an error: Use of DISTINCT is not allowed with the OVER clause.

jarlh Over a year ago

Which dbms are you using?

Collectives™ on Stack Overflow

How to create a flag based on partitioned column values in SQL

3 Answers 3

5 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related