Post reply

DATEDIFF - WEEK PROBLEM

  • October 26, 2009 at 6:42 am

    #135884

    Dear Friends,

    I would like to know if somebody can help me. I need to generate the number of weeks in a table, we have projects bigger than 1 year, when I use the DateDiff, its return the weeks but when are in the week 53, its skip the week number 1...

    Set rs = CreateObject("ADODB.Recordset")

    sSQL = "SELECT Disp.Colaborador Colaborador , "

    numWeek = datediff("ww",startProject,EndProject)

    for i = 0 to numWeek - 1

    weeknumber = datepart("ww", dateadd("ww", i, startProject))

    sSQL = sSQL + " SUM(CASE WHEN DATEPART(ww, Data) = " & weeknumber & " THEN 1 ELSE 0 END) [" & weeknumber & "], "

    next

    sSQL = sSQL + "COUNT(*) [Total] "

    sSQL = sSQL + "FROM [Disponibilidade RH] Disp "

    sSQL = sSQL + "LEFT OUTER JOIN RH ON Disp.Colaborador = RH.Colaborador "

    sSQL = sSQL + "WHERE TIPO = '"& ProjectName &"'"

    sSQL = sSQL + "GROUP by Disp.Colaborador "

    sSQL = sSQL + "ORDER BY Disp.Colaborador "

    Thank u very much

    Flávio Santos

    Lisbon - Portugal

  • October 26, 2009 at 6:48 am

    #1070652

    Please don't cross post. It just wastes peoples time and fragments replies.

    No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic808633-338-1.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply