Looking to generate new rows for roles missing in initial query

user_7067536 0
Viewed 30

I have a current set of data that looks like the following:

Table Name: Project_EffectiveDating

ProjectCode RoleCode EffectiveDate
1a1 A 1/1/2020
1a1 B 1/1/2020
1a1 C 1/1/2020
1a1 D 1/1/2020
1a1 E 1/1/2020
1a1 A 2/1/2020
ProjectCode RoleCode EffectiveDate
1a1 A 1/1/2020
1a1 B 1/1/2020
1a1 C 1/1/2020
1a1 D 1/1/2020
1a1 E 1/1/2020
1a1 A 2/1/2020
1a1 B 2/1/2020
1a1 C 2/1/2020
1a1 D 2/1/2020
1a1 E 2/1/2020

Where the top table is existing data and the bottom table is the desired output.

I have been trying to join the table on itself with the following:

select 
  dbd.projectcode
, dbd.role
, dbd.effectivedate
from Project_EffectiveDating as dbd 
left join Project_EffectiveDating as dad 
     on dad.projectcode = dbd.projectcode 
     and dbd.rolecode <> dad.rolecode

but obviously this is not working. I was thinking about doing a union statement using a WHERE NOT IN() but am at a loss. Any help would be appreciated.

sql-server-2008 sql
1 Answers

You seem to want a Cartesian product between pairs of ProjectCode/RoleCode and EffectiveDate:

select *
from (select distinct ProjectCode, RoleCode
      from Project_EffectiveDating
     ) pr cross join
     (select distinct EffectiveDate
      from Project_EffectiveDating
     ) e;

If you want to add in the rows that don't exist:

insert into Project_EffectiveDating (ProjectCode, RoleCode, EffectiveDate)
    select pr.ProjectCode, pr.RoleCode, e.EffectiveDate
    from (select distinct ProjectCode, RoleCode
          from Project_EffectiveDating
         ) pr cross join
         (select distinct EffectiveDate
          from Project_EffectiveDating
         ) e left join
         Project_EffectiveDating ped
         on ped.ProjectCode = pr.ProjectCode and
            ped.RoleCode = pr.RoleCode and
            ped.EffectiveDate = pr.EffectiveDate
    where ped.ProjectCode is null;
user_11440350
Related