Strange Month number value in table of Dates

Question

Strange Month number value in table of Dates

edwardwill

SSCertifiable

Points: 5851
More actions
November 28, 2017 at 5:01 am

#341034

I've been tasked with writing a script to populate a Dates table. This contains a row for every date in the year, along with derived or implied data such as MonthNumber, MonthName, IsWorkingDay, WeekNumber etc.
All this is fine apart from one column which is called MonthNumberV2. The person who has previously seeded this table has left the company and there is no documentation that I can find.
The MonthNumberV2 more or less follows the MonthNumber except that it rolls over onto the next value according to an algorithm that I cannot figure out.
The table below shows the LAST date for each of the MonthNumberV2 values for 2016 and 2017 (I used a LAG operator in a CTE to surface this and so the value for MonthNumberV2 is missing, but unsurprisingly it's 31 December in both cases). So, for example, the first day of MonthNumberV2 = 2 would be 7 February 2016.
Can anyone enlighten me what might be the algorithm for calculating the correct date to roll over? It almost looks like the first Sunday WHERE the day of the month is <= 8 ... except for June 2017, where the roll-over date is Monday 5 June.
As this data was inserted using a Seed Script from the Web solution, it's possible that this is a hand-coding error, but someone on a different team thought it might be something to do with ISO dates.
Any help most gratefully received.
Edward

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

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

Mischa E.J. Hoogendoorn Mr or Mrs. 500 Points: 558 More actions · Answer 1

I am not sure that I understand the question, I see wrong MonthNumberV2 values for the column.
If you use this simple update statement, it should be fixed:

update tablename
set MonthNumberV2 = month([date])

I hope that this will solve your problem.
Best regards,
Mischa

edwardwill SSCertifiable Points: 5851 More actions · Answer 2

Thanks but that doesn't solve the problem. The table already has a MonthNumber column which corresponds exactly with the value of the month's date (e.g. January = 1, February = 2). But there is a business need for this second MonthNumberV2 column which does NOT change on the Month boundary.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 3

It mostly looks like it's carrying the previous month number through the first saturday of every month except you have one day in there 2017-06-04 that's a sunday... ask the LOB what the logic for that field is supposed to be.

edwardwill SSCertifiable Points: 5851 More actions · Answer 4

ZZartin - Tuesday, November 28, 2017 8:45 AM
It mostly looks like it's carrying the previous month number through the first saturday of every month except you have one day in there 2017-06-04 that's a sunday... ask the LOB what the logic for that field is supposed to be.

Sadly the authority for this is currently unavailable. I was hoping that someone somewhere might recognise the pattern ... though you have identified a certain commonality which I had also seen, and I think it's entirely possible that the 2017-06-04 value is a mistake.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 5

You can try something like this,

1.Set MonthNumberV2 = month([date])
2. Set MonthNumberV2 = month(dateadd(month, -1, [date])) WHERE [date] < DATEADD(day,
IIF(DATEPART(weekday, DATEADD(month, DATEDIFF(month, 0, [date]), 0)) = 1, 0 , 8 - DATEPART(weekday, DATEADD(month, DATEDIFF(month, 0, [date]), 0)))
,DATEADD(month, DATEDIFF(month, 0, [date]), 0))

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Would these formulas give the correct results?
DECLARE @test-2 TABLE ( [Date] DATE, [Weeknumber] AS DATEPART(wk, DATEADD(wk, -1, [Date])), [DayName] AS DATENAME( DW, [Date]), [MonthNumber] AS MONTH( DATEADD( DD, -DAY([Date]),[Date])) )

INSERT INTO @test-2 VALUES('20160206'), ('20160305'), ('20160402'), ('20160507'), ('20160604')

SELECT * FROM @test-2

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

edwardwill SSCertifiable Points: 5851 More actions · Answer 7

I went with this (the Stored Procedure is a WHILE loop that iterates over either 365 or 366 days. @DateToInsert is the current date being processed; the other variables are self-explanatory) :

IF ((DATENAME(DAY, @DateToInsert) BETWEEN 1 AND 7) AND (@Dayname = 'Sunday') AND (@MonthNumber > 1)) BEGIN SET @MonthNumberV2 = @MonthNumberV2 + 1; END

richardm-1037631 SSC-Addicted Points: 481 More actions · Answer 8

It looks to me like this is a fiscal date based on the week. You have a repeating pattern of 5-4-4 in the week numbers; except in June 2017 which is probably an error. Talk with your accounting department and see if they utilize a fiscal year based on weeks like this.

edwardwill SSCertifiable Points: 5851 More actions · Answer 9

richardm-1037631 - Thursday, November 30, 2017 7:41 AM
It looks to me like this is a fiscal date based on the week. You have a repeating pattern of 5-4-4 in the week numbers; except in June 2017 which is probably an error. Talk with your accounting department and see if they utilize a fiscal year based on weeks like this.

The trouble is that it's not my company, it's a client who is a Swedish multi-national and the person who has the information is out of the office at present. Anyway, I've gone with my solution and they're just going to have to suck it up.

Thanks everyone.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 10

edwardwill - Thursday, November 30, 2017 7:47 AM
richardm-1037631 - Thursday, November 30, 2017 7:41 AM
It looks to me like this is a fiscal date based on the week. You have a repeating pattern of 5-4-4 in the week numbers; except in June 2017 which is probably an error. Talk with your accounting department and see if they utilize a fiscal year based on weeks like this.
The trouble is that it's not my company, it's a client who is a Swedish multi-national and the person who has the information is out of the office at present. Anyway, I've gone with my solution and they're just going to have to suck it up.
Thanks everyone.

Can't that solution potentially end up setting monthV2 = 13?