Inner join 2 tables on multiple conditions

[amber2000]

Hi everyone,

I would like to create an Inner Join between table Fact_PickingAssignments: and table TimeRegistration:

The Inner Join is written in the table TimeRegistration and when I debug I get the Error: Ambiguous Column Name D_SK_Employee.

Can somenone see what I'm doing wrong here please? This is the first time I use a join and I have to say that table1(Fact_PickingAssignments) is a big table so I don't want to slow down performance.

Calendar:

Load *,

Date(DayStart([D_SK_Date_Start]))                            As [Date_Picking],

  Year([D_SK_Date_Start])                                         As [Date_Year],

  Month([D_SK_Date_Start])                                       As [Date_Month],

  Week([D_SK_Date_Start])                                       As [Date_Week],

  WeekDay([D_SK_Date_Start])                                 As [Week_Day],

  'Q' & Ceil(Month(D_SK_Date_Start)/3,1,1)                AS [Quarter],

  Date(Monthstart([D_SK_Date_Start]),'MM-YYYY')     As [Date_MonthYear];

//**************************************************************************************************************

//                                                         Table 1                                                                     *

//**************************************************************************************************************

Fact_PickingAssignments:

LOAD

    [D_SK_PickingAssignment]                                  AS [SK_PickingAss],

    [BK_WarehouseCode]                                          AS [Warehouse_Code],

    [BK_PickingAssignmentID]                                   AS [PA_Ass_ID],

    Date(Date#([D_SK_Date_Start],'YYYYMMDD'))     AS [D_SK_Date_Start],

    Date(Date#([D_SK_Date_Start],'YYYYMMDD'))     AS [PA_TR_StartDate],

    Date(Date#([D_SK_Date_End],'YYYYMMDD'))      AS [PA_EndDate],

    Time#([D_SK_Time_Start],'hhmmss')                    AS [PA_StartTime],

    Time#([D_SK_Time_End],'hhmmss')                     AS [PA_EndTime],

    Interval([SecondsWorked] /86400)                        AS [PA_SecondsWorked],

    [D_SK_Employee]                                               AS [Emp_ID],

    [FullPallets]                                                         AS [PA_FullPallets];                            
SQL SELECT   

    [D_SK_PickingAssignment],

    [BK_WarehouseCode],

    [BK_PickingAssignmentID],

    [D_SK_Date_Start],

    [D_SK_Date_End],

    [D_SK_Time_Start],

    [D_SK_Time_End],

    [SecondsWorked],

    [D_SK_Employee],

    [FullPallets]

FROM NVL_DWH.dbo."DW_F_PickingAssignment";

//**************************************************************************************************************

//                                                         Table 2                                                                     *

//**************************************************************************************************************

TimeRegistration:

LOAD

    [D_SK_TimeRegistration]                          as TR_ID,

    [BK_DatabaseCodeGPS]                           as TR_BK_DatabaseCodeGPS,

    [BK_EmployeeID]                                     as TR_GPS_PersonnelID,

    [BK_ClockIn]                                            as TR_BK_ClokIn,

    [BK_TimeRegistrationCode]                       as TR_CD_Register,

    [TimeRegistrationSeconds]                        as TR_Num_Minutes,

    [D_SK_Date_Planning]                              as TR_Date_PlanningGPS,

    [D_SK_Date_In]                                        as TR_DateClockIn,

    [D_SK_Date_Out]                                     as TR_DateClockOut,

    [D_SK_Time_In]                                       as TR_Clock_In,

    [D_SK_Time_Out]                                    as TR_Clock_Out,

    [D_SK_Employee]                                    as Emp_ID;

SQL SELECT

  [D_SK_TimeRegistration],

    [BK_DatabaseCodeGPS],

    [BK_EmployeeID],

    [BK_ClockIn],

    [BK_TimeRegistrationCode],

    [TimeRegistrationSeconds],

    [D_SK_Date_Planning],

    [D_SK_Date_In],

    [D_SK_Date_Out],

    [D_SK_Time_In],

    [D_SK_Time_Out],

    [D_SK_Employee]

FROM "NVL_DWH".dbo."DW_F_TimeRegistration" tr

Inner join "NVL_DWH".dbo.DW_F_PickingAssignment pa

     on tr.D_SK_Employee = pa.D_SK_Employee

  and pa.D_SK_Date_Start between tr.D_SK_Date_In and tr.D_SK_Date_Out

  and pa.D_SK_Time_Start between tr.D_SK_Time_In and tr.D_SK_Time_Out

  and tr.TimeRegistrationCode = 'Totaal';

//**************************************************************************************************************

//                                                          Dimension                                                               *

//**************************************************************************************************************

Employee:

LOAD

    [D_SK_Employee]                                    AS [Emp_ID],

    Upper(Text([BK_UserName]))                    AS [Emp_Usys_UserName],

    [GPS_UserName]                                    AS [Emp_GPS_UserName],

    [ID_Employee_GPS]                                AS [Emp_GPSPersonnelID],

    [Active]                                                   AS [Emp_UsysActive];

SQL SELECT

   [D_SK_Employee],

   [BK_UserName],

   [GPS_UserName],

   [ID_Employee_GPS],

   [Active]

FROM NVL_DWH.dbo."DW_D_Employee";

Kind regards,

Monique

[Anil_Babu_Samineni]

Try This

Fact_PickingAssignments:

Load * Table1;

TimeRegistration:

Load * Table2;

Left Join(TimeRegistration)

Employee:

LOAD

    [D_SK_Employee]                                    AS [Emp_ID],

    Upper(Text([BK_UserName]))                    AS [Emp_Usys_UserName],

    [GPS_UserName]                                    AS [Emp_GPS_UserName],

    [ID_Employee_GPS]                                AS [Emp_GPSPersonnelID],

    [Active]                                                   AS [Emp_UsysActive];

SQL SELECT

   [D_SK_Employee],

   [BK_UserName],

   [GPS_UserName],

   [ID_Employee_GPS],

   [Active]

FROM NVL_DWH.dbo."DW_D_Employee";

[amber2000]

Where do I use the conditions?

Why a Left Join and not an Inner join? I want only those records who have a match in both tables.

[sunny_talwar · Accepted solution]

May be you need to specify which table you are picking D_SK_EMPLOYEE from? In fact I would do this for all my fields just to be sure.

SQL SELECT

  [D_SK_TimeRegistration],

    [BK_DatabaseCodeGPS],

    [BK_EmployeeID],

    [BK_ClockIn],

    [BK_TimeRegistrationCode],

    [TimeRegistrationSeconds],

    [D_SK_Date_Planning],

    [D_SK_Date_In],

    [D_SK_Date_Out],

    [D_SK_Time_In],

    [D_SK_Time_Out],

    [pa.D_SK_Employee] or [tr.D_SK_Employee]

FROM "NVL_DWH".dbo."DW_F_TimeRegistration" tr

Inner join "NVL_DWH".dbo.DW_F_PickingAssignment pa

    on tr.D_SK_Employee = pa.D_SK_Employee

  and pa.D_SK_Date_Start between tr.D_SK_Date_In and tr.D_SK_Date_Out

  and pa.D_SK_Time_Start between tr.D_SK_Time_In and tr.D_SK_Time_Out

  and tr.TimeRegistrationCode = 'Totaal';

[amber2000]

This is exactly what I wanted to do.

Thanks a million Sunny