Finding the last day of the PERIOD
-
We have a field in a table called 'IsLastDayofPeriod' where we want a 1 or 0 to represent true or false. We follow a 4-4-5 fiscal calendar. This query retrieves several other date fields, and joins with another table that feeds us FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear, Calendar_Year, FiscalDayOfYear, and DayOfWeek. Ideally we want to do this in a one line query (no subqueries). Some years the last quarter will be 4-4-6. Has anybody had to do this before? We're having trouble where the period is a five or six week period.
-
It would be a lot easier to help you if we knew the data structure and contents of the tables you are talking about.
Could you post the DDL for the tables and some sample data?
-
The first table, dbo.DimDate, is created in databaseA, we'll name it, and the second table, ETL.TransformDimDate is created in databaseB. Let's say we have a row where the date is 10/02/94. Below, I will put the value of this record for each field. (I will not put the values in for the second table, because those values go into the DimDate table.) We need logic to decide if this date is the last day of the period (fiscal month).
CREATE
TABLE [dbo].[DimDate](
[Date_Idx] [smalldatetime]
NOT NULL, 10/2/1994 12:00:00 AM
[Date_Code] [nvarchar]
(50) NOT NULL, Tuesday,October the 4th,1994
[FiscalWeek_Name] [nvarchar]
(50) NULL, Sunday,October the 2nd,1994
[Week_Idx] [smalldatetime]
NULL, 10/8/1994 12:00:00 AM
[DayOfWeek] [tinyint]
NULL, 1
[DayNameOfWeek] [nvarchar]
(10) NULL, Sunday
[DayOfMonth] [tinyint]
NULL, 2
[DayOfYear] [smallint]
NULL, 275
[WeekdayWeekend] [char]
(7) NULL, weekend
[MonthName] [nvarchar]
(10) NULL, October
[IsLastDayOfMonth] [char]
(1) NULL, 0
[CalendarQuarter] [tinyint]
NULL, 4
[CalendarYear] [smallint]
NULL, 1994
[CalendarYearMonth] [nvarchar]
(7) NULL, 9410
[CalendarYearQtr] [nvarchar](7) NULL, 9404
[CalendarWeekOfYear] [tinyint]
NULL, 41
[CalendarMonthOfYear] [tinyint]
NULL, 10
[FiscalMonthOfYear] [tinyint]
NULL, 1
[FiscalWeekOfPeriod] [tinyint]
NULL, 1
[FiscalWeekOfYear] [tinyint]
NULL, 1
[FiscalQuarter] [tinyint]
NULL, 1
[FiscalYear] [int]
NULL, 1995
[FiscalYearMonth] [nvarchar]
(9) NULL, 1995 - 1
[FiscalYearQtr] [nvarchar]
(8) NULL, 1995 - 1
[JDADate_Code] [nvarchar]
(50) NULL,
[WMDate_Code] [nvarchar]
(50) NULL,
[IsLastDayofPeriod] [tinyint]
NULL, ???????
[IsLastDayofFiscalQtr] [tinyint]
NULL,
CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED
(
CREATE
TABLE [ETL].[Transform_DimDate](
[Date_Code] [nvarchar]
(50) NOT NULL,
[Date_Name] [nvarchar]
(50) NOT NULL,
[FiscalPeriodOfYear] [int]
NOT NULL,
[FiscalWeekOfPeriod] [int]
NOT NULL,
[FiscalWeekOfYear] [int]
NOT NULL,
[Calendar_Year] [int]
NOT NULL,
[FiscalDayOfYear] [int]
NOT NULL,
[DayOfWeek] [int]
NOT NULL,
)
-
Lindsay,
What are the rules for the first week/first month of the year?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Try this:
select a.[Date_Idx], [IsLastDayofPeriod] = case when a.[Date_Idx] = b.[LastDayofPeriod] then 1 else 0 end from [dbo].[DimDate] a join ( select bb.[FiscalYear], bb.[FiscalMonthOfYear], [LastDayofPeriod] = max(bb.[Date_Idx]) from [dbo].[DimDate] bb ) b on a.[FiscalYear] = b.[FiscalYear] and a.[FiscalMonthOfYear]= b.[FiscalMonthOfYear]
-
I have read this post 3 times now and still don't understand what a fiscal period is.
Must be too early in the morning.
And why no subqueries?
-
Lindsay,
Is dbo.DimDate a table that has a row for every date? With our 4-4-5 periods, we created a lookup table with a row for every date from 1-1-2000 thru 1-1-2025. The table had several columns such as dt(datetime), fiscalMonth_no(int), fiscalYear_no(int), and isHoliday(bool). If we need to find the last day of fiscal March 2005, we used somthing like this:
SELECT MAX(dt)
FROM dimTime
WHERE fiscalMonth_no = 2
AND fiscalYear_no = 2005
(our fiscal year starts in Feb, so March is fiscalMonth_no 2)
Hope that helps,
Nick
-
I'm still waiting for Lindsay to describe what happens during the first week of the year... for example, if it has less than 4 days, is it considered to be a part of the previous year as in ISO?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, Are you talking about the first week of the calendar year? If so, that falls into week 14 for us, and that week starts on Sunday, January 31st, and ends on Saturday January 6th. All weeks are complete in our fiscal calendar. Hence, having two different months in the same period.
Nick, yes, we have a row for every date. I love your solution, but this is actually going to be running as part of a one-time load process, so theoretically the last date for that period could be the record that is currently getting written, and not necessarily the last record in the period!
Michael, fiscal period is the fiscal month, not the calendar month. We're trying to avoid subqueries because this query is actually part of a looooong query to load data into the DimTime table. Trying to keep it simple!
Thank you all for your insight!
-
Since you didn't supply any rules for how we could determine what the Fiscal Periods are, I gave you the best I could under the circumstances.
I don't understand why it should take a long time to load a Date table. I just ran the function on the link below, and it produced a table of all dates for 5 centuries (years 2000 through 2500) in about 55 seconds.
This function produces 65 columns, so it is probably at least as complex in logic as what you are doing Granted, it would take a little longer to load into a table, and you would need additional logic for the fiscal year, but I cannot imagine that adding a lot of runtime.
Date Table Function F_TABLE_DATE
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
-
The fiscal calendar is passed in through our ERP to our 400. These fields are in the [ETL].[Transform_DimDate] table.
-
Yep... that's what I was talking about... but that's week 14? Maybe I missed it somewhere but you still haven't given us all the info we need... like when the fiscal year starts (first whole week of October?) which will definitely tell us what a fiscal month is. It'll also help us determine years when the final quarter is defined as 4-4-6 instead of just 4-4-5 (it will be the final quarter, won't it?)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Obviously what Lindsay said in the post you quoted is wrong, he probably meant Dec 31 to Jan 6 is week 14, not Jan 31 to Jan 6.
Maybe they are following a week numbering rule, like the ISO week numbers, where the nominal year start date is Oct 1 and the weeks run from Sunday through Saturday. Something like this is common in retail, where the nominal year start date is commonly Feb 1 with the periods following a 4-5-4 week sequence.
This code would return Week #14 if the year starts on Oct 1:
select WeekOfFiscalYear = (datediff(dd,'20061001','20061231')/7)+1
If it is an ISO type rule, the Fiscal year would start any where from Sep 28 through Oct 4, and the basic rule would be that the Fiscal Year starts on the first Sunday on or before October 4 of any year. That is fairly trivial to calculate; this code returns the start dates from year 2000 to 2011.
select FiscalYearStartDate = dateadd(dd,(datediff(dd,-53684,a.DATE)/7)*7,-53684) from ( select DATE = convert(datetime,'20001004') union all select DATE = convert(datetime,'20011004') union all select DATE = convert(datetime,'20021004') union all select DATE = convert(datetime,'20031004') union all select DATE = convert(datetime,'20041004') union all select DATE = convert(datetime,'20051004') union all select DATE = convert(datetime,'20061004') union all select DATE = convert(datetime,'20071004') union all select DATE = convert(datetime,'20081004') union all select DATE = convert(datetime,'20091004') union all select DATE = convert(datetime,'20101004') union all select DATE = convert(datetime,'20111004') ) a
After that, it is just a matter of calculating the offset in weeks from the FiscalYearStartDate to determine the start and end dates of the fiscal periods: Weeks 1 to 4 = 1, Weeks 5 to 8 = 2, weeks 9 to 13 = 3, ..., Week 48 and greater = 12
However, this is all speculation, since Lindsay has been unable to explain the rules. Perhaps he does not actually know the rules; in his last post he just said that they come from an "ERP".
-
Obviously...
Like you said at the end of your last post, I'm still trying to get Lindsay to correctly identify the problem
which, in most cases, will be 90% of the solution 
I do think your speculation as to what Lindsay actually means is spot on...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply