| Value |
 Expression: "DECLARE @CurrentDay date = '" +@[User::CurrentDayDate] + "'
DECLARE @FiscalYear varchar(7)
DECLARE @FiscalYearStart varchar(7)
DECLARE @Year int
DECLARE @TempDate date
SET @Year = YEAR(@CurrentDay)
SET @TempDate =
(
CASE
WHEN MONTH(@CurrentDay) = 7 THEN DATEADD(mm, -1,@CurrentDay)
ELSE @CurrentDay
END
)
-- If we are in July, set the Fiscal Year to prior Fiscal Year since it is the last closed period
SET @FiscalYear = (SELECT FiscalYear
FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimDate] WHERE FullDate = @TempDate)
SET @Year = CAST(SUBSTRING(@FiscalYear,4,4) as int)
SET @Year = @Year - 15
SET @FiscalYearStart = 'FY' + ' ' + CAST(@Year as varchar(4))
UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimDate]
SET
[IsCurrentFiscalYear] =
(
CASE
WHEN FiscalYear <
DECLARE @CurrentDay date = '2016-03-04'
DECLARE @FiscalYear varchar(7)
DECLARE @FiscalYearStart varchar(7)
DECLARE @Year int
DECLARE @TempDate date
SET @Year = YEAR(@CurrentDay)
SET @TempDate =
(
CASE
WHEN MONTH(@CurrentDay) = 7 THEN DATEADD(mm, -1,@CurrentDay)
ELSE @CurrentDay
END
)
-- If we are in July, set the Fiscal Year to prior Fiscal Year since it is the last closed period
SET @FiscalYear = (SELECT FiscalYear
FROM [EnterpriseDW].[dbo].[DimDate] WHERE FullDate = @TempDate)
SET @Year = CAST(SUBSTRING(@FiscalYear,4,4) as int)
SET @Year = @Year - 15
SET @FiscalYearStart = 'FY' + ' ' + CAST(@Year as varchar(4))
UPDATE [EnterpriseDW].[dbo].[DimDate]
SET
[IsCurrentFiscalYear] =
(
CASE
WHEN FiscalYear <> @FiscalYear THEN 'N'
ELSE 'Y'
END
)
FROM [DimDate]
UPDATE [EnterpriseDW].[dbo].[DimDate]
SET
[IsAvailableForFinance] = 'Y'
FROM [DimDate]
WHERE FiscalYear >= @FiscalYearStart and
FiscalYear <= @FiscalYear
UPDATE [EnterpriseDW].[dbo].[DimDate]
SET
[CalendarPeriodSort] =
(CASE
WHEN
(
MONTH(@CurrentDay) > 6 AND Year(DATEADD(yy, 1,@CurrentDay)) = CAST(RIGHT(FiscalYear,4) AS Int)
OR
Year(@CurrentDay) = CAST(RIGHT(FiscalYear,4) AS Int)
)
AND MONTH(DATEADD(mm, -1,@CurrentDay)) = MonthOfYear
THEN 1000000 + CalendarPeriod
WHEN
(
MONTH(@CurrentDay) > 6 AND Year(DATEADD(yy, 1,@CurrentDay)) <> CAST(RIGHT(FiscalYear,4) AS Int)
OR
Year(@CurrentDay) <> CAST(RIGHT(FiscalYear,4) AS Int)
)
AND 6 = MonthOfYear
THEN 1000000 + CalendarPeriod
ELSE
2000000 + CalendarPeriod
END
)
--Set IsCurrentDay to match value in DWOperations.dbo.[SSIS Configurations] Var_CurrentDayDate
--(This is passed in to package using pacakge configurations)
UPDATE dbo.DimDate
SET IsCurrentDay = 'N'
WHERE IsCurrentDay = 'Y'
UPDATE dbo.DimDate
SET IsCurrentDay = 'Y'
WHERE FullDate = @CurrentDay
|
| Expression |
"DECLARE @CurrentDay date = '" +@[User::CurrentDayDate] + "'
DECLARE @FiscalYear varchar(7)
DECLARE @FiscalYearStart varchar(7)
DECLARE @Year int
DECLARE @TempDate date
SET @Year = YEAR(@CurrentDay)
SET @TempDate =
(
CASE
WHEN MONTH(@CurrentDay) = 7 THEN DATEADD(mm, -1,@CurrentDay)
ELSE @CurrentDay
END
)
-- If we are in July, set the Fiscal Year to prior Fiscal Year since it is the last closed period
SET @FiscalYear = (SELECT FiscalYear
FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimDate] WHERE FullDate = @TempDate)
SET @Year = CAST(SUBSTRING(@FiscalYear,4,4) as int)
SET @Year = @Year - 15
SET @FiscalYearStart = 'FY' + ' ' + CAST(@Year as varchar(4))
UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimDate]
SET
[IsCurrentFiscalYear] =
(
CASE
WHEN FiscalYear < |
Properties
