Date Calendar
-
Iwas Bornready (1/1/2016)
Wow, pretty comprehensive.Thanks. I tried to encompass as much as I could think of.
-
Sean Smith-776614 (1/1/2016)
Iwas Bornready (1/1/2016)
Wow, pretty comprehensive.Thanks. I tried to encompass as much as I could think of.
Nothing missing that I would need. Thanks again.
-
I know this is an old post but still such an awesome script. One additional field I added for my use is a fiscal_quarter column at the end. The year quarter is great but our fiscal year begins in October and goes through the following September. I'm sure others have added this as well when needed but in case anyone ever wants it, add a column called fiscal_quarter to the main table at the top of the script and then this to the --Table Update II section
DC.fiscal_quarter = Case When DC.calendar_month Between 1 And 9 Then CONVERT (VARCHAR (4), DC.calendar_year) + 'Q' + CONVERT (VARCHAR (1), DC.calendar_quarter + 1) Else CONVERT (VARCHAR (4), DC.calendar_year +1) + 'Q' + CONVERT (VARCHAR (1), DC.calendar_quarter -3) end
-
rcerney 63319 (11/23/2016)
I know this is an old post but still such an awesome script. One additional field I added for my use is a fiscal_quarter column at the end. The year quarter is great but our fiscal year begins in October and goes through the following September. I'm sure others have added this as well when needed but in case anyone ever wants it, add a column called fiscal_quarter to the main table at the top of the script and then this to the --Table Update II sectionDC.fiscal_quarter = Case When DC.calendar_month Between 1 And 9 Then CONVERT (VARCHAR (4), DC.calendar_year) + 'Q' + CONVERT (VARCHAR (1), DC.calendar_quarter + 1) Else CONVERT (VARCHAR (4), DC.calendar_year +1) + 'Q' + CONVERT (VARCHAR (1), DC.calendar_quarter -3) end
Very cool! Thanks for sharing this. I'm sure others will appreciate it and find it very useful! 🙂
- rcerney 63319 - Wednesday, November 23, 2016 12:51 PM
Hi,
I'm trying to also add the fiscal_quarter for End of August being Q1 and I'm getting conversion errors with your above code.
Can anyone help?
Regards,
Shawn - shawnmandel - Monday, January 15, 2018 4:04 PM
Bit of a "blunt force" approach, but are you looking for something like this?
,DC.fiscal_quarter = 'Q' + (CASE
WHEN DC.calendar_month IN (8, 9, 10) THEN '1'
WHEN DC.calendar_month IN (11, 12, 1) THEN '2'
WHEN DC.calendar_month IN (2, 3, 4) THEN '3'
WHEN DC.calendar_month IN (5, 6, 7) THEN '4'
END) - Sean Smith (SSC) - Monday, January 15, 2018 9:34 PM
I find it much simpler to add the number of months/quarters/whatever necessary to make the fiscal year line up with the calendar year. For example, the above code could be rewritten as
'Q' + CAST(DATEPART(QUARTER, DATEADD(MONTH, 5, DC.calendar_month)) AS CHAR(1))Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Tuesday, January 16, 2018 12:47 PM
Nice stuff! Thanks.
-
Here's what everyone seems to forget when downloading and using such code... 😉
--===== Check for Fragmentation on the Clustered Index
SELECT avg_fragmentation_in_percent
,avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.date_calendar'),1,1,'SAMPLED')
;Results:
--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) -
ALTER INDEX ALL ON dbo.date_calendar REBUILD;
😉 Happy holidays everyone! 🙂
- Sean Smith wrote:
ALTER INDEX ALL ON dbo.date_calendar REBUILD;
😉 Happy holidays everyone! 🙂
The other indexes weren't fragmented so no need to rebuild all the indexes. That's expensive even on such a small table.
What I'm suggesting is that you may want to modify your script to include a rebuild of the clustered index at the end of the run. You might also want to right-size the columns in the original CREATE TABLE instead of doing all the ALTERs that you do.
--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) -
Great tips, thanks! Added to the to-do list for the next revision. Enjoy the holidays!
Viewing 12 posts - 61 through 71 (of 71 total)
You must be logged in to reply to this topic. Login to reply