I have a calendar table with the below data which I want to add a new column to called "Business Day of Month Number". Essentially I want to take the "Day Of Month Number" column but when "Weekend Flag" = 'Y', then I do not want to count it. Is anyone able to help me with the logic for this?
Calendar Day Date Weekend Flag Day Of Month Number
01/01/2016 00:00 N 1
02/01/2016 00:00 Y 2
03/01/2016 00:00 Y 3
04/01/2016 00:00 N 4
05/01/2016 00:00 N 5
06/01/2016 00:00 N 6
07/01/2016 00:00 N 7
08/01/2016 00:00 N 8
09/01/2016 00:00 Y 9
10/01/2016 00:00 Y 10
Basically i want it to look like this:
Calendar Day Date Weekend Flag Day Of Month Number Business Day of Month Number
01/01/2016 00:00 N 1 1
02/01/2016 00:00 Y 2 null
03/01/2016 00:00 Y 3 null
04/01/2016 00:00 N 4 2
05/01/2016 00:00 N 5 3
06/01/2016 00:00 N 6 4
07/01/2016 00:00 N 7 5
08/01/2016 00:00 N 8 6
09/01/2016 00:00 Y 9 null
10/01/2016 00:00 Y 10 null
What's the underlying data source? This is pretty easy to do, but the syntax depends on the source (oracle, sql server, etc).