Insert (or update) script to populate data in table

Transact-SQL
1

I have created a table but am struggling to populate the data, I need to return a table showing the date, working hours Monday - Friday. I know how to populate the working start/end time and the working hours but am struggling with the[Day] [Day of Week] and [Working Day] element

INSERT INTO [N].[NHours]
([Day]
,[Day of Week]
,[Working Day]
,[Bank Holiday]
,[WD Start Time]
,[WD End Time]
,[WD Hours])
VALUES
(<Day, datetime,>
,<Day of Week, nchar(9),>
,<Working Day, bit,>
,<Bank Holiday, bit,>
,<WD Start Time, datetime,>
,<WD End Time, datetime,>
,<WD Hours, decimal(10,2),>)
GO

Data populated needs to be similar to the below

image
image.png753×121 7.06 KB

Any advice?

2

How is the Day populated? Is it just a sequential increase of a date or is it already populated? You can get the Day of Week using the DATENAME function (eg. select DATENAME(dw,getdate()) ). Is Working Day ever something other than 1 (ie is it 0 for weekends) if so then maybe something like

CASE WHEN DATENAME(dw,getdate()) IN ('Saturday','Sunday') THEN 0 ELSE 1 END

1 Like
3

It's easy enough to generate a generic calendar table. (See the code below. )
Holidays, on the other hand, are a little tougher. Yes, all other major holidays can be calculated but that gets complex, especially when you factor in the actual holiday date and it's observed date.. Not to mention the fact that not all companies observe the same set of holidays. The reality is, it's just easier to keep and maintain a permanent calendar table and have upper management supply you with the list holidays.

DECLARE
	@first_date DATE = '2015-01-01',
	@last_date DATE = '2018-12-31';

WITH 
	cte_day (dt) AS (
		SELECT TOP (DATEDIFF(DAY, @first_date, @last_date) + 1)
			DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY o1.object_id) - 1, @first_date)
		FROM
			sys.objects o1
			CROSS JOIN sys.objects o2
			CROSS JOIN sys.objects o3
		)
SELECT 
	d.dt,
	[DayOfWeek] = DATENAME(dw, d.dt),
	DATEPART(dw, d.dt),
	WorkingDay = CASE WHEN DATEPART(dw, d.dt) BETWEEN 2 AND 6 THEN 1 ELSE 0 END
FROM
	cte_day d;
1 Like
4

Hi, the date is not already populated, it needs to be a sequential increase starting from the 1st of this month.

5

Thank you, this information is really useful

6

Hi

You can use Tally Tables

Becomes very easy

1 Like
7

I have gotten this far (Ignoring the bank holiday element for the time being) but all it does is repeat today's date on all rows rather than start on the 1st and increment daily..

DECLARE @Day DATETIME
SET @Day = '01/11/2018'

INSERT INTO [N].[N_WD_Hours]
([Day]
,[Day of Week]
,[Working Day]
,[WD Start Time]
,[WD End Time]
,[WD Hours])

 VALUES
    (@Day + 1
	,DATENAME([DW],GETDATE())
	,CASE WHEN DATENAME([DW], @Day) IN ('Saturday','Sunday') THEN 0 ELSE 1 END
	,DATEADD(HOUR, 9, @Day)
	,DATEADD(minute, 30, DATEADD(HOUR, 17, @Day))
	,DATEDIFF(MINUTE,DATEADD(HOUR, 9, @Day),DATEADD(minute, 30, DATEADD(HOUR, 17, @Day))) / 60.0
	)

Set @Day = @Day +1

GO

8

hi you need a loop

9

please try this see if this works
it will loop 20 times .. if you want more or less change 20
WHILE @i < 20

SQL 
DECLARE @i INT = 0 
DECLARE @Day DATETIME 

SET @Day = '01/11/2018' 

WHILE @i < 20 
  BEGIN 
      INSERT INTO [N].[n_wd_hours] 
                  ([day], 
                   [day of week], 
                   [working day], 
                   [wd start time], 
                   [wd end time], 
                   [wd hours]) 
      VALUES      (@Day + 1, 
                   Datename([DW], Getdate()), 
                   CASE 
                     WHEN Datename([DW], @Day) IN ( 'Saturday', 'Sunday' ) THEN 
                     0 
                     ELSE 1 
                   END, 
                   Dateadd(hour, 9, @Day), 
                   Dateadd(minute, 30, Dateadd(hour, 17, @Day)), 
                   Datediff(minute, Dateadd(hour, 9, @Day), Dateadd(minute, 30, 
                   Dateadd(hour, 17, @Day))) 
                   / 60.0 ) 

      SET @Day = @Day + 1 
      SET @i = @i + 1 
  END
10

Hi, I added a While loop using the @Day and also the example you provided and I am still getting the same, today's date repeated over several rows..

11

ok please wait a few minutes

i will fix and send

13

i am getting different dates

please check .. dont know what you mean same

Script 
use tempdb 
go 

DROP TABLE [N_WD_Hours]
GO 

CREATE TABLE [N_WD_Hours]
(
[Day] DATETIME
,[Day of Week] VARCHAR(100)
,[Working Day] DATETIME
,[WD Start Time] DATETIME
,[WD End Time] DATETIME
,[WD Hours] FLOAT
)
GO 

DECLARE @i int = 0

DECLARE @Day DATETIME
SET @Day = '01/11/2018'

WHILE @i < 20
BEGIN
 
    
INSERT INTO [N_WD_Hours]
([Day]
,[Day of Week]
,[Working Day]
,[WD Start Time]
,[WD End Time]
,[WD Hours])

 VALUES
    (@Day + 1
	,DATENAME([DW],GETDATE())
	,CASE WHEN DATENAME([DW], @Day) IN ('Saturday','Sunday') THEN 0 ELSE 1 END
	,DATEADD(HOUR, 9, @Day)
	,DATEADD(minute, 30, DATEADD(HOUR, 17, @Day))
	,DATEDIFF(MINUTE,DATEADD(HOUR, 9, @Day),DATEADD(minute, 30, DATEADD(HOUR, 17, @Day))) / 60.0
	)

Set @Day = @Day +1
   SET @i = @i + 1

END

SELECT * FROM [N_WD_Hours]
result

image
image.png2173×1139 125 KB
strong text

1 Like
14

When I add this into a blank query it works, however I when I add it into a previously created table using 'Script' 'INSERT To' the table does not populate the same as the results.

view within insert to

image
image.png1008×102 2.16 KB

15

view when selecting rows

image
image.png722×191 11.1 KB

16

please provide your script .. i will take a look

17

I'm being an idiot! I think I have sussed it - thank you for all your help :grinning: