I have a Query where I get the WeekDay of a date but by default:
DATEPART(dw,ads.date) as weekday
You can use a formula like:
(weekday + 5) % 7 + 1
If you decide to use this, it would be worth running through some examples to convince yourself that it actually does what you want.