Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
SQL Server - Find the last X of the month
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
--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 = '01-JAN-18'; DECLARE @DateEnd DATE = EOMONTH('01-MAR-18'); 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 ; -------------------------------------------------------------------------------- -- Find the last X of the month -------------------------------------------------------------------------------- WITH date_data AS ( SELECT [TheDate], DateName(dw, [TheDate]) AS TheDate_day_name, [IsWeekend], [IsWeekday], CASE [IsWeekday] WHEN 1 THEN SUM(CAST([IsWeekday] AS INT)) OVER (PARTITION BY YEAR([TheDate]), MONTH([TheDate]) ORDER BY [TheDate]) ELSE NULL END AS [WorkingDayNumber], CASE DateName(dw, [TheDate]) WHEN 'Friday' -- Change this to find the day we are interested in THEN SUM(1) OVER (PARTITION BY YEAR([TheDate]), MONTH([TheDate]) ORDER BY [TheDate]) ELSE 0 END AS [DayToTrackNumber] FROM date_t ) SELECT [TheDate], [TheDate_day_name], [IsWeekend], [IsWeekday], --Find last weekday of month [WorkingDayNumber], CASE [WorkingDayNumber] WHEN MAX([WorkingDayNumber]) OVER (PARTITION BY YEAR([TheDate]), MONTH([TheDate])) THEN 1 ELSE 0 END [IsLast_WorkingDay], --Find last X day of month [DayToTrackNumber], CASE [DayToTrackNumber] WHEN MAX([DayToTrackNumber]) OVER (PARTITION BY YEAR([TheDate]), MONTH([TheDate])) THEN 1 ELSE 0 END [IsLast_DayToTrack] FROM date_data ;
View schema
Execution time: 0,02 sec, rows selected: 92, rows affected: 90, absolute service time: 0,23 sec
edit mode
|
history
TheDate
TheDate_day_name
IsWeekend
IsWeekday
WorkingDayNumber
IsLast_WorkingDay
DayToTrackNumber
IsLast_DayToTrack
1
01.01.2018 00:00:00
Monday
0
1
1
0
0
0
2
02.01.2018 00:00:00
Tuesday
0
1
2
0
0
0
3
03.01.2018 00:00:00
Wednesday
0
1
3
0
0
0
4
04.01.2018 00:00:00
Thursday
0
1
4
0
0
0
5
05.01.2018 00:00:00
Friday
0
1
5
0
5
0
6
06.01.2018 00:00:00
Saturday
1
0
NULL
0
0
0
7
07.01.2018 00:00:00
Sunday
1
0
NULL
0
0
0
8
08.01.2018 00:00:00
Monday
0
1
6
0
0
0
9
09.01.2018 00:00:00
Tuesday
0
1
7
0
0
0
10
10.01.2018 00:00:00
Wednesday
0
1
8
0
0
0
11
11.01.2018 00:00:00
Thursday
0
1
9
0
0
0
12
12.01.2018 00:00:00
Friday
0
1
10
0
12
0
13
13.01.2018 00:00:00
Saturday
1
0
NULL
0
0
0
14
14.01.2018 00:00:00
Sunday
1
0
NULL
0
0
0
15
15.01.2018 00:00:00
Monday
0
1
11
0
0
0
16
16.01.2018 00:00:00
Tuesday
0
1
12
0
0
0
17
17.01.2018 00:00:00
Wednesday
0
1
13
0
0
0
18
18.01.2018 00:00:00
Thursday
0
1
14
0
0
0
19
19.01.2018 00:00:00
Friday
0
1
15
0
19
0
20
20.01.2018 00:00:00
Saturday
1
0
NULL
0
0
0
21
21.01.2018 00:00:00
Sunday
1
0
NULL
0
0
0
22
22.01.2018 00:00:00
Monday
0
1
16
0
0
0
23
23.01.2018 00:00:00
Tuesday
0
1
17
0
0
0
24
24.01.2018 00:00:00
Wednesday
0
1
18
0
0
0
25
25.01.2018 00:00:00
Thursday
0
1
19
0
0
0
26
26.01.2018 00:00:00
Friday
0
1
20
0
26
1
27
27.01.2018 00:00:00
Saturday
1
0
NULL
0
0
0
28
28.01.2018 00:00:00
Sunday
1
0
NULL
0
0
0
29
29.01.2018 00:00:00
Monday
0
1
21
0
0
0
30
30.01.2018 00:00:00
Tuesday
0
1
22
0
0
0
31
31.01.2018 00:00:00
Wednesday
0
1
23
1
0
0
32
01.02.2018 00:00:00
Thursday
0
1
1
0
0
0
33
02.02.2018 00:00:00
Friday
0
1
2
0
2
0
34
03.02.2018 00:00:00
Saturday
1
0
NULL
0
0
0
35
04.02.2018 00:00:00
Sunday
1
0
NULL
0
0
0
36
05.02.2018 00:00:00
Monday
0
1
3
0
0
0
37
06.02.2018 00:00:00
Tuesday
0
1
4
0
0
0
38
07.02.2018 00:00:00
Wednesday
0
1
5
0
0
0
39
08.02.2018 00:00:00
Thursday
0
1
6
0
0
0
40
09.02.2018 00:00:00
Friday
0
1
7
0
9
0
41
10.02.2018 00:00:00
Saturday
1
0
NULL
0
0
0
42
11.02.2018 00:00:00
Sunday
1
0
NULL
0
0
0
43
12.02.2018 00:00:00
Monday
0
1
8
0
0
0
44
13.02.2018 00:00:00
Tuesday
0
1
9
0
0
0
45
14.02.2018 00:00:00
Wednesday
0
1
10
0
0
0
46
15.02.2018 00:00:00
Thursday
0
1
11
0
0
0
47
16.02.2018 00:00:00
Friday
0
1
12
0
16
0
48
17.02.2018 00:00:00
Saturday
1
0
NULL
0
0
0
49
18.02.2018 00:00:00
Sunday
1
0
NULL
0
0
0
50
19.02.2018 00:00:00
Monday
0
1
13
0
0
0
51
20.02.2018 00:00:00
Tuesday
0
1
14
0
0
0
52
21.02.2018 00:00:00
Wednesday
0
1
15
0
0
0
53
22.02.2018 00:00:00
Thursday
0
1
16
0
0
0
54
23.02.2018 00:00:00
Friday
0
1
17
0
23
1
55
24.02.2018 00:00:00
Saturday
1
0
NULL
0
0
0
56
25.02.2018 00:00:00
Sunday
1
0
NULL
0
0
0
57
26.02.2018 00:00:00
Monday
0
1
18
0
0
0
58
27.02.2018 00:00:00
Tuesday
0
1
19
0
0
0
59
28.02.2018 00:00:00
Wednesday
0
1
20
1
0
0
60
01.03.2018 00:00:00
Thursday
0
1
1
0
0
0
61
02.03.2018 00:00:00
Friday
0
1
2
0
2
0
62
03.03.2018 00:00:00
Saturday
1
0
NULL
0
0
0
63
04.03.2018 00:00:00
Sunday
1
0
NULL
0
0
0
64
05.03.2018 00:00:00
Monday
0
1
3
0
0
0
65
06.03.2018 00:00:00
Tuesday
0
1
4
0
0
0
66
07.03.2018 00:00:00
Wednesday
0
1
5
0
0
0
67
08.03.2018 00:00:00
Thursday
0
1
6
0
0
0
68
09.03.2018 00:00:00
Friday
0
1
7
0
9
0
69
10.03.2018 00:00:00
Saturday
1
0
NULL
0
0
0
70
11.03.2018 00:00:00
Sunday
1
0
NULL
0
0
0
71
12.03.2018 00:00:00
Monday
0
1
8
0
0
0
72
13.03.2018 00:00:00
Tuesday
0
1
9
0
0
0
73
14.03.2018 00:00:00
Wednesday
0
1
10
0
0
0
74
15.03.2018 00:00:00
Thursday
0
1
11
0
0
0
75
16.03.2018 00:00:00
Friday
0
1
12
0
16
0
76
17.03.2018 00:00:00
Saturday
1
0
NULL
0
0
0
77
18.03.2018 00:00:00
Sunday
1
0
NULL
0
0
0
78
19.03.2018 00:00:00
Monday
0
1
13
0
0
0
79
20.03.2018 00:00:00
Tuesday
0
1
14
0
0
0
80
21.03.2018 00:00:00
Wednesday
0
1
15
0
0
0
81
22.03.2018 00:00:00
Thursday
0
1
16
0
0
0
82
23.03.2018 00:00:00
Friday
0
1
17
0
23
0
83
24.03.2018 00:00:00
Saturday
1
0
NULL
0
0
0
84
25.03.2018 00:00:00
Sunday
1
0
NULL
0
0
0
85
26.03.2018 00:00:00
Monday
0
1
18
0
0
0
86
27.03.2018 00:00:00
Tuesday
0
1
19
0
0
0
87
28.03.2018 00:00:00
Wednesday
0
1
20
0
0
0
88
29.03.2018 00:00:00
Thursday
0
1
21
0
0
0
89
30.03.2018 00:00:00
Friday
0
1
22
1
30
1
90
31.03.2018 00:00:00
Saturday
1
0
NULL
0
0
0