>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?

I am looking into using DATEDIFF(second, @Start, @End) to giv eme th enumber of seconds between the two dates, converting this to hours and then subtracting 48 hours (since there are 2 non-working days in this example). However, when I calculate the hours, I am not getting any decimal places which I need for accuracy.

My code would be something like this:

declare    @Start Datetime = '2017-01-19 09:04:53',
    @End Datetime = '2017-01-23 09:03:01',
    @DayLimit Int = 2

select calchours = (datediff(second, @Start, @End) / 3600
,(SELECT COUNT(*) FROM dbo.Calendar 
								WHERE isWeekday = 1 
								AND isHoliday = 0 
								AND dt >= cast(@Start as date) AND dt < cast(@End as date)) * 24

But why don't I get 95.97 (rounded to 2 decimals) for the calchours? My result is just 95.

got it:

round(cast(datediff(second, @Start, @End) as float) / 3600, 2)