Calculate SLA between SAME working week SQL Server

Question

I have the following columns in the database. AgreedStartDate, ActualStartDate. I need to apply the following logic: When the AcualStartDate is within the SAME business week as the AgreedStartDate, then it is 'inside' the SLA. If this is not the case then it is 'outside' the SLA. If either of the two columns is NULL, then it is 'incomplete'. There must be a simple solution to this, please help!

Answer 1

A simple CASE statement? You don't need to use variables, you can just reference your column names instead. I just used variables for a complete statement.

DECLARE @agreedStartDate DATETIME
DECLARE @actualStartDate DATETIME

SET @agreedStartDate = GETDATE()
SET @actualStartDate = GETDATE()

SELECT
 CASE
  WHEN
   @agreedStartDate IS NULL
   OR
   @actualStartDate IS NULL
  THEN
   'Incomplete'
  WHEN
   DATEPART(wk, @agreedStartDate) = DATEPART(wk, @actualStartDate)
   AND
   DATEPART(yyyy, @agreedStartDate) = DATEPART(yyyy, @actualStartDate)
  THEN
   'Inside'
  ELSE
   'Outside'
 END

Answer 2

In SQL Server, you can use a CASE statement like:

select case 
    when ActualStateDate is null or AgreedStartDate is null then 'Incomplete'
    when datepart(wk,ActualStateDate) = datepart(AgreedStartDate) then 'Inside'
    else 'Outside'
    end as ColumnName
from YourTable