I got a test task

There are 2 tables of the following content: Table A:

Референс договора на кредитную карту REP_REF (тип поля текстовый) Бранч (код отделения) договора ECA_BRNM (тип поля текстовый) Логин сотрудника, за которым закреплен договор LDAP_EXPERT (тип поля текстовый)

Table B:

 Логин сотрудника LDAP_LOGIN (тип поля текстовый) Бранч (код отделения) сотрудника ECA_BRNM (тип поля текстовый) Код бизнеса сотрудника BUS_BUS (тип поля числовой) Код должности сотрудника POSIT_POS (тип поля текстовый)

Task: Select all contracts that are assigned to an employee working on a brunch that does not match the branch of the contract or if the specified employee is not in table B. Find in table B the employee who works on the brunch of the contract. ------- in which there is one of the conditions

4) if several employees of the above-mentioned businesses are found on the brunch, then distribute the agreements evenly between them

I try to use

 COUNT(DISTINCT LDAP_EXPERT) AS exp_cnt FROM A into #EXP_COUNT IF (#EXP_COUNT.exp_cnt ) > 1 NTILE(2) OVER(PARTITION BY REP_REF ORDER BY LDAP_EXPERT) gr FROM BUS_BUS ORDER BY *********??.

but it seems to me that this is total murk. How to use this function for this condition? or you can do simple things

logically, should work like this

you need to count the number of contracts and the number of employees, between which you need to distribute them further, number the contracts in a row (from 1 to the number of employees) and number the employees further to the table with the contracts, join the table with the employees on the condition that the number of the contract coincides with the number of the employee

help make a request

  • You IF count () is not needed at all. If the request is able to distribute the contract for N employees, then he will also hang up all the contracts for N = 1 employees. And try to write examples in more detail, without the words "select" and semicolons, you are far from immediately aware that these are pieces of individual TSQL instructions. - Mike

1 answer 1

I did not understand how to use NTILE, or rather how to correctly convey to it the necessary amount received in the same request. And I decided to take records with the same balances from dividing the record number in the context of contracts and in the context of employees by the number of employees. It is also not clear from the question which field is the primary key in the contract table, wrote the ID, replace with the fields you need in the selection.

 select id,LDAP_LOGIN from ( select A.id,B.LDAP_LOGIN, row_number() over(partition by A.id order by B.LDAP_LOGIN) rn, row_number() over(partition by B.LDAP_LOGIN order by A.id) rn2, count(distinct B.LDAP_LOGIN) over() cnt from A, B where A.ECA_BRNM=B.ECA_BRNM and not exists(select 1 from B as B1 where B1.LDAP_LOGIN=A.LDAP_LOGIN and B1.ECA_BRNM=A.ECA_BRNM ) ) where mod(rn2,cnt)=mod(rn,cnt)

not exists in the request to take those contracts for which the wrong employees are assigned. What you said at the beginning of the task, but you omit it in the final wording, how would you do it.

What in the question means “the above-mentioned businesses” is not at all clear, because nothing like this has been said above. If necessary, add the necessary additional conditions.

PS the request was written for Oracle, all used functions are in Sybase. You may need to write something inside the empty over() which for Oracle means a window the size of the entire sample.

  • I also tried this SELECT t1.ECA_BRNM, t1.LDAP_LOGIN, t2.REP_REF INTO #RESULT FROM #BRANCH_SOTR_NUMBERED t1 LEFT JOIN #BRANCH_REF_NUMBERED t2 ON t2.ECA_BRNM = t1.ECA account in the account of the current account in the account of the return rate of the return rate (3). Your code is very elegant for me, I am a person with no work experience) It’s a pity there is no access to the database to test it, but I don’t understand it yet. - tews
  • @tews Well, such a request will give entries with the same department and a certain number, and plus, for some reason, select employees for whom there were no contracts at all. In Sybase, these tables with # are some temporary tables that you filled in with previous queries? - Mike
  • @tews And as for not exists, in Russian if it is read out like "such entries in contracts for which there is no entry in the table of employees with the same login and department", i.e. equivalent to your "all contracts that are assigned to an employee working on a brunch that does not match the contract brunch or if the specified employee is not in table B" - Mike
  • Here, apparently, you can’t say thanks. I haven’t figured out how to like you, but you were very helpful - tews
  • Yes, temporary tables are - tews