Combine two unioned tables?

Question

I have two tables that look something like this. One has "package" data and the other has "recipe" data.

Order#   Lot#    Pkg#  Time
----------------------------
188688   PVB079   19   2:34
188688   PVB079   24   3:15
188688   PVB079   18   4:08
188688   PVB079   13   5:02
188688   PVB079   14   5:40
188688   PVB079   16   6:18
188688   PVB079   15   6:48
188688   PVB079   21   7:22
188688   PVB079   17   8:12

Order#  Recipe  Version  Time
------------------------------
188688  EP01     1       2:35
188688  EP01     2       4:09
188688  EP01     3       6:49

I can UNION them together and order it by the timestamp to get something that looks like this...

Order#   Lot#    Pkg#  Time  Recipe   Version
----------------------------------------------
188688   PVB079   19   2:34   --        --
188688    --      --   2:35   EP01      1
188688   PVB079   24   3:15   --        --
188688   PVB079   18   4:08   --        -- 
188688    --      --   4:09   EP01      2
188688   PVB079   13   5:02   --        --
188688   PVB079   14   5:40   --        --
188688   PVB079   16   6:18   --        --
188688   PVB079   15   6:48   --        --
188688    --      --   6:49   EP01      3
188688   PVB079   21   7:22   --        --
188688   PVB079   17   8:12   --        --

However, I would like to combine them so that the recipe data is in the same row as the 1 package before it, and the other packages after it. My ideal result would look something like this...

Order#   Lot#    Pkg#  PkgTime  Recipe   Version   RecipeTime
---------------------------------------------------------------
188688   PVB079   19   2:34     EP01      1        2:35
188688   PVB079   24   3:15     EP01      1        2:35
188688   PVB079   18   4:08     EP01      2        4:09 
188688   PVB079   13   5:02     EP02      2        4:09
188688   PVB079   14   5:40     EP02      2        4:09
188688   PVB079   16   6:18     EP02      2        4:09
188688   PVB079   15   6:48     EP02      3        6:49
188688   PVB079   21   7:22     EP02      3        6:49
188688   PVB079   17   8:12     EP02      3        6:49

Operationally, a package is entered (timestamp) and then they get a recipe (timestamp). But sometimes the recipe doesn't change. Therefore, they use the same recipe for the following packages until the recipe changes.

Is that possible? How can I combine these two tables like this?

Answer 1

Based on Gordon Linoff answer, this seems to match your question

select p.*, r.*
from package p 
outer apply
  (select top (1) r.*
   from recipe r
   where r.rec_order = p.pkg_order and r.rec_time < isnull((
     select min(p2.pkg_time)
     from package p2
     where p2.pkg_order = p.pkg_order and p2.pkg_time > p.pkg_time       
     ), convert(datetime2, '9999-12-31'))
   order by r.rec_time desc
  ) r
order by pkg_time

Answer 2

Something like this should work:

SELECT
  package.`Order#`,
  package.`Lot#`,
  package.`Pkg#`,
  package.`Time` AS PkgTime,
  recipe.Recipe,
  recipe.`Version`,
  recipe.`Time` AS RecipeTime
FROM (
SELECT
  p.`Order#` AS keyOrder,
  MAX(IF(p.Time < r.Time, p.Time, NULL)) AS startTime,
  COALESCE(MAX(IF(p.Time < r2.Time, p.Time, NULL)), MAX(p.Time)+1 ) AS endTime,
  r.`Version` AS keyRecipeVersion,
  r.Time AS r1t,
  r2.Time AS r2t
FROM recipe AS r
LEFT JOIN recipe AS r2 ON r2.`Order#` = r.`Order#` AND r2.Version = 1 + r.Version
JOIN package AS p ON p.`Order#` = r.`Order#` -- all packages.  
GROUP BY p.`Order#`, r.`Version`
) AS k
JOIN recipe ON keyOrder = recipe.`Order#` AND keyRecipeVersion = recipe.`Version`
JOIN package ON keyOrder = package.`Order#` 
            AND package.`Time` >= startTime AND package.`Time` < endTime
            -- recipe start is 1 back package time.  
;

The idea is to distill the record you want down to one set of "key fields" that can then be re-joined back to the source tables. In this case your indexing is weak and your data isn't well organized so it's a bit tricky and less robust.

The other answer with OUTER APPLY may well be more elegant, I just like to avoid non-ANSI SQL when possible.

NOTE: This could also probably be done by wrapping the UNION with GROUP BY but I definitely wouldn't recommend it. UNION makes for messy inconsistent queries and is mostly for slapping something together quickly.

Answer 3

This has nothing to do with union. You can most easily do it using a lateral join, which in SQL Server uses apply:

select p.*, r.*
from package p outer apply
     (select top (1) r.*
      from recipe r
      where r.order# = p.order# and r.time > p.time
      order by r.time asc
     ) r;

A lateral join is like a correlated subquery that can return multiple columns and multiple rows (although in this case, it is only returning one row).