First Monday of the Month

Question

Post reply

First Monday of the Month

June 3, 2019 at 5:32 pm

This was removed by the editor as SPAM
June 3, 2019 at 5:45 pm

This was removed by the editor as SPAM

Viewing 15 posts - 31 through 45 (of 62 total)

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

DesNorton SSC-Insane Points: 24285 More actions · Answer 1

So, although my prev code produced the correct results, it was clunky at best.

Here is a cleaned up version of the code

CREATE FUNCTION dbo.MonthStartEnd( @DateInMonth date )
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
  WITH cteBaseDates AS (
      SELECT  ThisMonthStartDate     = CAST( DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth)   , 0) AS date)
            , NextMonthStartDate     = CAST( DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth) +1, 0) AS date)
            , NextNextMonthStartDate = CAST( DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth) +2, 0) AS date)
  )
  , cteBaseValues AS (
      SELECT  ThisMonthStartDate     = d.ThisMonthStartDate
            , ThisMonthStartNum      = CAST(DATEDIFF(dd, 0, d.ThisMonthStartDate) %7 AS tinyint)
            , NextMonthStartDate     = d.NextMonthStartDate
            , NextMonthStartNum      = CAST(DATEDIFF(dd, 0, d.NextMonthStartDate) %7 AS tinyint)
            , NextNextMonthStartDate = d.NextNextMonthStartDate
            , NextNextMonthStartNum  = CAST(DATEDIFF(dd, 0, d.NextNextMonthStartDate) %7 AS tinyint)
      FROM cteBaseDates AS d
  )
  , cteMonthValues AS (
      SELECT MonthStart     = DATEADD(dd, -bv.ThisMonthStartNum, bv.ThisMonthStartDate)
           , MonthEnd       = DATEADD(dd, -bv.NextMonthStartNum -1, bv.NextMonthStartDate)
           , NextMonthStart = DATEADD(dd, -bv.NextMonthStartNum, bv.NextMonthStartDate)
           , NextMonthEnd   = DATEADD(dd, -bv.NextNextMonthStartNum -1, bv.NextNextMonthStartDate)
      FROM cteBaseValues AS bv
  )
  SELECT MonthStart  = CASE WHEN @DateInMonth > mv.MonthEnd THEN mv.NextMonthStart ELSE mv.MonthStart END 
       , MonthEnd    = CASE WHEN @DateInMonth > mv.MonthEnd THEN mv.NextMonthEnd   ELSE mv.MonthEnd   END 
  FROM cteMonthValues AS mv;
GO

How to post data/code on a forum to get the best help.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

DesNorton wrote:

So, although my prev code produced the correct results, it was clunky at best. Here is a cleaned up version of the code

CREATE FUNCTION dbo.MonthStartEnd( @DateInMonth date )
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
  WITH cteBaseDates AS (
      SELECT  ThisMonthStartDate     = CAST( DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth)   , 0) AS date)
            , NextMonthStartDate     = CAST( DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth) +1, 0) AS date)
            , NextNextMonthStartDate = CAST( DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth) +2, 0) AS date)
  )
  , cteBaseValues AS (
      SELECT  ThisMonthStartDate     = d.ThisMonthStartDate
            , ThisMonthStartNum      = CAST(DATEDIFF(dd, 0, d.ThisMonthStartDate) %7 AS tinyint)
            , NextMonthStartDate     = d.NextMonthStartDate
            , NextMonthStartNum      = CAST(DATEDIFF(dd, 0, d.NextMonthStartDate) %7 AS tinyint)
            , NextNextMonthStartDate = d.NextNextMonthStartDate
            , NextNextMonthStartNum  = CAST(DATEDIFF(dd, 0, d.NextNextMonthStartDate) %7 AS tinyint)
      FROM cteBaseDates AS d
  )
  , cteMonthValues AS (
      SELECT MonthStart     = DATEADD(dd, -bv.ThisMonthStartNum, bv.ThisMonthStartDate)
           , MonthEnd       = DATEADD(dd, -bv.NextMonthStartNum -1, bv.NextMonthStartDate)
           , NextMonthStart = DATEADD(dd, -bv.NextMonthStartNum, bv.NextMonthStartDate)
           , NextMonthEnd   = DATEADD(dd, -bv.NextNextMonthStartNum -1, bv.NextNextMonthStartDate)
      FROM cteBaseValues AS bv
  )
  SELECT MonthStart  = CASE WHEN @DateInMonth > mv.MonthEnd THEN mv.NextMonthStart ELSE mv.MonthStart END 
       , MonthEnd    = CASE WHEN @DateInMonth > mv.MonthEnd THEN mv.NextMonthEnd   ELSE mv.MonthEnd   END 
  FROM cteMonthValues AS mv;
GO

Good stuff, at least three times faster than the previous one 😉

😎

Looking at the execution plans, the changes reduced the scans from 3 to 1.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

How nice it is to see this again - a bunch of folks working the hell out of a problem to come up with their solutions, each one faster and more elegant than the last.

For way too long, I feared this would never return to ssc. It's one of many things which brought me here in the first place.

Jeff, many thanks for finding the gaping hole in my code. We have a vacancy for a tester here if you're interested 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

DesNorton SSC-Insane Points: 24285 More actions · Answer 4

ChrisM@Work wrote:

How nice it is to see this again - a bunch of folks working the hell out of a problem to come up with their solutions, each one faster and more elegant than the last. For way too long, I feared this would never return to ssc. It's one of many things which brought me here in the first place. Jeff, many thanks for finding the gaping hole in my code. We have a vacancy for a tester here if you're interested 😉

Agreed

This is how I learned. From watching the experienced guys hash it out.

How to post data/code on a forum to get the best help.

Simon Hundleby SSCarpal Tunnel Points: 4554 More actions · Answer 5

Jeff,

Thanks for this very elegant solution.

Regards,

Simon

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 6

DesNorton wrote:

ChrisM@Work wrote:
How nice it is to see this again - a bunch of folks working the hell out of a problem to come up with their solutions, each one faster and more elegant than the last. For way too long, I feared this would never return to ssc. It's one of many things which brought me here in the first place. Jeff, many thanks for finding the gaping hole in my code. We have a vacancy for a tester here if you're interested 😉
Agreed This is how I learned. From watching the experienced guys hash it out.

Ditto that. This is one of the best communities there is for T-SQL and SQL Server.

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

Simon Hundleby SSCarpal Tunnel Points: 4554 More actions · Answer 7

Thanks to everyone who helped

with this. I'm just working through them to understand how each one works.

Regards,

Simon

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 8

ChrisM@Work wrote:

How nice it is to see this again - a bunch of folks working the hell out of a problem to come up with their solutions, each one faster and more elegant than the last. For way too long, I feared this would never return to ssc. It's one of many things which brought me here in the first place. Jeff, many thanks for finding the gaping hole in my code. We have a vacancy for a tester here if you're interested 😉

To be honest, I thought you had it, Chris.

--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 SSC Guru Points: 1003894 More actions · Answer 9

Eirikur Eiriksson wrote:

Finding this a bit amusing, "The criteria is that if the 1st of the Month is a Monday then use that Date otherwise use the last Monday of the previous month" does not reflect that if the day entered is within the same week as the following start of month then it should be counted as the following month and the Monday before would be the desired date. The contradiction is apparent in the following statement: "The above example should return '2019-01-28' as it's the start of February, How ever It's returning for the start of January." 😎 This is no rocket science, get the date serial difference between the last day of the month and the input day and a simple sliding window type algorithm can decide where the day belongs, that is within next or previous month. On a side note, incomplete requirement resolution is far more complex than "simple" rocket science 😉

Although written kind of funny, especially with that thing about the first of the month, one of our jobs in real life is to figure out what the heck the person asking for help really wants. The thing that firmed it up for me was the example of the dates in 2019 provide by the OP.

The hard part for me was to make the realization that the key was to calculate the End Date first and if it was less than the given date, just add 1 day to that end date and you actually have the start date of the correct 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 SSC Guru Points: 1003894 More actions · Answer 10

Thanks for the feedback, Simon.

--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 SSC Guru Points: 1003894 More actions · Answer 11

Simon Hundleby wrote:

Thanks to everyone who helped with this. I'm just working through them to understand how each one works. Regards, Simon

Not trying to being a snot on this but most of the solutions flat out don't work.

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

Simon Hundleby SSCarpal Tunnel Points: 4554 More actions · Answer 12

Jeff,

Yes I didn't explain the requirements as well as I could have.

Yours and Des Norton's solutions both fulfilled the requirements.

Thanks again.

ScottPletcher SSC Guru Points: 100952 More actions · Answer 13

I can't see my own last post(s), so I don't know if it(they) showed up or not.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.