2 Replies Latest reply on Nov 14, 2018 9:14 AM by Martin Ocando

    Join within a date range

    Martin Ocando

      I'm trying to join a couple of tables using data blending, based on a date range that I filter on the LEFT table. If I use DAY(date), MONTH(date), and YEAR(date), all records which have the same date will be shown, but those records from the RIGHT table that doesn't have a corresponding record on the LEFT table, are not retrieved.

       

      Tables are on different DB systems (SQL Server & MySQL), so I can't union them. Since I'm using data blending, I can't do full outer.

       

       

      For example

       

      Left Table:

      Employee IDIncident DateCauseHours
      13012018-09-01TAR3
      13012018-09-05DES2
      13012018-09-09TAR1

       

      Right Table:

      Employee IDIncident DatePersonal Event
      13012018-09-01Arrived late
      13012018-09-03Absent
      13012018-09-09Retired early

       

      If I join those tables on Employee and Incident Date, I get the following:

       

      Employee IDIncident DateCauseHoursPersonal Event
      13012018-09-01TAR3Arrived late
      13012018-09-05DES2NULL
      13012018-09-09TAR1Retired early

       

      So, row #2 from the RIGHT table was completely ignored, since there is no corresponding record on the LEFT table.

       

      Any ideas on how I can do this?

       

      Thanks

        • 1. Re: Join within a date range
          Mavis Liu

          Hi Martin,

           

          Can you use a cross data base join? Or maybe create a master file with ALL the incident dates and use this as your primary data source for blending.

           

          Thanks,

           

          Mavis

          • 2. Re: Join within a date range
            Martin Ocando

            Cross DB join won't allow full outer. I'll try creating a master table and try to use it as a join source. Although I'm trying to avoid any external ETLs or another table to keep up to date, but if there is no other way, it might be the solution.

             

            I'll report back if it works.

             

            Thanks

             

            UPDATE: Is not working. Employee ID is part of the key, and since I'm joining both tables with the master date table, and I don't have the ID on that one, is mixing everyone and it will be impossible to count records from one table or the other. The solution will be a union, but looks like building a 3rd table with an ETL engine that can union those together is the only viable solution.

            The problem with that is that I don't want to duplicate the data on a 3rd table, and I don't want to have to keep an eye on it, running the ETL on a regular basis to keep it updated, etc. Is an administrative nightmare.

             

            Can't believe there is no other way.