Post reply

Date Calendar

  • January 1, 2016 at 1:22 pm

    #1849029

    Iwas Bornready (1/1/2016)

    Wow, pretty comprehensive.

    Thanks. I tried to encompass as much as I could think of.

  • April 26, 2016 at 7:12 am

    #1874585

    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.

  • November 23, 2016 at 12:51 pm

    #1914318

    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

  • November 23, 2016 at 1:01 pm

    #1914322

    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 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

    Very cool! Thanks for sharing this. I'm sure others will appreciate it and find it very useful! 🙂

  • January 15, 2018 at 4:04 pm

    #1975939

    rcerney 63319 - Wednesday, November 23, 2016 12:51 PM

    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

    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

  • January 15, 2018 at 9:34 pm

    #1975949

    shawnmandel - Monday, January 15, 2018 4:04 PM

    rcerney 63319 - Wednesday, November 23, 2016 12:51 PM

    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

    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

    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)

  • January 16, 2018 at 12:47 pm

    #1976046

    Sean Smith (SSC) - Monday, January 15, 2018 9:34 PM

    shawnmandel - Monday, January 15, 2018 4:04 PM

    rcerney 63319 - Wednesday, November 23, 2016 12:51 PM

    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

    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

    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)

    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

  • January 16, 2018 at 1:03 pm

    #1976047

    drew.allen - Tuesday, January 16, 2018 12:47 PM

    Sean Smith (SSC) - Monday, January 15, 2018 9:34 PM

    shawnmandel - Monday, January 15, 2018 4:04 PM

    rcerney 63319 - Wednesday, November 23, 2016 12:51 PM

    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

    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

    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)

    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

    Nice stuff! Thanks.

  • December 25, 2021 at 12:02 am

    #3967098

    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)

  • December 25, 2021 at 2:36 am

    #3967116

    ALTER INDEX ALL ON dbo.date_calendar REBUILD;

    😉 Happy holidays everyone! 🙂

  • December 26, 2021 at 12:56 am

    #3967220

    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)

  • December 26, 2021 at 7:52 pm

    #3967340

    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