SQL Server - Calculate working day number, Sql Server

SQL Server - Calculate working day number

Language: Layout:

--Set the start of the week to Monday
SET DATEFIRST 1;

--------------------------------------------------------------------------------
-- Create Date Table
--------------------------------------------------------------------------------
CREATE TABLE date_t
(
  [TheDate]   DATE NOT NULL PRIMARY KEY,
  [IsWeekend] INT  NOT NULL,
  [IsWeekday] INT  NOT NULL,
);

--------------------------------------------------------------------------------
-- Create Date list
-- Using Number sequence: http://datablog.roman-halliday.com/index.php/2018/06/01/dates-in-sql-server-create-sample-date-ranges/
--------------------------------------------------------------------------------
DECLARE @DateStart DATE = DateAdd(MONTH, -1, DateAdd(month, DateDiff(month, 0, GetDate()), 0)); -- Start of last month
DECLARE @DateEnd   DATE = EOMONTH(GetDate()); -- End of this month
 
PRINT 'Start Date: ' + CONVERT(VARCHAR(10), @DateStart, 120);
PRINT 'Start End: '  + CONVERT(VARCHAR(10), @DateEnd,   120);

WITH number_tbl AS (
SELECT 0 AS a UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
), number_sequences AS (
SELECT t1.a AS t1a,
       t2.a AS t2a,
       t3.a AS t3a,
       (t3.a + (10 * t2.a) + (100 * t1.a)) AS concatenate_calculation
  FROM number_tbl t1
  CROSS JOIN number_tbl t2
  CROSS JOIN number_tbl t3
), date_sequence AS (
SELECT CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) AS [TheDate]
  FROM number_sequences
 WHERE CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) <= @DateEnd
)
INSERT
  INTO date_t
      ([TheDate], [IsWeekend], [IsWeekday])
SELECT [TheDate],
       CASE WHEN DatePart(dw, [TheDate]) IN (6, 7) THEN 1 ELSE 0 END AS [IsWeekend],
       CASE WHEN DatePart(dw, [TheDate]) IN (6, 7) THEN 0 ELSE 1 END AS [IsWeekday]
  FROM date_sequence
;

--------------------------------------------------------------------------------
-- Calculate working day number (of the month)
--------------------------------------------------------------------------------
SELECT [TheDate],
       DateName(dw, [TheDate]) AS TheDate_day_name,
       [IsWeekend],
       [IsWeekday],
       CASE [IsWeekday] WHEN 0 THEN NULL
                        ELSE SUM(CAST([IsWeekday] AS INT)) OVER (PARTITION BY YEAR([TheDate]), MONTH([TheDate]) ORDER BY [TheDate])
        END AS [WorkingDayNumber],
       CASE [TheDate] WHEN DateFromParts(YEAR(GetDate()), MONTH(GetDate()), DAY(GetDate())) THEN 'Today' ELSE '' END AS [DayMarker]
  FROM date_t
;

View schema
Execution time: 0,02 sec, rows selected: 63, rows affected: 61, absolute service time: 0,19 sec

	TheDate	TheDate_day_name	IsWeekend	IsWeekday	WorkingDayNumber	DayMarker
1	01.05.2018 00:00:00	Tuesday	0	1	1
2	02.05.2018 00:00:00	Wednesday	0	1	2
3	03.05.2018 00:00:00	Thursday	0	1	3
4	04.05.2018 00:00:00	Friday	0	1	4
5	05.05.2018 00:00:00	Saturday	1	0	NULL
6	06.05.2018 00:00:00	Sunday	1	0	NULL
7	07.05.2018 00:00:00	Monday	0	1	5
8	08.05.2018 00:00:00	Tuesday	0	1	6
9	09.05.2018 00:00:00	Wednesday	0	1	7
10	10.05.2018 00:00:00	Thursday	0	1	8
11	11.05.2018 00:00:00	Friday	0	1	9
12	12.05.2018 00:00:00	Saturday	1	0	NULL
13	13.05.2018 00:00:00	Sunday	1	0	NULL
14	14.05.2018 00:00:00	Monday	0	1	10
15	15.05.2018 00:00:00	Tuesday	0	1	11
16	16.05.2018 00:00:00	Wednesday	0	1	12
17	17.05.2018 00:00:00	Thursday	0	1	13
18	18.05.2018 00:00:00	Friday	0	1	14
19	19.05.2018 00:00:00	Saturday	1	0	NULL
20	20.05.2018 00:00:00	Sunday	1	0	NULL
21	21.05.2018 00:00:00	Monday	0	1	15
22	22.05.2018 00:00:00	Tuesday	0	1	16
23	23.05.2018 00:00:00	Wednesday	0	1	17
24	24.05.2018 00:00:00	Thursday	0	1	18
25	25.05.2018 00:00:00	Friday	0	1	19
26	26.05.2018 00:00:00	Saturday	1	0	NULL
27	27.05.2018 00:00:00	Sunday	1	0	NULL
28	28.05.2018 00:00:00	Monday	0	1	20
29	29.05.2018 00:00:00	Tuesday	0	1	21
30	30.05.2018 00:00:00	Wednesday	0	1	22
31	31.05.2018 00:00:00	Thursday	0	1	23
32	01.06.2018 00:00:00	Friday	0	1	1
33	02.06.2018 00:00:00	Saturday	1	0	NULL
34	03.06.2018 00:00:00	Sunday	1	0	NULL
35	04.06.2018 00:00:00	Monday	0	1	2
36	05.06.2018 00:00:00	Tuesday	0	1	3
37	06.06.2018 00:00:00	Wednesday	0	1	4
38	07.06.2018 00:00:00	Thursday	0	1	5
39	08.06.2018 00:00:00	Friday	0	1	6
40	09.06.2018 00:00:00	Saturday	1	0	NULL
41	10.06.2018 00:00:00	Sunday	1	0	NULL
42	11.06.2018 00:00:00	Monday	0	1	7
43	12.06.2018 00:00:00	Tuesday	0	1	8
44	13.06.2018 00:00:00	Wednesday	0	1	9
45	14.06.2018 00:00:00	Thursday	0	1	10
46	15.06.2018 00:00:00	Friday	0	1	11
47	16.06.2018 00:00:00	Saturday	1	0	NULL
48	17.06.2018 00:00:00	Sunday	1	0	NULL	Today
49	18.06.2018 00:00:00	Monday	0	1	12
50	19.06.2018 00:00:00	Tuesday	0	1	13
51	20.06.2018 00:00:00	Wednesday	0	1	14
52	21.06.2018 00:00:00	Thursday	0	1	15
53	22.06.2018 00:00:00	Friday	0	1	16
54	23.06.2018 00:00:00	Saturday	1	0	NULL
55	24.06.2018 00:00:00	Sunday	1	0	NULL
56	25.06.2018 00:00:00	Monday	0	1	17
57	26.06.2018 00:00:00	Tuesday	0	1	18
58	27.06.2018 00:00:00	Wednesday	0	1	19
59	28.06.2018 00:00:00	Thursday	0	1	20
60	29.06.2018 00:00:00	Friday	0	1	21
61	30.06.2018 00:00:00	Saturday	1	0	NULL