Creating a temp table for the off hours of the organization

Question

I need to create a temp table that will have a Start_Date and an End_Date column based on input parameters sdate and edate. This table is the list of the off-hours of the office which includes 6pm to 6am on weekdays. For the weekends i need the time to be as follows :

6pm Friday to 12am Saturday
12am Saturday to 12am Sunday and
12am Sunday to 6am Monday

Hope the following table makes you understand what i am trying to achieve :

Start	Start_Date	End	End_Date
Sun	08/01/2021 00:00:00.000	Mon	08/02/2021 06:00:00.000
Mon	08/02/2021 18:00:00.000	Tue	08/03/2021 06:00:00.000
Tue	08/03/2021 18:00:00.000	Wed	08/04/2021 06:00:00.000
Wed	08/04/2021 18:00:00.000	Thu	08/05/2021 06:00:00.000
Thu	08/05/2021 18:00:00.000	Fri	08/06/2021 06:00:00.000
Fri	08/06/2021 18:00:00.000	Sat	08/07/2021 00:00:00.000
Sat	08/07/2021 00:00:00.000	Sun	08/08/2021 00:00:00.000
Sun	08/08/2021 00:00:00.000	Mon	08/09/2021 06:00:00.000
Mon	08/09/2021 18:00:00.000	Tue	08/10/2021 06:00:00.000
Tue	08/10/2021 18:00:00.000	Wed	08/11/2021 06:00:00.000

I have tried the following code, but it does not get the timeframe right.

ALTER PROCEDURE [dbo].[temptableforoffhours]
    @BDate  varchar(50),
    @EDate   varchar(50),
    @Provider   varchar(1000)=''

AS

BEGIN
SET NOCOUNT ON
    
DECLARE 
    @BeginDate  datetime = '',
    @EndDate    DATETIME = ''

    IF @BeginDate = '' AND @EndDate = ''
    BEGIN 
        SET @BeginDate = DATEADD(YY,-3,GETDATE())
        SET @EndDate = GETDATE()
    END

    IF @BeginDate <> '' AND @EndDate <> ''
    BEGIN
        
        Set @BeginDate =CONVERT(datetime, @BDate, 121)
        
        SET @EndDate =Convert (Datetime, cast(CONVERT(DateTime, DATEADD(DD,1,@EDate), 121) as Date),121)
        

    END

/********************************Creation of #tmptimeFrameAudit table with FrameID and Start_day and end_Day********************************/

Declare @CountTimeFrames int = DateDiff(Day, @BeginDate, @EndDate)


DECLARE @Counter INT
SET @Counter = 1

Create Table #tmptimeFrameAudit (Frameid int,Start_Day datetime, End_Day datetime, doW varchar(10))

WHILE ( @Counter <= @CountTimeFrames)
BEGIN

IF @counter = 1
Begin
    set @BeginDate = @BeginDate
End
Else
Begin
    set @BeginDate = DATEADD(DD,1,@BeginDate)
End

IF (DatePart(weekday,@BeginDate) = 7)
        BEGIN
            Insert Into #tmptimeFrameAudit values (@Counter,@BeginDate,DATEADD(HOUR,24,@BeginDate),  DATENAME(WEEKDAY,@BeginDate))
        END
ELSE IF (DatePart(weekday,@BeginDate) = 6) 
        BEGIN
            Insert Into #tmptimeFrameAudit values (@Counter,Dateadd(HOUR,18,CONVERT(datetime, @BeginDate, 121)),DATEADD(HOUR,6,@BeginDate),  DATENAME(WEEKDAY,@BeginDate))
        END
ELSE IF (DatePart(weekday,@BeginDate) = 1) 
        BEGIN
            Insert Into #tmptimeFrameAudit values (@Counter,@BeginDate,DATEADD(HOUR,30,@BeginDate),  DATENAME(WEEKDAY,@BeginDate))
        END
ELSE  
        BEGIN
            Insert Into #tmptimeFrameAudit values (@Counter,Dateadd(HOUR,18,CONVERT(datetime, @BeginDate, 121)),DATEADD(HOUR,12,@BeginDate),  DATENAME(WEEKDAY,@BeginDate))
        END     

set @Counter = @Counter+1

END

Select * from #tmptimeFrameAudit

drop Table #tmptimeFrameAudit

END

Can you please tell me what is wrong with the code and help me rectify it? Thanks!

There is a lot of code smell here. All that crazy date stuff at the beginning is pointless. You set a datetime = '', Then you check to see if it is the value you just set it to, then you check the value you set it to again. Why? And you really should be passing in a datetime datatype instead of a string and assuming it will be a valid datetime. All that aside you need to look at using a tally table here instead of a loop. And what about holiday closures? This kind of thing screams of needing to use a calendar table. — Sean Lange, Sep 22 '21 at 15:28
`@BDate varchar(50),` Nope - start over. These should be defined as DATE. Don't encourage sloppy coding and implicit conversions that can easily lead to more problems. And I suggest you don't add extra logic inside the procedure to "ease" the caller's attempt to avoid providing date parameters. Throw an error. And this `@BeginDate datetime = ''` does not do what you think. — SMor, Sep 22 '21 at 15:53

score 1 · Answer 1 · answered Sep 22 '21 at 15:55

Not sure I understand the point of your Provider parameter. It is not referenced in your code at all. I would also strongly suggest you pass the proper datatypes instead of strings.

I would suggest that using a calendar table is a better approach because you have the flexibility of things like holiday days off, or even extended or shortened hours on certain days. But that is outside the scope of what you asked.

I am using a tally table here instead of a loop. I keep one on my databases as a view like this.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally

Now we can utilize this tally table for a set based approach to this problem.

create PROCEDURE [dbo].[temptableforoffhours]
(
    @BDate date
    , @EDate date
    , @Provider varchar(1000)=''
) as

    set nocount on;
        
    declare @StartDate datetime = @BDate 
        , @EndDate datetime = @EDate;

    with Dates as
    (
        select CurrentDate = dateadd(day, t.N - 1, @StartDate)
        from cteTally t
        where t.N <= datediff(day, @StartDate, @EndDate)
    )

    select Start = datename(weekday, d.CurrentDate)
        , Start_Date = dateadd(hour, case datename(weekday, d.CurrentDate) when 'Sunday' then 0 else 18 end, d.CurrentDate)
        , [End] = case datename(weekday, d.CurrentDate) when 'Friday' then dateadd(day, 1, d.CurrentDate) when 'Saturday' then dateadd(day, 1, d.CurrentDate) else dateadd(hour, 30, d.CurrentDate) end
        , End_Date = case datename(weekday, d.CurrentDate) when 'Friday' then dateadd(day, 1, d.CurrentDate) when 'Saturday' then dateadd(day, 1, d.CurrentDate) else dateadd(hour, 30, d.CurrentDate) end
    from Dates d;

This returns the results you showed in your question.

exec temptableforoffhours @BDate = '20210801', @EDate = '20210811', @Provider = ''

GuidoG · Answer 2 · 2021-09-23T05:58:27.143

I also suggest a calendar table, this is a small part of my calendar table

select c.CalendarID,
       c.DateCalendar,
       c.DayNameID,
       c.DayName,
       c.IsWeekDay
from   tblCalendar c
where  DateCalendar between '20210801' and '20210810'

CalendarID DateCalendar DayNameID DayName   IsWeekDay
2770       2021-08-01   7         Sunday    False
2771       2021-08-02   1         Monday    True
2772       2021-08-03   2         Tuesday   True
2773       2021-08-04   3         Wednesday True
2774       2021-08-05   4         Thursday  True
2775       2021-08-06   5         Friday    True
2776       2021-08-07   6         Saturday  False
2777       2021-08-08   7         Sunday    False
2778       2021-08-09   1         Monday    True
2779       2021-08-10   2         Tuesday   True

it starts from where my company started off, until the year 2100

With this, your expected outcome is easy to achieve

select 
       c.DayName,
       case when DayNameID in (6, 7) then c.DateCalendar
            else dateadd(hour, 18, convert(datetime,c.DateCalendar))
       end as start_Date,
       case when DayNameID = 7 then dateadd(hour, 6, convert(datetime,c.DateCalendar))
            when DayNameID in (5, 6) then dateadd(hour, 24, convert(datetime,c.DateCalendar))
            else dateadd(hour, 30, convert(datetime,c.DateCalendar))
       end as end_Date
       
from   tblCalendar c
where  c.DateCalendar between '20210801' and '20210810'

it returns this

DayName    start_Date          end_Date
Sunday     01/08/2021 00:00:00  01/08/2021 06:00:00
Monday     02/08/2021 18:00:00  03/08/2021 06:00:00
Tuesday    03/08/2021 18:00:00  04/08/2021 06:00:00
Wednesday  04/08/2021 18:00:00  05/08/2021 06:00:00
Thursday   05/08/2021 18:00:00  06/08/2021 06:00:00
Friday     06/08/2021 18:00:00  07/08/2021 00:00:00
Saturday   07/08/2021 00:00:00  07/08/2021 00:00:00
Sunday     08/08/2021 00:00:00  08/08/2021 06:00:00
Monday     09/08/2021 18:00:00  10/08/2021 06:00:00
Tuesday    10/08/2021 18:00:00  11/08/2021 06:00:00

I found that having such a table has solved many problems for me, it can do the same for you

Thank you GuidoG! I used this to tweak a few things in the code! — SChavan, Oct 08 '21 at 12:07

Creating a temp table for the off hours of the organization

2 Answers2