I have a SQL table that keeps information about monthly Transactions by Day of Week as opposed to actual date. Below is an example:
As you can see, the day of month in the Month column is always 01.
I need to add a 3rd column for a new Date value that is dependent on the value of both Month and DOW columns. Basically, I need to convert day value from the generic 01 value to a value between 1-7:
The new date will be determined according to the Month and DOW values, so for example:
Month DOW New Date
01-Jan-2014 Mon 06-Jan-2014
01-Jan-2014 Tue 07-Jan-2014
01-Jan-2014 Sun 05-Jan-2014
01-Feb-2014 Mon 03-Feb-2014
01-Feb-2014 Tue 04-Feb-2014
01-Dec-2014 Mon 01-Dec-2014
01-Dec-2014 Tue 02-Dec-2014
Ideally, the new column should be added to the Select statement as condition or CASE statement, based on the values in Month and DOW.
Many thanks in advance.