September 20, 2016 at 3:10 pm
Patrick, if you think this is about competition, then you totally misunderstand me. The only thing I'm concerned about here is teaching newcomers to SQL good habits and good techniques.
However, I've been told more than once that my written language seems a lot harsher than when I'm just talking to people. Perhaps in this instance, I read something in the tone of your posts which wasn't intended. If so, I humbly apologize.
I sincerely hope you have a good one too.
Best regards
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 21, 2016 at 7:11 am
I've had chance to look over what I did before and it's more a case of over-complicated in places rather than shocking. It only actually looks a mess, rather than actually is a mess. The 'function' itself is pretty nifty, I'd just used it in places where a simple CASE statement would do. I can't take any credit for this, ChrisM@Work provided me with it when I was looking in to the start and end of British Summer Time. It's very useful for finding days such as the last Monday in May to establish May Bank holiday. The example is still set for the last Sunday in March which is when BST starts. I've broken it down to show what each bit does because it doesn't immediately jump out.
To use it for the date you're looking for you'll need to tweak the date values and number of days to add as necessary.
DECLARE
@date DATETIME = GETDATE()
SELECT
DATEADD(day,DATEDIFF(day,'19000107',DATEADD(month,DATEDIFF(MONTH,0,(CAST(YEAR(@date) AS VARCHAR(4))+'0301')),30))/7*7,'19000107')
DECLARE @F1 VARCHAR(8) = CAST(YEAR(@date) AS VARCHAR(4))+'0301' ---Finds 1st of March for given year
DECLARE @F2 INT = DATEDIFF(MONTH,0,@f1)---Finds number of whole months between Jan 1900 and @F1
DECLARE @F3 DATETIME = DATEADD(month,@F2,30) -- Adds @f2 to 31st Jan 1900 (30) to give end of March in given year
DECLARE @F4 INT = DATEDIFF(day,'19000107',@f3) --Finds the number of days between 7th Jan 1900 (first Sunday in Jan 1900) and @F3
DECLARE @F5 DATETIME = DATEADD(day,@F4/7*7,'19000107') -- Adds number of whole weeks worth of days (@F4/7*7) to 7th Jan 1900 to give last Sunday in March of given year
SELECT
@F1,@F2,@F3,@F4,@F5
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
September 27, 2016 at 4:17 pm
I appreciate the update on the Holiday table. We never needed a separate table - just stored a flag in the Calendar table to indicate whether the day was a Holiday or not. I can see the value for some businesses and could definitely see it as a reference if you wanted to look up what particular holiday(s) fell on a certain day. I don't know that I'd use it as a normal course of business, though. The flag in the Calendar table would usually be enough to handle most business calculations. It's only if we had some sort of report that explained why people weren't working or perhaps why some people weren't working on a given day - to explain numbers being different - that I'd consult a Holiday table with details.
September 27, 2016 at 4:23 pm
To be clear, part of the purpose of the Holiday table is to serve as a join when creating the calendar table. In the previous calendar table article, a bit flag was used simply to indicate "holiday". What the holiday table did was allow the determination of holidays years in advance so that the calendar table could easily be built out that far.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 5, 2017 at 7:58 am
I've hit similar issues with holidays rolling and so far up to 7 different types
I also came across an issue with datefirst and should really be noted. Besides the fact it is assumed (default Sunday is 7) it's a good idea to verify in case someone did decide to change it and in my case, the system I'm checking against decided Sunday was 1 as well
/*Business day from data retrieved is Sunday = 1 and Saturday = 7*/
1 + ((6 + DATEPART(dw, @dt) + @@DATEFIRST) % 7)
December 27, 2018 at 5:07 am
2 important points.
The holiday table in the article misses a crucial column:
RuleID, or PatternID, or SetID, or TypeID, or whatever name you use to define different patterns of holidays.
Even in the same country different customers may observe different sets of holidays.
Even if all your customers are in the same state of the USA, they still may observe different holidays.
Some dairy shops may be well open on Christmas Day, but have their doors shut on Ramadan.
MacDonald’s is open 24 hours on every public holiday (where I am) but works for 8hours only on Christmas Day. It’s also a holiday pattern, which is specific for that particular organisation.
So, you must have different patterns, and establish a relationship between customers and patterns during Customer Setup.
And, from that, you can clearly see that the appfoach suggested by GPO, is quite weak and inappropriate.
Never use it
_____________
Code for TallyGenerator
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply