I have a table called Inventory, with one of the columns being report_dt, I want to add another column to the existing table called weekending_dt based on the report_dt. Report_dt : 20180108, 20180123, 20180129. Weekending_dt: 20180112, 20180126, 20180202.
If not able to add it to the same table I can make a copy of the table and add the columns to it. I need the logic to derive the Weekending_dt column. Thanks in advance your inputs are very much appreciated.
My initial approach was: I thought of creating a table with 365 rows with dates (Jan through Dec) and deriving weekending_dt in the second column manually. Then doing a lookup from the Inventory table of report_dt against the date column of the second table to pull it's corresponding weekending_dt.
I'm sure there is an easier approach. Once again appreciate all your inputs.
