DayOfWeek 7 is ambiguous in SQL Server, since SQL allows the week to start on any day that you specify.

Instead, I'd strongly urge you to use known day values.  SQL's base date is day 0 / 1900-01-01, a Monday.  Therefore, a days displacement from that date can be used to determine day of week while ignoring current SQL date settings.

My best guess is that your code is adjusting Saturday back one day and Sunday back two days, if not, adjust the code below as needed.  0=Mon,6=Sun.  The CTE is just for initial use; after a while, you won't need it.


;WITH days AS
(
  SELECT 5 AS Saturday, 6 AS Sunday
)
...
CASE
WHEN DATEDIFF(DAY, '19000101', convert(date,kp_aspirationalduedate)) % 7 = Saturday THEN ...
WHEN DATEDIFF(DAY, '19000101', convert(date,kp_aspirationalduedate)) % 7 = Sunday THEN ...
ELSE ... END AS "DAYS"