|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Monday, December 28, 2009 11:57 AM
Points: 5,
Visits: 8
|
|
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
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: 2 days ago @ 1:32 AM
Points: 136,
Visits: 770
|
|
Hi Flávio,
If I understand your problem correctly, when DATEDIFF(WW, StartDate, EndDate) > 52, you experience a rollover? (eg 53 => 1) I can't seem to replicate your problem, and perhaps a senior here could help out in telling us why this might be happening to you.
Else, you might want to consider using: (DATEDIFF(YY, StartDate, EndDate) * 52) + DATEDIFF(WW, StartDate, EndDate)
I don't think that this is the best approach to solving your problem and would really like to see what the seniors have to say.
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Monday, May 20, 2013 12:09 PM
Points: 2,550,
Visits: 18,882
|
|
It's the datepart function that's causing you problems, because it's specifically looking at the week within the year. From BOL:
The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.
Can't say as I fully understand why you need this broken out in this manner, but if you just returned the datediff instead of the datepart, you'd get your correct numbers above 52.
Example:
DECLARE @start datetime, @end datetime
SET @start = '1/1/2008' SET @end = '2/1/2009'
SELECT datediff(ww,@start,@end) SELECT datepart("ww", dateadd("ww", datediff(ww,@start,@end), @start))
--------------------------------------------------------- How best to post your question How to post performance problems Tally Table:What it is and how it replaces a loop
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 113,
Visits: 413
|
|
How about something like this:
with DateCTE as ( select cast('01/01/1998' as datetime) DateValue union all select DateValue + 1 from DateCTE where DateValue + 1 < '1/1/2016' )
Select cast(CAST(DateValue AS CHAR(11)) AS DateTime) AS [FullDate], DATEPART(wk, DateValue) AS WeekOfYear, DATEPART(yy, DateValue) AS CalendarYear from DateCTE OPTION (MAXRECURSION 0)
G. Milner
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Monday, December 28, 2009 11:57 AM
Points: 5,
Visits: 8
|
|
Hi DiamondDgm, thanks. I hope somebody help us...
And I wouldn't like to want this (DATEDIFF(YY, StartDate, EndDate) * 52) + DATEDIFF(WW, StartDate, EndDate)
Eg. A Project has startDate = 2009-06-01 and endDate = 2010-01-29 WeekNumber = 34 First week = 23 last week = 4
Table Name ---------------------------------------------------------------------------- Week------------------------------------------------------------- --------------------23--24--25--26--27--28--29- 30--31--32--33--34--35--36---37--38--39--40--41--42--43--44--45--46--47--48--49--50--51---52--53---2--3---4 ARTUR CARVALHO--- 5---3---5---5---5---5---5---5---5----5---5---5---5---4----5----5---3---0----2---5---5---5---5---5---5---5---4----4----5---4---4---5---5---5 JIM CARTER--------- 5---3---5---5---5---5---5---5---5----5---5---5---5---4----5----5---3---0----2---5---5---5---5---5---5---5---4----4----5---4---4---5---5---5
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Monday, December 28, 2009 11:57 AM
Points: 5,
Visits: 8
|
|
Hi Everybory,
I got something like my problem, but I dont know how and if it can help me.
http://support.microsoft.com/kb/200299/pt
http://www.rondebruin.nl/weeknumber.htm
Thank u very much !
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Monday, December 28, 2009 11:57 AM
Points: 5,
Visits: 8
|
|
Link in English
http://support.microsoft.com/kb/200299/en
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: 2 days ago @ 1:32 AM
Points: 136,
Visits: 770
|
|
Hi Flavio,
Based on that article (http://support.microsoft.com/kb/200299/en), it would seem that your error is a result of bug in a VB library. Looking closer at your code, you are using a VB function - DATEDIFF (that have the same name in T-SQL). Perhaps you would like to use SQL code to populate your variable?
numWeek = datediff("ww",startProject,EndProject) ==> PSUEDO: int numWeek; string s_SQL; string s_SQL = "SELECT datediff("ww",startProject,EndProject)"; numWeek = Execute(s_SQL);
I'm sorry, but I don't know VB syntax so I've written it in a pseudo syntax of sorts.
Doing this should avoid the bug in Oleaut32.dll, unless SQL Server is making use of this DLL too? I don't know tha answer to that.
Seniors, please advise if I am making any mistakes here. Thank you.
|
|
|
|