Function for calculating working week in SQL Server

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.

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] ";开发者_StackOverflow中文版
}

Why not add another Case as First Case

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

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'

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] ";