Good day everyone!

I do not think how to approach the task.

There is a data table with the fields report_date (reporting date), inn (employer), client_id (employee), for simplicity, we assume that report_date takes 2 values ​​- 01/01/2018 and 01.12.2017.

It is necessary to withdraw employers (inn), who changed the TIN, i.e. withdraw old and new inn.

One organization can be absorbed by another. At the same time, the TIN of the absorbed company changes (for one period) to the TIN of the absorbed company. Determine the TIN of the absorbed companies, and the companies that absorbed them. Those. In this unloading you need to find the changes that occurred in January compared with December.

There is a rule that a company is considered to have changed the TIN, if at least 90% of the employees in the company change the TIN next month, while the former TIN in the next reporting period will not have a single employee.

    1 answer 1

    For a basis, you can take something like this:

    with inn_in_period as( select report_date, inn, count(1) all_client from data group by report_date, inn ), lost_comp as( select d_old.* from inn_in_period d_old left join inn_in_period d_new on d_new.report_date=dateadd(month, 1, d_old.report_date) and d_new.inn=d_old.inn where d_new.inn is null ) select l.report_date, d.inn as old_inn, n.inn as new_inn, count(1) cnt, l.all_client*0.9 need_cnt from lost_comp l, data d, data n where d.report_date=l.report_date and d.inn=l.inn and n.report_date=dateadd(month, 1, d.report_date) and n.client_id=d.client_id group by l.report_date, l.all_client, d.inn, n.inn having count(1)>=l.all_client*0.9

    We get the TIN lists for periods with the number of employees (all_client) in them. Let's call this sample inn_in_period .

    Then we get those TINs that were in the same period and are absent in a month ( lost_comp ).

    In the final request, we take all the employees of the disappeared TIN and look at where they were next month. Group up the TIN and get passed in this direction. We verify the number of people transferred in the group with the number of employees of the "missing" TIN.