Using WEEK as calculation is prone to at least two errors. In this case, if you are using a sunday you will get wrong result.

See this repro

DECLARE@Basedate DATETIME =...

Remember that SET DATEFIRST will recompile your code. And also, not everyone has DATEFIRST 1 as default setting.

Try this code that is safe and doesn't rely on any setting and...

-- Prepare sample data

DECLARE@Sample TABLE

(

Value INT NOT NULL

);

INSERT@Sample

(

Value

)

SELECTNumber

FROMmaster.dbo.spt_values

WHERE[Type] = 'P';

-- SwePeso

DECLARE@BatchSize TINYINT = 5;

WITH cteSource(Value, theGrp)

AS (

SELECTValue,

(ROW_NUMBER() OVER (ORDER BY Value) - 1) / @BatchSize AS theGrp

FROM@Sample

)

SELECTd.theGrp,

STUFF(f.Data, 1, 1, '')...

DECLARE@Sample TABLE

(

Data XML NOT NULL

);

INSERT@Sample

(

Data

)

VALUES(N'<bookstore><book><title lang="eng">Harry Potter</title><price>29.99</price></book><book><title lang="eng">Learning XML</title><price>39.95</price></book></bookstore>');

-- SwePeso

SELECTb.n.value('(title)[1]', 'NVARCHAR(MAX)') AS Title,

b.n.value('(title/@lang)[1]', 'NVARCHAR(MAX)') AS Lang,

b.n.value('(price)[1]', 'MONEY') AS Price

FROM@Sample AS s

CROSS APPLYs.Data.nodes('(bookstore/book)') AS b(n);

Keep it simple...DECLARE@Sample TABLE

(

theDate DATETIME NOT NULL

);

INSERT@Sample

(

theDate

)

SELECTDATEADD(DAY, Number, '20130101')

FROMmaster.dbo.spt_values

WHERE[Type] = 'P';

-- SwePeso

SELECTtheDate,

DATEPART(YEAR, DATEADD(MONTH, -3, theDate)) AS FiscalYear,

(DATEPART(DAYOFYEAR, DATEADD(MONTH, -3, theDate)) + 6) / 7 AS FiscalWeek

FROM@Sample

ORDER BYtheDate;

Need OverSum and UnderSum calculations?

See http://weblogs.sqlteam.com/peterl/archive/2008/08/12/How-to-sum-up-an-unknown-number-of-records.aspx

and http://weblogs.sqlteam.com/peterl/archive/2008/11/23/Bin-packaging.aspx

SELECTDATEADD(DAY, Number, '17530101')

FROM(

VALUES(DATEDIFF(DAY, '17530108', GETDATE())),

(DATEDIFF(DAY, '17530115', GETDATE())),

(DATEDIFF(DAY, '17530122', GETDATE())),

(DATEDIFF(DAY, '17530129', GETDATE())),

(DATEDIFF(DAY, '17530205', GETDATE())),

(DATEDIFF(DAY, '17530212', GETDATE()))

) AS d(Number)

Yes, I did indeed. However, the time at my place was 3 am and time to go to bed...

Here is a further reduced code (one less addition)(DATEPART(DAYOFYEAR, DATEADD(DAY, DATEDIFF(DAY, '17530101',...

Or this, if you want to get rid of all Microsoft dependencies.

This will work for SQL 2000 and onwards, and on all dates between 00010101 and 99991231.

CREATE FUNCTION dbo.fnISOWEEK

(

@Year...

Until Microsoft has fixed all date function to be DATE compliant, try thisCREATE FUNCTION dbo.fnISOWEEK

(

@Date DATE

)

RETURNS TINYINT

AS

BEGIN

RETURN(

SELECTCASE

WHEN nextYear <= theDate THEN 0

WHEN currYear <= theDate THEN (theDate - currYear) /...

Any January 1st will do, as long as it is a monday in a non-leapyear.

I am running on SQL Server 2012 and have tested every January 1st between 0001 and...

DECLARE@Sample TABLE

(

Col1 VARCHAR(20) NOT NULL,

Col2 INT NOT NULL

);

INSERT@Sample

(

Col1,

Col2

)

VALUES('SwePeso', 1),

('Demo', 2);

SELECTCol2 AS [Element/@Age],

Col1 AS [Element]

FROM@Sample

FOR XMLPATH(''),

ROOT('Data');

If you prefix your column name with "@", the value becomes an attribute instead of an element.

See your post here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=187048

and my answer.

With SQL Server 2012, there is no longer need for the Quirky update, since the windowed functions supports window framing.