I have a difficult problem. I am developing a web application (.net (C #)) that accepts a specific excel file and exports a delimited file with the specifications described below. Here is the original to import:
Loan_ID |Owner_Id | RelPerson_Id | Loan_Class ------------------------------------------------ L131231 | 1234 | 5678 | 0 L135148 | 2345 | 6789 | 2 L417128 | 1234 | 5432 | 1 L271237 | 5678 | 1112 | 5 L213781 | 2345 | 1113 | 4 L098932 | 1117 | 6789 | 6 L012213 | 1122 | 3311 | 2 L398721 | 2221 | 1112 | 3 L098766 | 5552 | | 1
Required Conclusion:
Cust_ID | Cust_class | Gr_ID | Gr.Members | Gr_Class --------------------------------------------------------------------- 1234 | 1 | 1 | (1234,5678,5432,1112,2221) | 5 2345 | 4 | 2 | (2345,1117,6789,1113) | 6 5678 | 5 | 1 | (1234,5678,5432,1112,2221) | 5 1117 | 6 | 2 | (2345,1117,6789,1113) | 6 1122 | 2 | 3 | (1122,3311) | 2 6789 | 6 | 2 | (2345,1117,6789,1113) | 6 5432 | 1 | 1 | (1234,5678,5432,1112,2221) | 5 1112 | 5 | 1 | (1234,5678,5432,1112,2221) | 5 1113 | 4 | 2 | (2345,1117,6789,1113) | 6 3311 | 2 | 3 | (1122,3311) | 2 2221 | 3 | 1 | (1234,5678,5432,1112,2221) | 5 5552 | 1 | 4 | (5552) | 4
So, I will explain the case for you to understand. In the excel file, the first column represents the loan with a unique identifier (no duplicates), the second column represents the loan owner, which is the identifier to identify the customer (Customer_Id). The related person identifier is also Customer_Id, which is the guarantor of the owner in this loan. A client can have 2 or more loans and can be a guarantor in two or more other loans. A guarantor may be a guarantor in two or three loans, but may also be the owner of another loan. Thus, owner_id and Rel_PersonId can have duplicates, and Rel_PersonId can have empty values. The class column is the rating or classification of the Credit ID (based on the days of delay).
From this file I want to get the above output. Cust_id is a separate Client Identifier that will be borrowed from both columns, Owner and Relative Persons, and it will be assigned the highest class (Column: Cust_Class) of the loan in which it enters (whether it is the owner or surety). I already found a solution for the first two columns of the output I'm stuck with grouping. Combination logic: two clients participating in one loan are grouped together, but if one of these clients participates in another loan (as an owner or guarantor) with another Cutomer X, and in the other with another client Y both clients will be in a group with the first two, and if client x or y participates in other loans with other clients z, and they are added to the first group along with the others, and this cycle continues until the client is no longer associated with those who are already in the group . See the desired result for understanding. The group identifier will be generated automatically and sequentially (1,2,3,4). The group class will be the customer class with the highest group class Cust_class. I am using sql server to import excel.Any idea on how to build a query to get this output or any other solutions? Hope you can understand, feel free to ask for further clarification. Thanks.