Skip to main content

First Week Number of Year

Comments

8 comments

  • Blake

    Dates are fun aren't they!?!  I found the best solution is to have a dates table in the database in which almost all of my queries get joined to.  I can give you more details if running dates through a table is a viable solution for you.

    0
  • Steve Jarvis

    Thanks Blake. This could be a good option. How do you set this up and how often? I do have a few default tables but never thought I would need one for week number.

    Dates are always a pain especially since our Mainframe DB is formatted as yyyymmdd. However, I would expect out-of-the-box support for correctly aligned week numbers from Info Studio.

    Much appreciated.

    0
  • Blake

    I hear ya on all of the random formats floating around.  I have a DateDim table that covers this as well.  The idea comes form Ralph Kimball and if you search the net there are some sample scripts.  There is some info at https://arcanecode.com/2009/11/18/populating-a-kimball-date-dimension/.  There is so much you can do with this from populating drop downs to find the start or end of a month to normalize dates or easily compare to a future or previous year.

    Once populated it should be pretty static if you add enough years in the future.

    I exported some of my data to send to you but I'm not seeing a way to add a bigger script here.  I added a screenshot of some of the data.
    Here is what the table def looks like

    CREATE TABLE [dbo].[DateDim](

    [DateId] [int] NOTNULL,

    [DateVal] [date] NULL,

    [DayOfWeek] [varchar](10)NULL,

    [WeekOfYear] [int] NULL,

    [CalendarMonth] [varchar](10)NULL,

    [CalendarMonthShort] [varchar](3)NULL,

    [CalendarYear] [int] NULL,

    [CalendarQuarter] [int] NULL,

    [HolidayIndicator] [bit] NULL,

    [WeekendIndicator] [bit] NULL,

    [FirstDayOfMonthIndicator] [bit] NULL,

    [LastDayOfMonthIndicator] [bit] NULL,

    [StandardDate] [char](10)NULL,

    [Day] [varchar](2)NULL,

    [DaySuffix] [varchar](4)NULL,

    [DayOfWeekNumber] [char](1)NULL,

    [DayOfWeekInMonth] [varchar](2)NULL,

    [DayOfWeekInYear] [varchar](2)NULL,

    [DayOfQuarter] [varchar](3)NULL,

    [DayOfYear] [varchar](3)NULL,

    [WeekOfMonth] [varchar](1)NULL,

    [WeekOfQuarter] [varchar](2)NULL,

    [MonthNumber] [int] NULL,

    [MonthOfQuarter] [varchar](2)NULL,

    [QuarterName] [varchar](9)NULL,

    [YearName] [char](7)NULL,

    [MonthYear] [char](10)NULL,

    [MMYYYY] [char](6)NULL,

    [FirstDayOfMonthDateVal] [date] NULL,

    [FirstDayOfMonthDateId] [int] NOTNULL,

    [LastDayOfMonthDateVal] [date] NULL,

    [LastDayOfMonthDateId] [int] NOTNULL,

    [FirstDayOfQuarterDateVal] [date] NULL,

    [FirstDayOfQuarterDateId] [int] NOTNULL,

    [LastDayOfQuarterDateVal] [date] NULL,

    [LastDayOfQuarterDateId] [int] NOTNULL,

    [FirstDayOfYearDateVal] [date] NULL,

    [FirstDayOfYearDateId] [int] NOTNULL,

    [LastDayOfYearDateVal] [date] NULL,

    [LastDayOfYearDateId] [int] NOTNULL,

    [PreviousYearDateVal] [date] NULL,

    [PreviousYearDateId] [int] NOTNULL,

    [FirstDayOfPreviousYearDateVal] [date] NULL,

    [FirstDayOfPreviousYearDateId] [int] NOTNULL,

    [LastDayOfPreviousYearDateVal] [date] NULL,

    [LastDayOfPreviousYearDateId] [int] NOTNULL,

    [WeekdayIndicator] [bit] NULL,

    [Holiday] [varchar](50)NULL

    )
    1
  • Steve Jarvis

    Blake, again this is much appreciated and I shall pass this on to my "DBA" (we don't have one!), so I will probably be running this. I can add this to a annual reminder.

    We are a Mainframe team, Assembler guys, data had been pushed to MySQL and the boss wanted a BI tool to analyze it. However the person that created the DB, whilst doing an excellent job, did create dates in multiple formats including varchar 10! I have had so much fun working with these dates!

    Hitting Ctl-+ a few times enabled me to view your table. Question, does this allow for combining end of year/first of year dates into the same week number?

    Thanks.

    0
  • Blake

    I don't think my version gets what you want, but the beauty of it is that you can control how that works when you populate the table.  Note: you should only have to create this once.  I created mine out to 2050. I found a sample excel generator.  You'll likely have to tell the browser this is a safe download http://www.kimballgroup.com/wp-content/uploads/2014/03/Ch3-SampleDateDim.xls.

     

     

     

    1
  • Blake

    Also a note if you have multiple formats that mean the same thing you could add these to the table and then just simply join to that alternate format and then use the normal DateVal.

    0
  • Steve Jarvis

    Thanks for sharing this extra information, it is much appreciated. I downloaded the Excel, it is pretty darned amazing! I like the idea of joining a table containing date formats. I have been reformatting most of the dates in MySQL.

    I just noticed I had given this the incorrect heading, I have updated it.

    Cheers!!

    0
  • Steve Jarvis

    I believe I may be getting close to a solution for the week splitting between years, I.E. Week 53/1. It looks like this is a MySQL problem. Thanks to Steve Murfitt.

    Adding the following put the dates into the same Week Number.
    WeekOfYear(table.date) As WeekNumber

    However, this put the dates in the last week of the previous year. We are working on how to make the first week of year the one that contains Jan 1.

    0

Please sign in to leave a comment.