none
Excel NETWORKDAYS formula to TSQL RRS feed

  • Dotaz

  • Hi All,

    I have a sample spreadsheet with data, which uses a formula for the Bus Hrs column (business hours are calculated as 08h00 – 17h00 Monday to Friday) and also exclude public holidays, as per the below

    I  have been given a table with the StartDate and EndDate, as per the below.

    CREATE TABLE [dbo].[myTable](
    [my_dpt] [nvarchar](259) NULL,
    [StartDate] [datetime] NULL,
    [EndDate] [datetime] NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[myTable] ([my_dpt], [StartDate], [EndDate]) VALUES (N'Tegela', CAST(N'2019-10-10T10:17:37.000' AS DateTime), CAST(N'2019-10-10T11:06:38.000' AS DateTime))
    INSERT [dbo].[myTable] ([my_dpt], [StartDate], [EndDate]) VALUES (N'Salsi', CAST(N'2019-10-10T10:18:01.000' AS DateTime), CAST(N'2019-10-10T10:55:20.000' AS DateTime))
    INSERT [dbo].[myTable] ([my_dpt], [StartDate], [EndDate]) VALUES (N'Killamou', CAST(N'2019-10-10T10:17:37.000' AS DateTime), CAST(N'2019-10-10T11:17:49.000' AS DateTime))
    INSERT [dbo].[myTable] ([my_dpt], [StartDate], [EndDate]) VALUES (N'Romney', CAST(N'2019-10-11T08:36:43.000' AS DateTime), CAST(N'2019-10-15T15:53:53.000' AS DateTime))
    INSERT [dbo].[myTable] ([my_dpt], [StartDate], [EndDate]) VALUES (N'Quens Park', CAST(N'2019-10-11T08:38:24.000' AS DateTime), CAST(N'2019-10-14T13:23:48.000' AS DateTime))
    INSERT [dbo].[myTable] ([my_dpt], [StartDate], [EndDate]) VALUES (N'Northvale', CAST(N'2019-10-11T08:40:09.000' AS DateTime), CAST(N'2019-10-11T15:19:19.000' AS DateTime))
    INSERT [dbo].[myTable] ([my_dpt], [StartDate], [EndDate]) VALUES (N'Beadle', CAST(N'2019-10-11T08:44:24.000' AS DateTime), CAST(N'2019-10-11T09:43:19.000' AS DateTime))
    INSERT [dbo].[myTable] ([my_dpt], [StartDate], [EndDate]) VALUES (N'Northlea', CAST(N'2019-10-11T08:44:26.000' AS DateTime), CAST(N'2019-10-14T16:49:02.000' AS DateTime))
    INSERT [dbo].[myTable] ([my_dpt], [StartDate], [EndDate]) VALUES (N'Prop', CAST(N'2019-10-11T08:46:15.000' AS DateTime), CAST(N'2019-10-14T06:40:13.000' AS DateTime))
    INSERT [dbo].[myTable] ([my_dpt], [StartDate], [EndDate]) VALUES (N'Padonhurst', CAST(N'2019-10-11T08:46:15.000' AS DateTime), CAST(N'2019-10-18T14:44:20.000' AS DateTime))

    How can I achieve the same formula in TSQL? 

    Please assist.

    Thanks

    úterý 22. října 2019 15:21

Všechny reakce

  • You need an auxiliary calendar table to join and calculate. 
    úterý 22. října 2019 15:33
    Moderátor
  • Hi,

    Thank you for the suggestion given.

    Any ideas on how I can use the join on the table.

    I have created the below

    ;WITH CTE_Business_Hours AS

    (

    SELECT 1 [DayOfWeek],

    '19000101 08:00' BusDayStartHour,

    '19000101 17:00' BusDayFinishHour-- Monday

    UNION ALL

    SELECT 2 [DayOfWeek],

    '19000101 08:00' BusDayStartHour,

    '19000101 17:00' BusDayFinishHour-- Tuesday

    UNION ALL

    SELECT 3 [DayOfWeek],

    '19000101 08:00' BusDayStartHour,

    '19000101 17:00' BusDayFinishHour-- Wednesday

    UNION ALL

    SELECT 4 [DayOfWeek],

    '19000101 08:00' BusDayStartHour,

    '19000101 17:00' BusDayFinishHour-- Thursday

    UNION ALL

    SELECT 5 [DayOfWeek],

    '19000101 08:00' BusDayStartHour,

    '19000101 17:00' BusDayFinishHour-- Friday

    ) select *
    from CTE_Business_Hours

    Thanks

    úterý 22. října 2019 17:16
  • Create a generic calendar with date, hour and holiday (and more columns) and join your current table and aggregate your data.
    úterý 22. října 2019 17:31
    Moderátor
  • Hi , 

    Per your description , you would like to calculate the time in addition to Public Holidays and Weekends. Right ?

    If so, Please refer to  Calculating business hours

    Hope it will help you.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    středa 23. října 2019 3:17
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    úterý 29. října 2019 8:36