Assign employees to table in PL/SQL

Hi ksummers,

I can understand your question but before i propose some solution/advice, i need to understand as to how did you arrive at the below.

in work_records table,  you have 7 records for state GA and you have two employees working in that state namely 5919 and 2335 but
1) how did you decide that first 4 records should go to 5919 and the remaining 3 records should go to 2335 ? any rationale behind this ?

OR

2)  it is just that you have 7 recoreds and 2 emps, you gave some 4 to one emp say 5919 and the remaining 3 to emp 2335

So can you answer whether you pick 1 ) or 2) from this.

sdstuber - i will try your select and let you know how it works.  i believe that will work as long as there is some sort of even distribution.

nav_kum_v - thank you for your questions.  that is what i am trying to arrive at, achieving some sort of even distribution in my sql.  if two employees are assigned the same state, then i need to divide the assignments up some sort of way.  it does not need to be a perfect distribution.  

yes it does an even distribution, or as even as it can.  If you have 2 employees and 5 locations, one of them will get more work.

sdstuber - when i run your sql, i am getting more records than expected.  there are additional fields that i did not include in myproblem.  where i should i include them in your sql?  Here is my SQL, obviously a bit longer.

SELECT w.state_abbreviation, e.employee_key
  FROM (SELECT state_abbreviation,
               employee_key,
               ROW_NUMBER() OVER(PARTITION BY state_abbreviation ORDER BY employee_key) rn,
               COUNT(*) OVER(PARTITION BY state_abbreviation) cnt
          FROM ccsd_assignment) e,
       (SELECT /*+index (dp XIE4DELQ_PARCEL)*/
        distinct sl.state_abbreviation,
                 lpp.payee_code,
                 jl.zc_jurisdiction_ldesc,
                 a.ParcelCount,
                 ROW_NUMBER() OVER(PARTITION BY sl.state_abbreviation ORDER BY sl.state_abbreviation) rn
        -- NVL (a.ParcelCount - NVL (b.assignedloans, 0), 0) "Unassigned Count"
          FROM state_list sl,
               lender_payee_profile lpp,
               zc_jur_list jl,
               lender len,
               (SELECT lpp2.payee_code,
                       COUNT(DISTINCT dp.delq_parcel_key) ParcelCount
                  FROM delq_parcel          dp,
                       lender_payee_profile lpp2,
                       loan                 l,
                       lender               len2,
                       lss_tax_line         lss
                 WHERE nvl(l.inactive_ind, 'NO') = 'NO'
                   AND NVL(len2.ccsd_research_ind, 'NO') = 'YES'
                   AND dp.lender_payee_code = lpp2.lender_payee_code
                   and lpp2.LENDER_NUMBER = 693
                   and nvl(lpp2.inactive_ind, 'NO') = 'NO'
                   and l.LENDER_NUMBER = 693
                   and nvl(len2.inactive_ind, 'NO') = 'NO'
                   AND dp.delq_loan_key = l.delq_loan_key
                   AND l.LENDER_NUMBER = 693
                   AND l.loan_number = lss.loan_number
                   AND dp.lender_payee_code = lss.lender_payee_code
                   AND dp.payee_parcel_number = lss.payee_parcel_number
                   AND dp.delq_letter_status_mldesc =
                       'Cycle Complete - Still Delinquent'
                   AND nvl(dp.adverse_action_date, sysdate + 91) >
                       sysdate + 90
                   AND nvl(dp.DELQ_VERIFIED_IND, 'NO') = 'YES'
                   AND nvl(dp.TAX_ALL_PAID_IND, 'NO') = 'NO'
                   and nvl(dp.NEXT_ACTION_TYPE_MLDESC, 'zzz') <>
                       'No Action Taken'
                   and nvl(dp.NEXT_ACTION_TYPE_MLDESC, 'zzz') <>
                       'Paid - Without forced escrow'
                   and nvl(dp.NEXT_ACTION_TYPE_MLDESC, 'zzz') <>
                       'Paid - With Forced Escrow'
                   and nvl(dp.NEXT_ACTION_TYPE_MLDESC, 'zzz') <>
                       'Paid - Without Forced Escrow'
                   and dp.CCSD_COMPLETED_DATE is null
                   AND dp.CCSD_ASSIGNED_EMPLOYEE_KEY is null
                 GROUP BY lpp2.payee_code) a
         WHERE a.payee_code = lpp.payee_code
           and NVL(len.ccsd_research_ind, 'NO') = 'YES'
           AND lpp.LENDER_NUMBER = 693
           and nvl(len.inactive_ind, 'NO') = 'NO'
           AND lpp.payee_code = jl.zc_jurisdiction_code
           and sl.FIPS_STATE_CODE = jl.FIPS_STATE_CODE) w
 WHERE e.state_abbreviation = w.state_abbreviation
   AND MOD(w.rn, e.cnt) = e.rn - 1

depends on what output you are expecting.  please post "create table" ddl for your objects with sample data and expected output.  It will be MUCH more helpful if you post it as text rather than images, that way we can use what you post directly rather than retyping it all in ourselves.  Not only is it more efficient but less error prone.

yes, i agree.  i guess my question is maybe this is happening because i am not including fields in the row_num rn field.  the second set of sql "w" SHOULD return 221 records for me, but it is returning 1422 records when the row_num is included.

probably you want to do the numbering after you do distinct...

if so, change your "w" view to to be this...

this is still somewhat of a guess, if you provide the ddl and sample data, I can build the test case and confirm

(SELECT state_abbreviation,
        payee_code,
        zc_jurisdiction_ldesc,
        parcelcount,
        ROW_NUMBER() OVER (PARTITION BY sl.state_abbreviation ORDER BY sl.state_abbreviation) rn
   FROM (SELECT                                                      /*+index (dp XIE4DELQ_PARCEL)*/
               DISTINCT
                sl.state_abbreviation, lpp.payee_code, jl.zc_jurisdiction_ldesc, a.parcelcount
           -- NVL (a.ParcelCount - NVL (b.assignedloans, 0), 0) "Unassigned Count"
           FROM state_list sl,
                lender_payee_profile lpp,
                zc_jur_list jl,
                lender len,
                (  SELECT lpp2.payee_code, COUNT(DISTINCT dp.delq_parcel_key) parcelcount
                     FROM delq_parcel dp,
                          lender_payee_profile lpp2,
                          loan l,
                          lender len2,
                          lss_tax_line lss
                    WHERE NVL(l.inactive_ind, 'NO') = 'NO'
                      AND NVL(len2.ccsd_research_ind, 'NO') = 'YES'
                      AND dp.lender_payee_code = lpp2.lender_payee_code
                      AND lpp2.lender_number = 693
                      AND NVL(lpp2.inactive_ind, 'NO') = 'NO'
                      AND l.lender_number = 693
                      AND NVL(len2.inactive_ind, 'NO') = 'NO'
                      AND dp.delq_loan_key = l.delq_loan_key
                      AND l.lender_number = 693
                      AND l.loan_number = lss.loan_number
                      AND dp.lender_payee_code = lss.lender_payee_code
                      AND dp.payee_parcel_number = lss.payee_parcel_number
                      AND dp.delq_letter_status_mldesc = 'Cycle Complete - Still Delinquent'
                      AND NVL(dp.adverse_action_date, SYSDATE + 91) > SYSDATE + 90
                      AND NVL(dp.delq_verified_ind, 'NO') = 'YES'
                      AND NVL(dp.tax_all_paid_ind, 'NO') = 'NO'
                      AND NVL(dp.next_action_type_mldesc, 'zzz') <> 'No Action Taken'
                      AND NVL(dp.next_action_type_mldesc, 'zzz') <> 'Paid - Without forced escrow'
                      AND NVL(dp.next_action_type_mldesc, 'zzz') <> 'Paid - With Forced Escrow'
                      AND NVL(dp.next_action_type_mldesc, 'zzz') <> 'Paid - Without Forced Escrow'
                      AND dp.ccsd_completed_date IS NULL
                      AND dp.ccsd_assigned_employee_key IS NULL
                 GROUP BY lpp2.payee_code) a
          WHERE a.payee_code = lpp.payee_code
            AND NVL(len.ccsd_research_ind, 'NO') = 'YES'
            AND lpp.lender_number = 693
            AND NVL(len.inactive_ind, 'NO') = 'NO'
            AND lpp.payee_code = jl.zc_jurisdiction_code
            AND sl.fips_state_code = jl.fips_state_code))

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:

what do you mean ddl?  i will continue playing with this sql.  i am getting close!

ddl is data definition language.

the "sql" commands to create the objects you are working with.

for example....

create table mytable (
 col1 number,
 col2 date,
 col3 varchar2(20)
)

do you need further assistance?

yes, i got sidetracked.  i will be working on this today and tomorrow.  i will make some comments here soon.

sdstuber - i looked back over your SQL and did some tests here.  i had the counts wrong last time we corresponded.  it seems to work perfect, thank you!  i will award you the points.  if i have anymore questions, i will contact you here.  thanks again!