Duplicate values and extra rows

Question

I currently have the following query with the results on the bottom.

select 
           b.EMPLOYEE,
           FIRST_NAME,
                       LAST_NAME,
                       PL_1,
                       PL_2,
                       PL_3,
                       PL_4,
                       PL_5,
                       PL_6,
                       DUAL_EMPLOYEE,
                       Dual_Process_Level_Comparison,
           case when b.Primary_Compare_Number <> b.Supplemental_Compare_Number then 'REVIEW' else 'MATCH' end as process_level_compare
    FROM
    (
    select 
      a.EMPLOYEE, 
      m.FIRST_NAME, 
      m.LAST_NAME, 
      m.PROCESS_LEVEL as PL_1, 
      max(case when a.POS_LEVEL = 2 then a.PROCESS_LEVEL end) as PL_2, 
      max(case when a.POS_LEVEL = 3 then a.PROCESS_LEVEL end) as PL_3, 
      max(case when a.POS_LEVEL = 4 then a.PROCESS_LEVEL end) as PL_4, 
      max(case when a.POS_LEVEL = 5 then a.PROCESS_LEVEL end) as PL_5, 
      max(case when a.POS_LEVEL = 6 then a.PROCESS_LEVEL end) as PL_6, 
      case when s.[DUAL EMPLOYEE] = 'UNASSIGNED' then ' ' else s.[DUAL EMPLOYEE] end as DUAL_EMPLOYEE, 
      case when m.PROCESS_LEVEL = s.[DUAL EMPLOYEE] then 'REVIEW' end as Dual_Process_Level_Comparison, 
      case 
        when m.PROCESS_LEVEL in ('CALL') then 1 
        when m.PROCESS_LEVEL in ('ECCHS', 'HCCHS') then 2 
           when m.PROCESS_LEVEL in ('ESCH', 'HCLER', 'HSCH') then 3 
           when m.PROCESS_LEVEL in ('LPBNO', 'LSBCR', 'LSBCW', 'LTBC','LTBH', 'LTBV', 'LTCLR', 'LTCS', 'LTHC','LTMON', 'LTSBC') then 4 
           when m.PROCESS_LEVEL in ('MELRO') then 5 
           when m.PROCESS_LEVEL in ('PLONG') then 6 
           when m.PROCESS_LEVEL in ('PNSVL') then 7 
           when m.PROCESS_LEVEL in ('SAUK') then 8 
           when m.PROCESS_LEVEL in ('TMONT') then 9 
           when m.PROCESS_LEVEL in ('WACM') then 10 
           when m.PROCESS_LEVEL in ('WCRS', 'WFPMC', 'WRDWF') then 11 
      end as Primary_Compare_Number,   

      case 
        when a.PROCESS_LEVEL in ('CALL') then 1 
        when a.PROCESS_LEVEL in ('ECCHS', 'HCCHS') then 2 
           when a.PROCESS_LEVEL in ('ESCH', 'HCLER', 'HSCH') then 3 
           when a.PROCESS_LEVEL in ('LPBNO', 'LSBCR', 'LSBCW', 'LTBC', 'LTBH', 'LTBV', 'LTCLR', 'LTCS', 'LTHC', 'LTMON', 'LTSBC') then 4 
           when a.PROCESS_LEVEL in ('MELRO') then 5 
           when a.PROCESS_LEVEL in ('PLONG') then 6 
           when a.PROCESS_LEVEL in ('PNSVL') then 7 
           when a.PROCESS_LEVEL in ('SAUK') then 8 
           when a.PROCESS_LEVEL in ('TMONT') then 9 
           when a.PROCESS_LEVEL in ('WACM') then 10 
           when a.PROCESS_LEVEL in ('WCRS', 'WFPMC', 'WRDWF') then 11 
      end as Supplemental_Compare_Number
    from 
      dbo.vw_PAEMPPOS a 
      join dbo.COMPLETE_EMPLOYEE_MASTER m on m.EMPLOYEE = a.EMPLOYEE 
      join dbo.HR_EMPUSERFIELDS s on s.EMPLOYEE = m.EMPLOYEE 


    where END_DATE = '2099-12-31 00:00:00.000' 
      and EMP_STATUS NOT IN ('1A', 'RT', 'SZ', 'T1', 'XD', 'XV', 'ZZ') 
    group by 
      a.EMPLOYEE, 
      m.LAST_NAME, 
      m.FIRST_NAME, 
      m.PROCESS_LEVEL, 
      s.[DUAL EMPLOYEE], 
      a.PROCESS_LEVEL 
    --can’t have an order by here
    ) as b
                order by Dual_Process_Level_Comparison desc, process_level_compare desc

RESULTS

|EMPLOYEE|FIRST_NAME| LAST_NAME|PL_1 |PL_2 |PL_3|PL_4|PL_5|PL_6|DUAL EMPLOYEE|DUAL_PROCESS_LEVEL|process_level_compare|
+--------+----------+----------+-----+-----+----+----+----+----+-------------+------------------+---------------------+
|73202   |  TEST    | EMPLOYEE |MELRO|LSBCW|NULL|NULL|NULL|NULL|blank        |NULL              |        REVIEW       |
|73202   |  TEST    | EMPLOYEE |MELRO|NULL |NULL|NULL|NULL|NULL|blank        |NULL              |        MATCH        |

As you can see that this is a duplicate employee, it should only be showing one record. Which the record should be the top record. When I group by

a.EMPLOYEE, 
m.LAST_NAME, 
m.FIRST_NAME, 
m.PROCESS_LEVEL, 
s.[DUAL EMPLOYEE], 
a.PROCESS_LEVEL

This is the result I get. When I created this new query below it only shows one record since the a.EMPLOYEE is the only one I group by. But when you look at the output for the same employee, the process_level_compare does not say "Review'. But does now not have duplicate employees. What am I missing here to get it so I don't have any more duplicates and so that it does show 'Match' for that employee?

         select 
                   b.EMPLOYEE,
                   FIRST_NAME,
                   LAST_NAME,
                   PL_1,
                   PL_2,
                   PL_3,
                   PL_4,
                   PL_5,
                   PL_6,
                   DUAL_EMPLOYEE,
                   Dual_Process_Level_Comparison,

                   case when b.Primary_Compare_Number <> b.Supplemental_Compare_Number then 'REVIEW' else 'MATCH' end as process_level_compare
            FROM
            (
            select 
              a.EMPLOYEE, 
              Min(m.FIRST_NAME) as FIRST_NAME, 
              Min(m.LAST_NAME) as LAST_NAME, 
             MAX(m.PROCESS_LEVEL) as PL_1, 
              max(case when a.POS_LEVEL = 2 then a.PROCESS_LEVEL end) as PL_2, 
              max(case when a.POS_LEVEL = 3 then a.PROCESS_LEVEL end) as PL_3, 
              max(case when a.POS_LEVEL = 4 then a.PROCESS_LEVEL end) as PL_4, 
              max(case when a.POS_LEVEL = 5 then a.PROCESS_LEVEL end) as PL_5, 
              max(case when a.POS_LEVEL = 6 then a.PROCESS_LEVEL end) as PL_6, 
               MAX(case when s.[DUAL EMPLOYEE] = 'UNASSIGNED' then ' ' else s.[DUAL EMPLOYEE] end )as DUAL_EMPLOYEE, 
             MAX( case when m.PROCESS_LEVEL = s.[DUAL EMPLOYEE] then 'REVIEW' end) as Dual_Process_Level_Comparison, 
             MAX( case  when m.PROCESS_LEVEL in ('CALL') then 1 
                when m.PROCESS_LEVEL in ('ECCHS', 'HCCHS') then 2 
                   when m.PROCESS_LEVEL in ('ESCH', 'HCLER', 'HSCH') then 3 
                   when m.PROCESS_LEVEL in ('LPBNO', 'LSBCR', 'LSBCW', 'LTBC','LTBH', 'LTBV', 'LTCLR', 'LTCS', 'LTHC','LTMON', 'LTSBC') then 4 
                   when m.PROCESS_LEVEL in ('MELRO') then 5 
                   when m.PROCESS_LEVEL in ('PLONG') then 6 
                   when m.PROCESS_LEVEL in ('PNSVL') then 7 
                   when m.PROCESS_LEVEL in ('SAUK') then 8 
                   when m.PROCESS_LEVEL in ('TMONT') then 9 
                   when m.PROCESS_LEVEL in ('WACM') then 10 
                   when m.PROCESS_LEVEL in ('WCRS', 'WFPMC', 'WRDWF') then 11 
              end) as Primary_Compare_Number,   

              Max(case 
                when a.PROCESS_LEVEL in ('CALL') then 1 
                when a.PROCESS_LEVEL in ('ECCHS', 'HCCHS') then 2 
                   when a.PROCESS_LEVEL in ('ESCH', 'HCLER', 'HSCH') then 3 
                   when a.PROCESS_LEVEL in ('LPBNO', 'LSBCR', 'LSBCW', 'LTBC', 'LTBH', 'LTBV', 'LTCLR', 'LTCS', 'LTHC', 'LTMON', 'LTSBC') then 4 
                   when a.PROCESS_LEVEL in ('MELRO') then 5 
                   when a.PROCESS_LEVEL in ('PLONG') then 6 
                   when a.PROCESS_LEVEL in ('PNSVL') then 7 
                   when a.PROCESS_LEVEL in ('SAUK') then 8 
                   when a.PROCESS_LEVEL in ('TMONT') then 9 
                   when a.PROCESS_LEVEL in ('WACM') then 10 
                   when a.PROCESS_LEVEL in ('WCRS', 'WFPMC', 'WRDWF') then 11 
              end )as Supplemental_Compare_Number
            from 
              dbo.vw_PAEMPPOS a 
              join dbo.COMPLETE_EMPLOYEE_MASTER m on m.EMPLOYEE = a.EMPLOYEE 
              join dbo.HR_EMPUSERFIELDS s on s.EMPLOYEE = m.EMPLOYEE 


            where END_DATE = '2099-12-31 00:00:00.000' 
              and EMP_STATUS NOT IN ('1A', 'RT', 'SZ', 'T1', 'XD', 'XV', 'ZZ') 
            group by 
              a.EMPLOYEE
            ) as b
            order by Dual_Process_Level_Comparison desc, process_level_compare desc

Results:

 |EMPLOYEE|FIRST_NAME| LAST_NAME|PL_1 |PL_2 |PL_3|PL_4|PL_5|PL_6|DUAL EMPLOYEE| DUAL_PROCESS_LEVEL|process_level_compare|
 +--------+----------+----------+-----+-----+----+----+----+----+-------------+-------------------+---------------------+
 |73202   |TEST      | EMPLOYEE |MELRO|LSBCW|NULL|NULL|NULL|NULL|blank        |NULL               |        MATCH        |

The Final goal is to have one record per employee, but to show all the PL_1 through PL_6 if they have any values in there. Also to mark in the process_level_compare column 'REVIEW' if the Primary_Compare_Number is different then the Supplemental_Compare_number.

Desired Output
     +--------+----------+----------+-----+-----+----+----+----+----+-------------+-------------------+---------------------+
     |73202   |TEST      | EMPLOYEE |MELRO|LSBCW|NULL|NULL|NULL|NULL|blank        |NULL               |        REVIEW       |

Inner Query Results

|EMPLOYEE|FIRST_NAME| LAST_NAME|PL_1 |PL_2 |PL_3|PL_4|PL_5|PL_6|DUAL EMPLOYEE|DUAL_PROCESS_LEVEL|Primary_Compare_Number|Supplemental_Compare_Number|
+--------+----------+----------+-----+-----+----+----+----+----+-------------+------------------+---------------------+----------------------------+
|73202   |  TEST    | EMPLOYEE |MELRO|LSBCW|NULL|NULL|NULL|NULL|blank        |NULL              |        5            |        5                   |

VBoka · Accepted Answer

From your inner select you will only get one number for Primary_Compare_Number and one for Supplemental_Compare_Number because you have written group by a.Employee where you have selected this two values as max of PROCESS_LEVEL column.

To have two rows for the same employee you will have to group by that column(the one that has different values) which is here already done in some way in your first select. The biggest problem here is that we do not know what is your final goal... in your previous result you had two in your results so in one way you do know what to do....

So as You see you have:

case when b.Primary_Compare_Number <> b.Supplemental_Compare_Number 
     then 'REVIEW' 
     else 'MATCH' 
end as process_level_compare

AS you have given us results from your inner query that would look like this:

case when 5 <> 5    --when 5 is not equal to 5
     then 'REVIEW'  --then result is 'REVIEW'
     else 'MATCH'   --else result is 'MATCH'
end as process_level_compare --put the result in new column named process_level_compare

If you want to compare 5 to 4 then change this:

Max(case when a.PROCESS_LEVEL in ('CALL') then 1 
         when a.PROCESS_LEVEL in ('ECCHS', 'HCCHS') then 2 
         when a.PROCESS_LEVEL in ('ESCH', 'HCLER', 'HSCH') then 3 
         when a.PROCESS_LEVEL in ('LPBNO', 'LSBCR', 'LSBCW', 'LTBC', 'LTBH', 'LTBV', 'LTCLR', 'LTCS', 'LTHC', 'LTMON', 'LTSBC') then 4 
         when a.PROCESS_LEVEL in ('MELRO') then 5 
         when a.PROCESS_LEVEL in ('PLONG') then 6 
         when a.PROCESS_LEVEL in ('PNSVL') then 7 
         when a.PROCESS_LEVEL in ('SAUK') then 8 
         when a.PROCESS_LEVEL in ('TMONT') then 9 
         when a.PROCESS_LEVEL in ('WACM') then 10 
         when a.PROCESS_LEVEL in ('WCRS', 'WFPMC', 'WRDWF') then 11 
 end )as Supplemental_Compare_Number

TO THIS (the difference is only MAX to MIN):

Min(case when a.PROCESS_LEVEL in ('CALL') then 1 
         when a.PROCESS_LEVEL in ('ECCHS', 'HCCHS') then 2 
         when a.PROCESS_LEVEL in ('ESCH', 'HCLER', 'HSCH') then 3 
         when a.PROCESS_LEVEL in ('LPBNO', 'LSBCR', 'LSBCW', 'LTBC', 'LTBH', 'LTBV', 'LTCLR', 'LTCS', 'LTHC', 'LTMON', 'LTSBC') then 4 
         when a.PROCESS_LEVEL in ('MELRO') then 5 
         when a.PROCESS_LEVEL in ('PLONG') then 6 
         when a.PROCESS_LEVEL in ('PNSVL') then 7 
         when a.PROCESS_LEVEL in ('SAUK') then 8 
         when a.PROCESS_LEVEL in ('TMONT') then 9 
         when a.PROCESS_LEVEL in ('WACM') then 10 
         when a.PROCESS_LEVEL in ('WCRS', 'WFPMC', 'WRDWF') then 11 
 end )as Supplemental_Compare_Number

P.S. try to make the question as short as possible to represent the problem you have. Also some original data would be nice (without all of this columns you do not need to get to the answer you need).

Duplicate values and extra rows

Answers (1)

Related Questions