Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DATEDIFF WEEKS!!! Expand / Collapse
Author
Message
Posted Monday, October 26, 2009 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Post #808633
Posted Monday, October 26, 2009 11:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 11:38 PM
Points: 141, Visits: 855
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.
Post #809097
Posted Tuesday, October 27, 2009 9:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:08 PM
Points: 1,328, Visits: 19,288
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."
Post #809386
Posted Tuesday, October 27, 2009 10:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 4:28 PM
Points: 114, Visits: 454
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
Post #809711
Posted Wednesday, October 28, 2009 10:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

Post #810107
Posted Wednesday, October 28, 2009 10:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 !
Post #810122
Posted Wednesday, October 28, 2009 10:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Post #810124
Posted Wednesday, October 28, 2009 12:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 11:38 PM
Points: 141, Visits: 855
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.
Post #810212
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse