Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2010
    Posts
    20

    Unanswered: Working with dates and weeks...?

    I'm trying to find a way to create a table for a list of Periods.
    One period should be 2 weeks, from monday to sunday.
    I'm working with Visual Basic also, but haven't managed to do a query in SQL that inserts the right kind of data... Need help on this one...

    The table containing this list of Periods should have the "ID", "PeriodNumber", "StartDate" and "EndDate".
    -ID is just an identity specification with an identity increment of 1 for every new row. (DONE)
    -PeriodNumber is an integer that refers to the 2-week period. This number is supposed to reset every year. So there are always 26 or 27 periods per year.
    -StartDate is the first date of the period which is a monday. (Going to work with somehing like:
    WHERE DATENAME (DW, StartDate) = 'Monday' )
    -EndDate is the last date of each period which is a sunday 13 days after the StartDate. (Going to work with something like:
    UPDATE Periods
    SET EndDate = (DATEADD(DAY,+13,StartDate))
    WHERE ID = @@IDENTITY


    So,
    -How can I determin the following periods to start every other monday?
    -How can I determin the PeriodNumber? For example, now is week 51 of this year, which would be PeriodNumber 25. Next week would be PeriodNumber 26. Then number should not be determined by the last row, but by the week of the year...
    -Is it possible to add the EndDate in the same query as the the StartDate if I decide to use the "(DATEADD(DAY,+13,StartDate)"?
    -ANY other tips and advices are always welcome!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This may get you started:
    Code:
    with cte as 
    (   
       select nbr = 1
       union all
       select nbr + 1
          from cte
          where nbr < datediff(day, cast(cast(year(getdate()) as char(4)) + '-01-01' as date), cast(cast(year(getdate())+1 as char(4)) + '-01-01' as date))
    )
    select
       PayPeriod = row_number() over (order by nbr)
      ,DayOfWeek = datename(weekday, dateadd(day, nbr-1, cast(cast(year(getdate()) as char(4)) + '-01-01' as date)))
      ,CalDate = dateadd(day, nbr-1, cast(cast(year(getdate()) as char(4)) + '-01-01' as date))
      ,DayOfWeekIn2Weeks = datename(weekday, dateadd(day, nbr-1+13, cast(cast(year(getdate()) as char(4)) + '-01-01' as date)))
      ,CalDateIn2Weeks = dateadd(day, nbr-1+13, cast(cast(year(getdate()) as char(4)) + '-01-01' as date))
      ,WeekNumber = datepart(week, dateadd(day, nbr-1, cast(cast(year(getdate()) as char(4)) + '-01-01' as date)))
      ,DayOfYear = datepart(dayofyear, dateadd(day, nbr-1, cast(cast(year(getdate()) as char(4)) + '-01-01' as date)))
       from cte
       where 1 = /* DayOfWeekNbr */ datepart(weekday, dateadd(day, nbr-1-1, cast(cast(year(getdate()) as char(4)) + '-01-01' as date)))
       and   1 = /* FirstSecond */ case datepart(week, dateadd(day, nbr-1, cast(cast(year(getdate()) as char(4)) + '-01-01' as date))) % 2 when 0 then 1 else 2 end
       option (maxrecursion 0)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    I have this function in sql

    USE [Pars-SQL]
    GO
    /****** Object: UserDefinedFunction [dbo].[weekend] Script Date: 12/23/2010 10:26:13 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE FUNCTION [dbo].[weekend] (@we datetime)
    RETURNS datetime
    AS
    BEGIN
    RETURN DateAdd(dd, (-1 * Coalesce(NullIf(DateDiff(dd, 5, @we) % 7, 0), 7)) + 7, @we)
    END

    it will return a saturday date base on the date it is pass to to
    Then you can group your data by week.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •