October 23, 2020 at 4:20 pm
Jeffrey Williams wrote:I was thinking more about this - realized we can do this:
SELECT weekend_day_count=y/7-x/7+(y+1)/7-(x-1)/7
FROM (VALUES(DATEDIFF(DAY,-53690,'1899-01-01') + 1,DATEDIFF(DAY,-53690,'1900-01-14') + 1))x(x,y)
We can specify the anchor date as 1753-01-01 (-53690) and then add 1 to shift to the Sunday prior.
I've not quite figured out why but my testing shows that works for some dates but not all. Admittedly, I've not taken the time to figure out why.
That doesn't make sense...in the first case we are returning the integer difference between 2 dates, in the second we are returning the integer difference between 2 dates.
DATEDIFF(day, -1, GETDATE()) = 44126 = DATEDIFF(day, 0, GETDATE()) + 1
DATEDIFF(day, -53690, GETDATE()) + 1 = 97816 = DATEDIFF(day, '1752-12-31', GETDATE())
The key to the calculation is returning the integer value for the date based on Sunday being the start date. The key difference is that we can use the integer value for the first parameter and it will be natively converted to the corresponding datetime value.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 23, 2020 at 4:26 pm
Unfortunately, a weekend is not always a Saturday and Sunday. You can have three and four day weekends, depending on how holidays fall. I've always found it better to calculate the business days.
Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math and there are two of them (Orthodox and Catholic).
The Ordinal business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
ordinal_business_nbr INTEGER NOT NULL,
...);
INSERT INTO Calendar
VALUES ('2007-04-05', 42);
('2007-04-06', 43); -- Good Friday
('2007-04-07', 43);
('2007-04-08', 43); -- Easter Sunday
('2007-04-09', 44);
('2007-04-10', 45); -- Tuesday, back to work
To compute the business days from '2007-04-05', Thursday before Easter in 2007 to '2007-04-10', the next Tuesday:
SELECT (C2.ordinal_business_nbr - C1.ordinal_business_nbr -1) AS business_day_cnt
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = '2007-04-05'
AND C2.cal_date = '2007-04-10';
This saves having to scan all of the rows within the range to get a sum. The -1 is to avoid counting the current day as one of the business days, but you might have a different rule in your company.
I think you are conflating 'non-working days' with weekends. They are not necessarily the same - nor would they be treated the same in a business. In most businesses...a holiday can be defined as a non-working day but if you have to work that day you get holiday pay which is different from normal pay.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 23, 2020 at 5:04 pm
Jeff Moden wrote:Jeffrey Williams wrote:I was thinking more about this - realized we can do this:
SELECT weekend_day_count=y/7-x/7+(y+1)/7-(x-1)/7
FROM (VALUES(DATEDIFF(DAY,-53690,'1899-01-01') + 1,DATEDIFF(DAY,-53690,'1900-01-14') + 1))x(x,y)
We can specify the anchor date as 1753-01-01 (-53690) and then add 1 to shift to the Sunday prior.
I've not quite figured out why but my testing shows that works for some dates but not all. Admittedly, I've not taken the time to figure out why.
That doesn't make sense...in the first case we are returning the integer difference between 2 dates, in the second we are returning the integer difference between 2 dates.
DATEDIFF(day, -1, GETDATE()) = 44126 = DATEDIFF(day, 0, GETDATE()) + 1
DATEDIFF(day, -53690, GETDATE()) + 1 = 97816 = DATEDIFF(day, '1752-12-31', GETDATE())
The key to the calculation is returning the integer value for the date based on Sunday being the start date. The key difference is that we can use the integer value for the first parameter and it will be natively converted to the corresponding datetime value.
Yeah, this approach is like the approach I mentioned in my long note on ways around the negative problem:
... Finally, in what I can't decide is either the most elegant or crudest of the solutions, since only the relative positions to the anchor matter, not the absolute size of the offsets, you can just add some sufficiently large multiple of 7 to the DATEDIFF(DAY... expressions in the VALUES constructor to make sure that even if a date is 0001-01-01, the resulting offset value can't be negative.
This added the fewest characters, and generally performed better than the others (typically around ~10-15% increase in CPU compared to the original)...
Off the top of my head, I'm also rather confused why your version of this wouldn't work, unless it's been tested in a form that does allow some negative numbers (so some start dates prior to 17521231, which would also require using something other than the numeric datetime equivalents for the anchor to avoid an out-of-range error).
Cheers!
Viewing 3 posts - 61 through 62 (of 62 total)
You must be logged in to reply to this topic. Login to reply