Hi,

I need to present a status to users based on the date time that a record is stamped with, bearing in mind working days.

I have a Calendar table which has days defined as to whether they are working days or not and if they are a holiday or not.

This works fine if I am just taking days into consideration, but they now want it more accurately based on the time as well.

So for example, a record has the date & time of 2017-01-19 09:04:53 (a Thursday)

On the Friday the status will be "Pending", likewise on the Saturday and Sunday since those are defined as not working days.

On the Monday 2017-01-23 09:03:01 the status will be "Pending" still, however at 2017-01-23 09:04:54 it should change to "Overdue".

My existing Table Valued Function is:

ALTER FUNCTION [dbo].[GetPreCertStatus]
(	
	-- Add the parameters for the function here
    @Start Datetime,
    @End Datetime,
    @DayLimit Int
)
RETURNS TABLE 
AS
RETURN 
(
	-- Add the SELECT statement with parameter references here
with cte as (select case 
					when @Start IS NOT NULL and @End IS NOT NULL 
						THEN 'Settled'
					WHEN @Start IS NOT NULL and @End IS NULL 
						then case when @DayLimit < (SELECT COUNT(*) FROM dbo.Calendar 
								WHERE isWeekday = 1 
								AND isHoliday = 0 
								AND dt >= cast(@Start as date) AND dt < cast(GETDATE() as date))
						THEN 'Overdue'
							  else
								   'Pending' 
							END
					ELSE -- what else is not covered ?
					   'Pending'
				  END as StatusDescr)

select StatusDescr, case StatusDescr WHEN 'Settled' then 2 when 'Pending' then 1 when 'Overdue' then 3 end as [Status]
from cte
)

How can I change this logic to check for 48 hours accurate to the second?