function for calculating working week SQL Server

Question

I have a similar question posted, but did not quite get the answer I needed I have the following sql code which gets built up in a c# app... This code works fine, except for when the date range is bigger than 1 working week. I need to edit the code so that when the CSIAchieved date falls into the next working week it is 'outside' of SLA. Please help! When the Achieved date is within the same week as the Target date it is 'inside' sla.

if (CSILOSDate.Length > 0) {
  sql += CSILOSDate + " as [CSITarget], " +
         CSIActualDate + " as [CSIAcheived], " +
         "CASE WHEN " + CSILOSDate + " IS NULL OR " + CSIActualDate + 
                   " IS NULL THEN 'Incomplete' " +
               "WHEN " + CSIActualDate + " 
                      <= DATEADD(DAY, 6-DATEPART(WEEKDAY, " +
                      CSIActualDate + "), " + CSIActualDate + ") " + 
                   "THEN 'Inside' " +
               "WHEN " + CSIActualDate + " <= " + CSILOSDate + 
                   " THEN 'Inside' " +
               "ELSE 'Outside' " +
          "END AS [CSIStatus] ";
}

Answer 1

First, I would suggest that you use parameterized queries.

Also, should this line:

WHEN CSIActualDate <= DATEADD(DAY, 6-DATEPART(WEEKDAY, CSIActualDate), CSIActualDate)
     THEN 'Inside'

actually be:

WHEN CSIActualDate <= DATEADD(DAY, 6-DATEPART(WEEKDAY, CSILOSDate), CSILOSDate)
     THEN 'Inside'

Answer 2

Why not add another Case as First Case

"Case When DateDiff(week, '" + CSIActualDate +
      "', '" + CSILOSDate + "') > 0 Then 'Outside'" 
+  --- then the rest of the cases ...

Answer 3

if (CSILOSDate.Length > 0)
  {
            sql +=
                CSILOSDate + " as [CSITarget], " +
                CSIActualDate + " as [CSIAcheived], " +
                "CASE WHEN DATEDIFF(WEEK, " + CSILOSDate + ", " + CSIActualDate + ") > 0 Then 'Outside'" +
                "WHEN " + CSILOSDate + " IS NULL OR " + CSIActualDate + " IS NULL THEN 'Incomplete' " +
                "WHEN " + CSIActualDate + " <= DATEADD(DAY, 6-DATEPART(WEEKDAY, " + CSIActualDate + "), " + CSIActualDate + ") " +
                "THEN 'Inside' " +
                "WHEN " + CSIActualDate + " <= " + CSILOSDate + " THEN 'Inside' " +
    "END AS [CSIStatus] ";