Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to get the Nth weekday of a month from a calendar table

Posted on 2015-02-03
12
Medium Priority
?
124 Views
Last Modified: 2015-02-04
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
0
Comment
Question by:Andy Green
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40586819
You need to post the syntax of your calendar table, as it will depend on what information you currently have.

Of the top of my head, it would seem that you want something like

With myTable as (
Select MyDate, MyWeekDay, ROW_NUMBER() OVER(Partition by Month Order By MyDate) as MyRowNumber
From myCalendarTable
WHERE MyWeekDay = 2)

Select MyDate
Where MyRowNumber = 2

Open in new window


but it depends on your calendar table.
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40586874
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
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 200 total points
ID: 40586885
Then try this:

With myTable as (
Select D, DW, ROW_NUMBER() OVER(Partition by M Order By D) as MyRowNumber
From zCalendar
WHERE DW = 2)

Select D
Where MyRowNumber = 2

Open in new window

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 3

Author Comment

by:Andy Green
ID: 40586903
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
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40586917
Then it would be Day between 8 and 14 and DW = 2
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40586941
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
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40587953
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
;

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40587965
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

Open in new window

0
 
LVL 3

Author Comment

by:Andy Green
ID: 40588059
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
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1800 total points
ID: 40588074
I see. Try it like this. Substitute "blah" with column name of your choice. & Change the data type of blah to suit.



alter table zCalendar add [blah] int
;

update zCalendar
set blah = 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, blah
from zCalendar
order by dt
;
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40588088
>> 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)
0
 
LVL 3

Author Closing Comment

by:Andy Green
ID: 40588147
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
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question