[SQL] Sql For each row with a particular status ,check all previous records with the same id...

I have data like:

`Empid Transaction_id Trans_status
12 1124 Internal
12 1243 Cash
12 1125 Credit
12 1123 Debit
15 1224 External
15 1226 Chargeback
15 1227 Thirdpartyclient`


1st condition: I have to check for each row with 'trans_Status' = "Internal", check all previous records with the same 'Emp_id' , if one of them is with 'trans_Status' type= "Credit" - the value will be 1 else 0 (create a new column trans_warning )

2nd condition: For each row with 'Trans_Status' type = "External", check all previous records with the same 'Emp_id'. if one of them is with 'trans_Status' type= "Chargeback" - the value will be 1. Else-the value will be 0 (new column loan_warning) **All the rows with 'Trans_Status' type = "External" - the value is NULL (The logic above is irrelevant for them).

Exepcted Output: `

Empid Transaction_id Trans_status Trans_warning Loan_warning
12 1124 Internal 0 0
12 1243 Cash 0 0
12 1125 Credit 1 0
12 1123 Debit 0 0
15 1224 External 0 0
15 1226 Chargeback 0 1
15 1227 Thirdpartyclient 0 0


`

Continue reading...