Nogle ældre versioner af metadata havde desværre fejl i ugenumrene.
Det er let at rette ugenumrene til korrekte ISO ugenumre, dvs. de ugenumre, der anvendes bredt i Europa med nedenstående scripts:
For at rette ugenumrene skal du gøre flg.:
1. Åbn SQL Server Management Studio
2. Åbn en query
3. Vælg databasen ”AcintaXXMeta” (NN er systemnavnet)
4. Indsæt nedenstående SQL og eksekvér det
create function dbo.F_ISO_WEEK_OF_YEAR
(
@Date datetime
)
returns int
as
/*
Function F_ISO_WEEK_OF_YEAR returns the
ISO 8601 week of the year for the date passed.
*/
begin
declare @WeekOfYear int
select
-- Compute week of year as (days since start of year/7)+1
-- Division by 7 gives whole weeks since start of year.
-- Adding 1 starts week number at 1, instead of zero.
@WeekOfYear =
(datediff(dd, case -- Case finds start of year
when NextYrStart <= @date then NextYrStart
when CurrYrStart <= @date then CurrYrStart
else PriorYrStart
end,@date)/7)+1
from
(
select
-- First day of first week of prior year
PriorYrStart =
dateadd(dd, (datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690),
-- First day of first week of current year
CurrYrStart =
dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690),
-- First day of first week of next year
NextYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690)
from
(
select
--Find Jan 4 for the year of the input date
Jan4 =
dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0))
) aa
) a
return @WeekOfYear
end
go
UPDATE AcTimeDim
SET CalendarWeek = CAST(CASE WHEN (dbo.F_ISO_WEEK_OF_YEAR(AcDate) > 50) AND(Month(AcDate) = 1) THEN YEAR(AcDate) - 1 ELSE YEAR(AcDate)
END AS CHAR(4)) + '-' + RIGHT('0' + CAST(dbo.F_ISO_WEEK_OF_YEAR(AcDate) AS VARCHAR(2)), 2)
