Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dbapimi26
Starting Member
3 Posts |
 Posted - 2012-05-16 : 12:50:03
|
| Hi to all,. need help to populate fiscal calendar. attempted to use olap but my fiscal date do not match what is in the table.these are the table values.[PostDate] [datetime] NOT NULL, [FiscalYear] [int] NOT NULL, [FiscalMonth] [int] NOT NULL, [FiscalWeek] [int] NOT NULL, [FiscalDay] [int] NOT NULL, [MonthLabel] [varchar](50) NOT NULL, [DayLabel] [varchar](50) NOT NULL, [CalendarYear] [int] NOT NULL, [CalendarMonth] [int] NOT NULL, [CalendarWeek] [int] NOT NULL, [CalendarDay] [int] NOT NULLany help is appreciated |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-05-16 : 13:08:36
|
| With what part(s) do you need help? Can you post what you've tried so far? are you looking for one row per day? How many years? basically I would populate the calender values first using a numbers table/function. Then update the fiscal period values based on the start/end of your fiscal cycle. You could probably do both steps at once. Typically, you would not store a MonthLabel and DayLabel as they are easily derived from the month and day INT values.Be One with the OptimizerTG |
 |
|
|
dbapimi26
Starting Member
3 Posts |
Posted - 2012-05-16 : 14:57:51
|
| Hi TC,Thanks for the prompt response, I was hoping to populate for the next 5 years and yes you are correct I can always do the month & day labels from current values. I tried doing a dimension date table but it adds way to much values so I was hoping to find a CTE or function that would create my calendar base on october being the start of my fiscal calendar...yamil lond |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-16 : 20:52:34
|
quote:
Originally posted by dbapimi26
Hi TC,Thanks for the prompt response, I was hoping to populate for the next 5 years and yes you are correct I can always do the month & day labels from current values. I tried doing a dimension date table but it adds way to much values so I was hoping to find a CTE or function that would create my calendar base on october being the start of my fiscal calendar...yamil lond
what according to you represent a fiscal period?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
dbapimi26
Starting Member
3 Posts |
Posted - 2012-05-16 : 21:27:11
|
| Thanks khtan, since this is something that was already in place I will make do with that. visakh16, our fiscal year runs from October 1 of the prior year through September 30 of the year and since I was not familiar with the process of creating a CTE or function to populate the table and was looking for some guidance in completing.yamil lond |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-16 : 21:45:23
|
quote:
Originally posted by dbapimi26
Thanks khtan, since this is something that was already in place I will make do with that. visakh16, our fiscal year runs from October 1 of the prior year through September 30 of the year and since I was not familiar with the process of creating a CTE or function to populate the table and was looking for some guidance in completing.yamil lond
then you can use logic like belowhttp://visakhm.blogspot.com/2010/02/generating-calendar-table.htmland apply it likeDECLARE @year int,@startdate datetime,@enddate datetimeSET @year=2012SET @startdate = DATEADD(mm,-3,DATEADD(yy,@year-1900,0)),@enddate=DATEADD(yy,1,@startdate)-1SELECT [Date]FROM dbo.CalendarTable(@startdate,@enddate,0,0) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Fiscal Calendar