June 18, 2009 at 4:05 pm
Greetings everyone 😀
So I had a query that was working. I have a calendar dimension that I am trying to dynamically calculate out fiscal days and week (we have fiscal year and months, we want more granular reporting). So I cam up with script that gives me the fiscal day of the year, and then the fiscal week based on that number.
About an hour ago, when I ran select datename(week, 7) I got 1. If I ran this as select datename(week,8). I got 2. I tested it at 15 and got 3, and at 22 and got 4. So in my mind, datename(week, n) was giving me the week number based on the day number right? This was working, I ran it a hundred times to validate. Then as I added it into my master query, when datename(week, 7) became 2!! So I retested it on my test query. It was 1 not 2. So I took my final code down into the EXACT same code, and they gave me different results! Now an hour later when I run it datename(week, 6) = 2 as well!
I haven't changed anything! I did not change the query options, no one changed the table, no one is even in the database but me.
Anyone have any ideas what is going on? am I missing something? Is there a simpler way to get what week it is based on the day number of the year?
any help on this would be much appreciated!
Link to my blog http://notyelf.com/
June 18, 2009 at 4:21 pm
[font="Verdana"]Datename(datepart, date) (from Books Online). So the function isn't based on a day number per se, but on a date.
Can you post the actual code for us to have a look at please?
Oh, and also from Books Online:
For versions of SQL Server later than SQL Server 2000, when the date parameter has a date data type argument, the return value depends on the setting specified by using SET DATEFIRST
[/font]
June 18, 2009 at 4:28 pm
Yes, I saw that to, I guess the part I get hung up on is I validated it earlier and it worked. I must have missed something or did something wrong while validating.
Anyways here is my code
SELECT
C1.Calendar_Date
,C4.YearStart
,FiscalDayNum = DATEDIFF(d, C4.YearStart, C1.Calendar_Date)
,FiscalWeek = datename(week, DATEDIFF(d, C4.YearStart, C1.Calendar_Date))
FROM dbo.Dim_Calendar AS C1
JOIN (SELECT FiscalYear, Min(Calendar_Date) -1 AS YearStart FROM dbo.Dim_Calendar GROUP BY FiscalYear) AS C4
ON C1.FiscalYear = C4.FiscalYear
Calendar_date is the calendar_date obviously, Year start is the first day of our fiscal year - 1. So for instance day 1 of fiscal year 2008 is 12/30/2007. I made it -1 so in the date diff from calendar date, the first day would = 1 not 0.
The day numbers come out perfect, so all I need is to get the week number based off of this and I am good to go 🙂
Link to my blog http://notyelf.com/
June 18, 2009 at 4:29 pm
Sorry here is a sample of the data this returns.
Calendar_DateYearStartFiscalWeekFiscalDayNum
2007-12-30 00:00:002007-12-29 00:00:0011
2007-12-31 00:00:002007-12-29 00:00:0012
2008-01-01 00:00:002007-12-29 00:00:0013
2008-01-02 00:00:002007-12-29 00:00:0014
2008-01-03 00:00:002007-12-29 00:00:0015
2008-01-04 00:00:002007-12-29 00:00:0026
2008-01-05 00:00:002007-12-29 00:00:0027
2008-01-06 00:00:002007-12-29 00:00:0028
2008-01-07 00:00:002007-12-29 00:00:0029
2008-01-08 00:00:002007-12-29 00:00:00210
2008-01-09 00:00:002007-12-29 00:00:00211
2008-01-10 00:00:002007-12-29 00:00:00212
2008-01-11 00:00:002007-12-29 00:00:00313
2008-01-12 00:00:002007-12-29 00:00:00314
2008-01-13 00:00:002007-12-29 00:00:00315
2008-01-14 00:00:002007-12-29 00:00:00316
2008-01-15 00:00:002007-12-29 00:00:00317
2008-01-16 00:00:002007-12-29 00:00:00318
2008-01-17 00:00:002007-12-29 00:00:00319
2008-01-18 00:00:002007-12-29 00:00:00420
2008-01-19 00:00:002007-12-29 00:00:00421
2008-01-20 00:00:002007-12-29 00:00:00422
2008-01-21 00:00:002007-12-29 00:00:00423
2008-01-22 00:00:002007-12-29 00:00:00424
2008-01-23 00:00:002007-12-29 00:00:00425
2008-01-24 00:00:002007-12-29 00:00:00426
2008-01-25 00:00:002007-12-29 00:00:00527
2008-01-26 00:00:002007-12-29 00:00:00528
2008-01-27 00:00:002007-12-29 00:00:00529
2008-01-28 00:00:002007-12-29 00:00:00530
Link to my blog http://notyelf.com/
June 18, 2009 at 5:27 pm
[font="Verdana"]It looks like the fiscal week is tracking to me. What's the issue with it?
That's kind of clever taking the date difference in days between the calendar date and the fiscal date to get the day number.
Is the issue that the first week is too short? But the week number will depend on what day of the week the start of the year falls on, as SQL Server always measures the start of the week from a fixed day of the week.
As an alternative, you could try using modulo 7 on the day number, which will always give you a result between 0 and 6 (so add one to it.)
(FiscalDayNum % 7) + 1 as FiscalWeek
You're going to end up with your fiscal weeks starting on random days in the week though.
[/font]
June 19, 2009 at 1:33 pm
Thank you everyone for your responses, I have managed to come up with a solution. I got the Fiscal calendar broken down by week from Finance and reviewed it. I realized that only the first week was off, so I created a subquery to handle the problem with the first week. Here is the entirety of my code should anyone ever want to use it for doing their own calendar dimension. I realize there are a lot of columns, but our reporting guy wanted the extras just in case, and I decided I wanted a challenge :-D.
SELECT
C1.Calendar_Idx
,C1.Division_Idx
,C1.Calendar_Company_Code
,C1.Calendar_Division_Code
,C1.Calendar_Division_Name
,C1.WorkingDaySerialNumber
,C1.BankDaySerialNumber
,C1.DayNumber
-- Fiscal related columns
,C1.FiscalYear
,FiscalSeason = CASE WHEN RIGHT(C1.Calendar_Idx,4) BETWEEN 0101 AND 0630 THEN 'Spring' ELSE 'Fall' END
,C1.Fiscal_Quarter
,C1.Fiscal_QuarterOfYear
,C2.FiscalQuarterBeginDate
,C2.FiscalQuarterEndDate
,C2.FiscalQuarterTimeSpan
,FiscalPeriod = CAST(Right(C1.Calendar_PeriodType1,2) AS TINYINT)
,FiscalPeriodOfYear = C1.Calendar_PeriodType1
,FiscalPeriodNumOverall = ((C1.FiscalYear - 2008) * 12) + CAST(Right(C1.Calendar_PeriodType1,2) AS TINYINT)
,FiscalPeriodBeginDate = FiscalperiodBeginDate
,FiscalPeriodEndDate = FiscalperiodEndDate
,FiscalPeriodTimeSpan = FiscalperiodTimeSpan
,FiscalMonthText = DATENAME(m, CAST(left(right(C1.Calendar_Idx, 4),2)+ '/' + Right(C1.Calendar_Idx, 2) + '/' + left(C1.Calendar_Idx, 4) AS SMALLDATETIME))
,FiscalWeekDayFlag = BankDay
,FiscalWeek = CASE WHEN C1.Calendar_Idx BETWEEN C5.FirstWeekStart AND C5.FirstWeekEnd THEN 1 ELSE DATENAME(WEEK, DATEDIFF(dd, C4.YearStart, C1.Calendar_Date) -2) END
,FiscalWeekOfYear = CASE WHEN C1.Calendar_Idx BETWEEN C5.FirstWeekStart AND C5.FirstWeekEnd THEN C1.FiscalYear + '1' ELSE C1.FiscalYear + DATENAME(WEEK, DATEDIFF(dd, C4.YearStart, C1.Calendar_Date) -2) END
,FiscalWeekBeginDate = CASE WHEN C1.Calendar_Idx BETWEEN C5.FirstWeekStart AND C5.FirstWeekEnd THEN C5.FirstWeekStart ELSE C5.FiscalWeekBeginDate END
,FiscalWeekEndDate = CASE WHEN C1.Calendar_Idx BETWEEN C5.FirstWeekStart AND C5.FirstWeekEnd THEN C5.FIrstWeekEnd ELSE C5.FiscalWeekEndDate END
,FiscalDayNum = DATEDIFF(dd, C4.YearStart, C1.Calendar_Date)
,FiscalDayText = DATENAME(dw,DATEDIFF(dd, C4.YearStart, C1.Calendar_Date)- 1)
-- Standard Calendar related columns
,C1.Calendar_Year
,C1.Calendar_Quarter
,C1.Calendar_QuarterofYear
,CalendarQuarterBeginDate = C6.CalendarQuarterBeginDate
,CalendarQuarterEndDate = C6.CalendarQuarterEndDate
,CalendarQuarterTimeSpan = C6.CalendarQuarterTimeSpan
,CalendarMonthNum = DATEPART(m, Calendar_Date)
,CalendarMonthText = DATENAME(MONTH, Calendar_Date)
,CalendarMonthNumOverall = ((C1.Calendar_Year - 2008) * 12) + DATEPART(m,calendar_Date)
,CalendarMonthBeginDate = CalendarperiodBeginDate
,CalendarMonthEndDate = CalendarperiodEndDate
,CalendarMonthTimeSpan = CalendarperiodTimeSpan
,C1.Calendar_Week
,CalendarWeekBeginDate = CalendarWeekBeginDate
,CalendarWeekEndDate = CalendarWeekEndDate
,C1.Calendar_Date
,CalendarDayNum = DATEPART(d,Calendar_Date)
,CalendarDayText = DATENAME(dw,Calendar_Date)
,CalendarSameWeekDayYearAgo = DATEADD(dd, -365,Calendar_Date)
,Calendar_IdxSameWeekDayYearAgo = Calendar_Idx - 10000
FROM dbo.Dim_Calendar AS C1
JOIN
-- Fiscal: Generates Quarter Begin and End dates as well as Quarter Time Span
(SELECT Division_Idx, Fiscal_QuarterOfYear, MAX(Calendar_Idx) AS FiscalQuarterEndDate , MIN(Calendar_Idx) AS FiscalQuarterBeginDate,CAST(MAX(Calendar_Date) - MIN(Calendar_Date) AS INT) AS FiscalQuarterTimeSpan
FROM dbo.Dim_Calendar GROUP BY Division_Idx, Fiscal_QuarterOfYear) AS C2
ON C1.Division_Idx = C2.Division_Idx
AND C1.Fiscal_QuarterOfYear = C2.Fiscal_QuarterOfYear
JOIN
-- Fiscal: Generates Period Begin and End dates as well as Period Time Span
(SELECT Division_Idx, Calendar_PeriodType1, MAX(Calendar_Idx) AS FiscalperiodEndDate , MIN(Calendar_Idx) AS FiscalperiodBeginDate,CAST(MAX(Calendar_Date) - MIN(Calendar_Date) AS INT) AS FiscalperiodTimeSpan
FROM dbo.Dim_Calendar GROUP BY Division_Idx, Calendar_PeriodType1) AS C3
ON C1.Division_Idx = C3.Division_Idx
AND C1.Calendar_PeriodType1 = C3.Calendar_PeriodType1
JOIN
-- Fiscal: Generates the first day of the fiscal year for specific mathematical reasons in main query
(SELECT Division_Idx, FiscalYear, MIN(Calendar_Date) -1 AS YearStart FROM dbo.Dim_Calendar GROUP BY Division_Idx, FiscalYear) AS C4
ON C1.Division_Idx = C4.Division_Idx
AND C1.FiscalYear = C4.FiscalYear
JOIN
-- Fiscal: Generates the first and last day of each fiscal Week, also includes math to handle first fiscal Week in year
(SELECT C1.Division_Idx, C1.FiscalYear + DATENAME(WEEK, DATEDIFF(dd, C4.YearStart, C1.Calendar_Date) -2) AS FiscalWeekOfYear , MIN(Calendar_Idx) AS FiscalWeekBeginDate, MAX(Calendar_Idx) AS FiscalWeekEndDate,
C4.FirstWeekStart, C4.FirstWeekEnd
FROM dbo.Dim_Calendar AS C1
JOIN(SELECT Division_Idx, FiscalYear, MIN(Calendar_Date) -1 AS YearStart, CAST(CONVERT(NCHAR(8),MIN(Calendar_Date),112) AS DECIMAL(8,0)) AS FirstWeekStart
,CAST(CONVERT(NCHAR(8),MIN(Calendar_Date) + 6,112) AS DECIMAL(8,0)) AS FirstWeekEnd
FROM dbo.Dim_Calendar GROUP BY Division_Idx, FiscalYear) AS C4
ON C1.Division_Idx = C4.Division_Idx
AND C1.FiscalYear = C4.FiscalYear
GROUP BY C1.Division_Idx, C1.FiscalYear + DATENAME(WEEK, DATEDIFF(dd, C4.YearStart, C1.Calendar_Date) -2) , C4.FirstWeekStart, C4.FirstWeekEnd) AS C5
ON C1.Division_Idx = C5.Division_Idx
AND C1.FiscalYear + DATENAME(WEEK, DATEDIFF(dd, C4.YearStart, C1.Calendar_Date) -2) = C5.FiscalWeekOfYear
JOIN
-- Calendar: Generates Quarter Begin and End dates as well as Quarter Time Span
(SELECT DivisIon_Idx, Calendar_QuarterofYear, MAX(Calendar_Idx) AS CalendarQuarterEndDate , MIN(Calendar_Idx) AS CalendarQuarterBeginDate,CAST(MAX(Calendar_Date) - MIN(Calendar_Date) AS INT) AS CalendarQuarterTimeSpan
FROM dbo.Dim_Calendar GROUP BY Division_Idx, Calendar_QuarterofYear) AS C6
ON C1.Division_Idx = C6.Division_Idx
AND C1.Calendar_QuarterofYear = C6.Calendar_QuarterofYear
JOIN
-- Calendar: Generates Period Begin and End dates as well as Period Time Span
(SELECT Division_Idx, CAST(year(Calendar_Date) AS NCHAR(4)) + CAST(Month(Calendar_Date) AS NVARCHAR(2)) AS MonthOfYear , MAX(Calendar_Idx) AS CalendarperiodEndDate , MIN(Calendar_Idx) AS CalendarperiodBeginDate
,CAST(MAX(Calendar_Date) - MIN(Calendar_Date) AS INT) AS CalendarperiodTimeSpan
FROM dbo.Dim_Calendar GROUP BY Division_Idx, CAST(year(Calendar_Date) AS NCHAR(4)) + CAST(Month(Calendar_Date) AS NVARCHAR(2))) AS C7
ON C1.Division_Idx = C7.Division_Idx
AND CAST(year(C1.Calendar_Date) AS NCHAR(4)) + CAST(Month(C1.Calendar_Date) AS NVARCHAR(2)) = C7.MonthOfYear
JOIN
-- Calendar: Generates Week start and end dates
(SELECT Division_Idx, Calendar_Year, Calendar_Week, MAX(Calendar_Idx) AS CalendarWeekEndDate, MIN(Calendar_Idx) AS CalendarWeekBeginDate
FROM dbo.Dim_Calendar GROUP BY Division_Idx, Calendar_Year, Calendar_Week ) AS C8
ON C1.Division_Idx = C8.Division_Idx
AND C1.Calendar_Year = C8.Calendar_Year
AND C1.Calendar_Week = C8.Calendar_Week
WHERE
C1.Calendar_PeriodType1 BETWEEN 200801 AND CAST(YEAR(GETDATE()) + 2 AS NCHAR(4)) + CAST(12 AS NCHAR(2))
AND C1.Division_Idx = 2
Link to my blog http://notyelf.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply