Andy Green
asked on
How to get the Nth weekday of a month from a calendar table
Hi
I have a calendar table (seems to be the best practice for other stuff I'm doing)
I have a need to select the top n records for the nth day in a month
SO from today if I need the top 5 second Tuesdays I would expect to get
10 Feb
10 Mar
14 Apr
12 May
9 June
In my table I have the day number ie a Monday is 2, and month ie feb is a 2 also.
If this cant be done in a query I could add another row to the table of day ordinal in a month, then the query would be simple, but how would I do that, The calendar currently holds 10 years worth of dates.
I'm using SQL server 2008
Andy
I have a calendar table (seems to be the best practice for other stuff I'm doing)
I have a need to select the top n records for the nth day in a month
SO from today if I need the top 5 second Tuesdays I would expect to get
10 Feb
10 Mar
14 Apr
12 May
9 June
In my table I have the day number ie a Monday is 2, and month ie feb is a 2 also.
If this cant be done in a query I could add another row to the table of day ordinal in a month, then the query would be simple, but how would I do that, The calendar currently holds 10 years worth of dates.
I'm using SQL server 2008
Andy
ASKER
Here you go:
CREATE TABLE [dbo].[zCalendar](
[dt] [smalldatetime] NOT NULL,
[isWorkday] [bit] NULL,
[isHoliday] [bit] NULL,
[Y] [smallint] NULL,
[FY] [smallint] NULL,
[Q] [tinyint] NULL,
[M] [tinyint] NULL,
[D] [tinyint] NULL,
[DW] [tinyint] NULL,
[monthname] [varchar](9) NULL,
[dayname] [varchar](9) NULL,
[W] [tinyint] NULL)
D is the day ( as in date day and the DW is the day in week IE 1 is a Monday.
A
CREATE TABLE [dbo].[zCalendar](
[dt] [smalldatetime] NOT NULL,
[isWorkday] [bit] NULL,
[isHoliday] [bit] NULL,
[Y] [smallint] NULL,
[FY] [smallint] NULL,
[Q] [tinyint] NULL,
[M] [tinyint] NULL,
[D] [tinyint] NULL,
[DW] [tinyint] NULL,
[monthname] [varchar](9) NULL,
[dayname] [varchar](9) NULL,
[W] [tinyint] NULL)
D is the day ( as in date day and the DW is the day in week IE 1 is a Monday.
A
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Will do Phillip thank you.
The more I think about this would it be more efficient to hold the position of the day in a month in another column, would make the SQL easier
Andy
The more I think about this would it be more efficient to hold the position of the day in a month in another column, would make the SQL easier
Andy
Then it would be Day between 8 and 14 and DW = 2
ASKER
I need 2 parameters, the day number (1-7) and its position in the month (1 First - 5 Last which could also be 4)
My query would be where day = 2 and ordinal = 2 would return the second Tuesday, but how to populate the column in the table?
Andy
My query would be where day = 2 and ordinal = 2 would return the second Tuesday, but how to populate the column in the table?
Andy
add a field to your table, below I used [dw_ordinal]
update zCalendar
set dw_ordinal = xtra.rn
from zCalendar
join (
select dt, row_number() over(partition by y, m, dw order by dt) as rn
from zCalendar
) xtra on zCalendar.dt = xtra.dt
;
select
dt, y, m, dw, dw_ordinal
from zCalendar
order by dt
;
details & sample data used
**MS SQL Server 2008 Schema Setup**:
CREATE TABLE zCalendar
( [dt] datetime
, [isWorkday] int
, [isHoliday] int
, [Y] int
, [FY] int
, [Q] int
, [M] int
, [D] int
, [DW] int
, [dw_ordinal] int
, [monthname] varchar(8)
, [dayname] varchar(4)
, [w] int)
;
INSERT INTO zCalendar
([dt], [isWorkday], [isHoliday], [Y], [FY], [Q], [M], [D], [DW], [monthname], [dayname], [w])
VALUES
('2015-01-01 00:00:00', 1, 0, 2015, 2014, 4, 1, 1, 4, 'January', NULL, 1),
('2015-01-02 00:00:00', 1, 0, 2015, 2014, 4, 1, 2, 5, 'January', NULL, 1),
('2015-01-03 00:00:00', 0, 1, 2015, 2014, 4, 1, 3, 6, 'January', NULL, 1),
('2015-01-04 00:00:00', 0, 1, 2015, 2014, 4, 1, 4, 7, 'January', NULL, 2),
('2015-01-05 00:00:00', 1, 0, 2015, 2014, 4, 1, 5, 1, 'January', NULL, 2),
('2015-01-06 00:00:00', 1, 0, 2015, 2014, 4, 1, 6, 2, 'January', NULL, 2),
('2015-01-07 00:00:00', 1, 0, 2015, 2014, 4, 1, 7, 3, 'January', NULL, 2),
('2015-01-08 00:00:00', 1, 0, 2015, 2014, 4, 1, 8, 4, 'January', NULL, 2),
('2015-01-09 00:00:00', 1, 0, 2015, 2014, 4, 1, 9, 5, 'January', NULL, 2),
('2015-01-10 00:00:00', 0, 1, 2015, 2014, 4, 1, 10, 6, 'January', NULL, 2),
('2015-01-11 00:00:00', 0, 1, 2015, 2014, 4, 1, 11, 7, 'January', NULL, 3),
('2015-01-12 00:00:00', 1, 0, 2015, 2014, 4, 1, 12, 1, 'January', NULL, 3),
('2015-01-13 00:00:00', 1, 0, 2015, 2014, 4, 1, 13, 2, 'January', NULL, 3),
('2015-01-14 00:00:00', 1, 0, 2015, 2014, 4, 1, 14, 3, 'January', NULL, 3),
('2015-01-15 00:00:00', 1, 0, 2015, 2014, 4, 1, 15, 4, 'January', NULL, 3),
('2015-01-16 00:00:00', 1, 0, 2015, 2014, 4, 1, 16, 5, 'January', NULL, 3),
('2015-01-17 00:00:00', 0, 1, 2015, 2014, 4, 1, 17, 6, 'January', NULL, 3),
('2015-01-18 00:00:00', 0, 1, 2015, 2014, 4, 1, 18, 7, 'January', NULL, 4),
('2015-01-19 00:00:00', 1, 0, 2015, 2014, 4, 1, 19, 1, 'January', NULL, 4),
('2015-01-20 00:00:00', 1, 0, 2015, 2014, 4, 1, 20, 2, 'January', NULL, 4),
('2015-01-21 00:00:00', 1, 0, 2015, 2014, 4, 1, 21, 3, 'January', NULL, 4),
('2015-01-22 00:00:00', 1, 0, 2015, 2014, 4, 1, 22, 4, 'January', NULL, 4),
('2015-01-23 00:00:00', 1, 0, 2015, 2014, 4, 1, 23, 5, 'January', NULL, 4),
('2015-01-24 00:00:00', 0, 1, 2015, 2014, 4, 1, 24, 6, 'January', NULL, 4),
('2015-01-25 00:00:00', 0, 1, 2015, 2014, 4, 1, 25, 7, 'January', NULL, 5),
('2015-01-26 00:00:00', 1, 0, 2015, 2014, 4, 1, 26, 1, 'January', NULL, 5),
('2015-01-27 00:00:00', 1, 0, 2015, 2014, 4, 1, 27, 2, 'January', NULL, 5),
('2015-01-28 00:00:00', 1, 0, 2015, 2014, 4, 1, 28, 3, 'January', NULL, 5),
('2015-01-29 00:00:00', 1, 0, 2015, 2014, 4, 1, 29, 4, 'January', NULL, 5),
('2015-01-30 00:00:00', 1, 0, 2015, 2014, 4, 1, 30, 5, 'January', NULL, 5),
('2015-01-31 00:00:00', 0, 1, 2015, 2014, 4, 1, 31, 6, 'January', NULL, 5),
('2015-02-01 00:00:00', 0, 1, 2015, 2014, 4, 2, 1, 7, 'February', NULL, 6),
('2015-02-02 00:00:00', 1, 0, 2015, 2014, 4, 2, 2, 1, 'February', NULL, 6)
;
**Query 1**:
update zCalendar
set dw_ordinal = xtra.rn
from zCalendar
join (
select dt, row_number() over(partition by y, m, dw order by dt) as rn
from zCalendar
) xtra on zCalendar.dt = xtra.dt
**[Results][2]**:
**Query 2**:
select
dt, y, m, dw, dw_ordinal
from zCalendar
order by dt
**[Results][3]**:
| DT | Y | M | DW | DW_ORDINAL |
|---------------------------------|------|---|----|------------|
| January, 01 2015 00:00:00+0000 | 2015 | 1 | 4 | 1 |
| January, 02 2015 00:00:00+0000 | 2015 | 1 | 5 | 1 |
| January, 03 2015 00:00:00+0000 | 2015 | 1 | 6 | 1 |
| January, 04 2015 00:00:00+0000 | 2015 | 1 | 7 | 1 |
| January, 05 2015 00:00:00+0000 | 2015 | 1 | 1 | 1 |
| January, 06 2015 00:00:00+0000 | 2015 | 1 | 2 | 1 |
| January, 07 2015 00:00:00+0000 | 2015 | 1 | 3 | 1 |
| January, 08 2015 00:00:00+0000 | 2015 | 1 | 4 | 2 |
| January, 09 2015 00:00:00+0000 | 2015 | 1 | 5 | 2 |
| January, 10 2015 00:00:00+0000 | 2015 | 1 | 6 | 2 |
| January, 11 2015 00:00:00+0000 | 2015 | 1 | 7 | 2 |
| January, 12 2015 00:00:00+0000 | 2015 | 1 | 1 | 2 |
| January, 13 2015 00:00:00+0000 | 2015 | 1 | 2 | 2 |
| January, 14 2015 00:00:00+0000 | 2015 | 1 | 3 | 2 |
| January, 15 2015 00:00:00+0000 | 2015 | 1 | 4 | 3 |
| January, 16 2015 00:00:00+0000 | 2015 | 1 | 5 | 3 |
| January, 17 2015 00:00:00+0000 | 2015 | 1 | 6 | 3 |
| January, 18 2015 00:00:00+0000 | 2015 | 1 | 7 | 3 |
| January, 19 2015 00:00:00+0000 | 2015 | 1 | 1 | 3 |
| January, 20 2015 00:00:00+0000 | 2015 | 1 | 2 | 3 |
| January, 21 2015 00:00:00+0000 | 2015 | 1 | 3 | 3 |
| January, 22 2015 00:00:00+0000 | 2015 | 1 | 4 | 4 |
| January, 23 2015 00:00:00+0000 | 2015 | 1 | 5 | 4 |
| January, 24 2015 00:00:00+0000 | 2015 | 1 | 6 | 4 |
| January, 25 2015 00:00:00+0000 | 2015 | 1 | 7 | 4 |
| January, 26 2015 00:00:00+0000 | 2015 | 1 | 1 | 4 |
| January, 27 2015 00:00:00+0000 | 2015 | 1 | 2 | 4 |
| January, 28 2015 00:00:00+0000 | 2015 | 1 | 3 | 4 |
| January, 29 2015 00:00:00+0000 | 2015 | 1 | 4 | 5 |
| January, 30 2015 00:00:00+0000 | 2015 | 1 | 5 | 5 |
| January, 31 2015 00:00:00+0000 | 2015 | 1 | 6 | 5 |
| February, 01 2015 00:00:00+0000 | 2015 | 2 | 7 | 1 |
| February, 02 2015 00:00:00+0000 | 2015 | 2 | 1 | 1 |
[1]: http://sqlfiddle.com/#!3/35d2b/4
ASKER
Thanks, but I don't really want to manually add the ordinal field to 10 years worth of dates.
Can this be scripted for my current dataset?
Andy
Can this be scripted for my current dataset?
Andy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> I need 2 parameters, the day number (1-7) and its position in the month (1 First - 5 Last which could also be 4)
If you are looking for the second Tuesday of a week, then it would have to be between the 8th and the 14th.
The position on the month is merely:
Convert(int,(D+6)/7)
If you are looking for the second Tuesday of a week, then it would have to be between the 8th and the 14th.
The position on the month is merely:
Convert(int,(D+6)/7)
ASKER
Thanks Guys
I have used PortletPauls Solutions to add and populate the day ordinals. Thanks also to Phillip, your solution worked, but on this occasion I prefer the other option
Andy
I have used PortletPauls Solutions to add and populate the day ordinals. Thanks also to Phillip, your solution worked, but on this occasion I prefer the other option
Andy
Of the top of my head, it would seem that you want something like
Open in new window
but it depends on your calendar table.