Help with date table

  • I have a date table which I am trying to get the first date of the month and the last day of the month (these dates are fiscal)

    I have the start of the week and end of the week but having trouble working out the first day of the month and last day of the month.

    To Create the table

    CREATE TABLE [dbo].[mhs_month_week](

    [startdate] [datetime] NULL,

    [enddate] [datetime] NULL,

    [year] [int] NULL,

    [month] [int] NULL,

    [week] [int] NULL,

    [financialyear] [varchar](10) NULL,

    [monthname] [varchar](20) NULL

    ) ON [PRIMARY]

    Data for the date in 2015 only.

    To Insert the data -

    INSERT INTO [dbo].[mhs_month_weekxx]

    (startdate,enddate,year,month,week,financialyear,monthname)

    SELECT 'Apr 6 2015 12:00AM','Apr 12 2015 12:00AM','2015','1','1','2015/2016','April' UNION ALL

    SELECT 'Apr 13 2015 12:00AM','Apr 19 2015 12:00AM','2015','1','2','2015/2016','April' UNION ALL

    SELECT 'Apr 20 2015 12:00AM','Apr 26 2015 12:00AM','2015','1','3','2015/2016','April' UNION ALL

    SELECT 'Apr 27 2015 12:00AM','May 3 2015 12:00AM','2015','1','4','2015/2016','April' UNION ALL

    SELECT 'May 4 2015 12:00AM','May 10 2015 12:00AM','2015','2','5','2015/2016','May' UNION ALL

    SELECT 'May 11 2015 12:00AM','May 17 2015 12:00AM','2015','2','6','2015/2016','May' UNION ALL

    SELECT 'May 18 2015 12:00AM','May 24 2015 12:00AM','2015','2','7','2015/2016','May' UNION ALL

    SELECT 'May 25 2015 12:00AM','May 31 2015 12:00AM','2015','2','8','2015/2016','May' UNION ALL

    SELECT 'Jun 1 2015 12:00AM','Jun 7 2015 12:00AM','2015','3','9','2015/2016','June' UNION ALL

    SELECT 'Jun 8 2015 12:00AM','Jun 14 2015 12:00AM','2015','3','10','2015/2016','June' UNION ALL

    SELECT 'Jun 15 2015 12:00AM','Jun 21 2015 12:00AM','2015','3','11','2015/2016','June' UNION ALL

    SELECT 'Jun 22 2015 12:00AM','Jun 28 2015 12:00AM','2015','3','12','2015/2016','June' UNION ALL

    SELECT 'Jun 29 2015 12:00AM','Jul 5 2015 12:00AM','2015','3','13','2015/2016','June' UNION ALL

    SELECT 'Jul 6 2015 12:00AM','Jul 12 2015 12:00AM','2015','4','14','2015/2016','July' UNION ALL

    SELECT 'Jul 13 2015 12:00AM','Jul 19 2015 12:00AM','2015','4','15','2015/2016','July' UNION ALL

    SELECT 'Jul 20 2015 12:00AM','Jul 26 2015 12:00AM','2015','4','16','2015/2016','July' UNION ALL

    SELECT 'Jul 27 2015 12:00AM','Aug 2 2015 12:00AM','2015','4','17','2015/2016','July' UNION ALL

    SELECT 'Aug 3 2015 12:00AM','Aug 9 2015 12:00AM','2015','5','18','2015/2016','August' UNION ALL

    SELECT 'Aug 10 2015 12:00AM','Aug 16 2015 12:00AM','2015','5','19','2015/2016','August' UNION ALL

    SELECT 'Aug 17 2015 12:00AM','Aug 23 2015 12:00AM','2015','5','20','2015/2016','August' UNION ALL

    SELECT 'Aug 24 2015 12:00AM','Aug 30 2015 12:00AM','2015','5','21','2015/2016','August' UNION ALL

    SELECT 'Aug 31 2015 12:00AM','Sep 6 2015 12:00AM','2015','5','22','2015/2016','August' UNION ALL

    SELECT 'Sep 7 2015 12:00AM','Sep 13 2015 12:00AM','2015','6','23','2015/2016','September' UNION ALL

    SELECT 'Sep 14 2015 12:00AM','Sep 20 2015 12:00AM','2015','6','24','2015/2016','September' UNION ALL

    SELECT 'Sep 21 2015 12:00AM','Sep 27 2015 12:00AM','2015','6','25','2015/2016','September' UNION ALL

    SELECT 'Sep 28 2015 12:00AM','Oct 4 2015 12:00AM','2015','6','26','2015/2016','September' UNION ALL

    SELECT 'Oct 5 2015 12:00AM','Oct 11 2015 12:00AM','2015','7','27','2015/2016','October' UNION ALL

    SELECT 'Oct 12 2015 12:00AM','Oct 18 2015 12:00AM','2015','7','28','2015/2016','October' UNION ALL

    SELECT 'Oct 19 2015 12:00AM','Oct 25 2015 12:00AM','2015','7','29','2015/2016','October' UNION ALL

    SELECT 'Oct 26 2015 12:00AM','Nov 1 2015 12:00AM','2015','7','30','2015/2016','October' UNION ALL

    SELECT 'Nov 2 2015 12:00AM','Nov 8 2015 12:00AM','2015','8','31','2015/2016','November' UNION ALL

    SELECT 'Nov 9 2015 12:00AM','Nov 15 2015 12:00AM','2015','8','32','2015/2016','November' UNION ALL

    SELECT 'Nov 16 2015 12:00AM','Nov 22 2015 12:00AM','2015','8','33','2015/2016','November' UNION ALL

    SELECT 'Nov 23 2015 12:00AM','Nov 29 2015 12:00AM','2015','8','34','2015/2016','November' UNION ALL

    SELECT 'Nov 30 2015 12:00AM','Dec 6 2015 12:00AM','2015','8','35','2015/2016','November' UNION ALL

    SELECT 'Dec 7 2015 12:00AM','Dec 13 2015 12:00AM','2015','9','36','2015/2016','December' UNION ALL

    SELECT 'Dec 14 2015 12:00AM','Dec 20 2015 12:00AM','2015','9','37','2015/2016','December' UNION ALL

    SELECT 'Dec 21 2015 12:00AM','Dec 27 2015 12:00AM','2015','9','38','2015/2016','December' UNION ALL

    SELECT 'Dec 28 2015 12:00AM','Jan 3 2016 12:00AM','2015','9','39','2015/2016','December' UNION ALL

    SELECT 'Jan 4 2016 12:00AM','Jan 10 2016 12:00AM','2015','10','40','2015/2016','January' UNION ALL

    SELECT 'Jan 11 2016 12:00AM','Jan 17 2016 12:00AM','2015','10','41','2015/2016','January' UNION ALL

    SELECT 'Jan 18 2016 12:00AM','Jan 24 2016 12:00AM','2015','10','42','2015/2016','January' UNION ALL

    SELECT 'Jan 25 2016 12:00AM','Jan 31 2016 12:00AM','2015','10','43','2015/2016','January' UNION ALL

    SELECT 'Feb 1 2016 12:00AM','Feb 7 2016 12:00AM','2015','11','44','2015/2016','Febuary' UNION ALL

    SELECT 'Feb 8 2016 12:00AM','Feb 14 2016 12:00AM','2015','11','45','2015/2016','Febuary' UNION ALL

    SELECT 'Feb 15 2016 12:00AM','Feb 21 2016 12:00AM','2015','11','46','2015/2016','Febuary' UNION ALL

    SELECT 'Feb 22 2016 12:00AM','Feb 28 2016 12:00AM','2015','11','47','2015/2016','Febuary' UNION ALL

    SELECT 'Feb 29 2016 12:00AM','Mar 6 2016 12:00AM','2015','11','48','2015/2016','Febuary' UNION ALL

    SELECT 'Mar 7 2016 12:00AM','Mar 13 2016 12:00AM','2015','12','49','2015/2016','March' UNION ALL

    SELECT 'Mar 14 2016 12:00AM','Mar 20 2016 12:00AM','2015','12','50','2015/2016','March' UNION ALL

    SELECT 'Mar 21 2016 12:00AM','Mar 27 2016 12:00AM','2015','12','51','2015/2016','March' UNION ALL

    SELECT 'Mar 28 2016 12:00AM','Apr 3 2016 12:00AM','2015','12','52','2015/2016','March'

    So for April of 2015 I would expect 6/4/15 to be the first day and 03/05/15 to be the end date.

    Thanks

  • Does this do what you want?

    SELECT year, monthname, MIN(startdate), MAX(enddate)

    FROM [dbo].[mhs_month_week]

    GROUP BY year, monthname

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Legend.

    Thanks. Does what I need it to do!!!

  • Shifting gears a bit... constantly recalculating that which will not change seems very expensive to me (in this case). Why not make another smaller table with the start and end dates for each month? That would require just one row per month.

    --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 Moden (6/4/2015)


    ... constantly recalculating that which will not change seems very expensive...

    That's going into my table of Jeff Moden quotes.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (6/4/2015)


    Jeff Moden (6/4/2015)


    ... constantly recalculating that which will not change seems very expensive...

    That's going into my table of Jeff Moden quotes.

    Oh, be careful. I truly meant "in this case" (and edited my original response so no one makes a similar conclusion). There are places where doing the recalculation IS much less expensive than looking it up especially when it comes to memory I/O (logical reads). Itzik's cascading CTE Tally is pretty much proof of that. Yep... it's a tiny bit slower than looking it up in a Tally table in some cases. It's also a whole lot less expensive for logical reads because it doesn't produce any. "It Depends".

    "In this case" means that there would be one lookup instead of two and no calculations to it very well might be worth it. It's at least worth the test.

    --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 Moden (6/4/2015)


    dwain.c (6/4/2015)


    Jeff Moden (6/4/2015)


    ... constantly recalculating that which will not change seems very expensive...

    That's going into my table of Jeff Moden quotes.

    Oh, be careful. I truly meant "in this case" (and edited my original response so no one makes a similar conclusion). There are places where doing the recalculation IS much less expensive than looking it up especially when it comes to memory I/O (logical reads). Itzik's cascading CTE Tally is pretty much proof of that. Yep... it's a tiny bit slower than looking it up in a Tally table in some cases. It's also a whole lot less expensive for logical reads because it doesn't produce any. "It Depends".

    "In this case" means that there would be one lookup instead of two and no calculations to it very well might be worth it. It's at least worth the test.

    Rats! And I was just going to Tweet this new Modenism too!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • If they're always Mondays, why waste resources with table I/O at all? Very simple date arithmetic can tell you what the starting and ending dates are.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ScottPletcher (6/5/2015)


    If they're always Mondays, why waste resources with table I/O at all? Very simple date arithmetic can tell you what the starting and ending dates are.

    You mean like this?

    WITH cteStart AS (

    SELECT CONVERT(datetime, '04/06/2015') first_date

    )

    SELECT DATEADD(week, t.N - 1, s.first_date) week_start, DATEADD(day, (t.n * 7) - 1, s.first_date) week_end

    FROM cteStart s

    CROSS APPLY dbo.Tally1K t

    WHERE t.N < 53;

    You need a tally table, but that's about it. You could also use this approach to form the base of your insert statement to populate the table instead of all that typing.

  • Ed Wagner (6/5/2015)


    ScottPletcher (6/5/2015)


    If they're always Mondays, why waste resources with table I/O at all? Very simple date arithmetic can tell you what the starting and ending dates are.

    You mean like this?

    WITH cteStart AS (

    SELECT CONVERT(datetime, '04/06/2015') first_date

    )

    SELECT DATEADD(week, t.N - 1, s.first_date) week_start, DATEADD(day, (t.n * 7) - 1, s.first_date) week_end

    FROM cteStart s

    CROSS APPLY dbo.Tally1K t

    WHERE t.N < 53;

    You need a tally table, but that's about it. You could also use this approach to form the base of your insert statement to populate the table instead of all that typing.

    Yep, along those exact lines (except with an inline tally to avoid table I/O, since that was the prime intent 🙂 ), and using a generic year:

    WITH firstMondayInApril AS

    (

    SELECT DATEADD(DAY, - DATEDIFF(DAY, 0, CAST(CAST(YEAR(GETDATE()) AS char(4)) + '0407' AS date)) % 7,

    CAST(CAST(YEAR(GETDATE()) AS char(4)) + '0407' AS date)) AS start_date

    )

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply