I have 4 identical tables (split by years in the download) that are concatenated. They are basically Order Information with dates (order date, shipped date, invoiced date, etc).
What I want to do is, after loading and concatenating the 4 tables, do a couple of extra columns that calculate the time between dates as well as create a flag if the order shipped after it was supposed to.
I know that I can put these 5 or so calculations in each load statement but that doesn't seem to be the most efficienct. I tried doing something like:
order date - shipped date as lag_time,
x - y as z,
However, that doesn't seem to work well as it creates syn1 joins and an extra table. I thought about joining the resident table by using order numbers but that doesn't seem to work correctly as some orders have multiple lines.