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 :

1. 6pm Friday to 12am Saturday
2. 12am Saturday to 12am Sunday and
3. 12am Sunday to 6am Monday

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

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!

CodePudding user response：

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 = ''

CodePudding user response：

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