March 12, 2021 at 11:17 pm
It's easy and flexible to include weekends in a Calendar table, usually with two flags one that says weekends, one that says holidays. Sometimes Christmas falls on a Saturday, but you don't want to count it twice (once as a weekend and once as a holiday.) If you need a cookbook on creating and using calendar tables, you can find some code here:
https://www.sqlservercentral.com/steps/bones-of-sql-the-calendar-table
Some people prefer not to leave workdays in the calendar, which makes the table a little smaller. Then they use the table to get the number of days to be excluded from a simple DATEDIFF().
Actually it makes the table much smaller. The only column in the table is a non-nullable date = 3 bytes. Even with the row overhead bytes, taking it to 12 bytes per row (max), you can get more than 600 days in a page, or ~5 years worth of nonwork days per page.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 13, 2021 at 1:34 am
The Dixie Flatline wrote:It's easy and flexible to include weekends in a Calendar table, usually with two flags one that says weekends, one that says holidays. Sometimes Christmas falls on a Saturday, but you don't want to count it twice (once as a weekend and once as a holiday.) If you need a cookbook on creating and using calendar tables, you can find some code here:
https://www.sqlservercentral.com/steps/bones-of-sql-the-calendar-table
Some people prefer not to leave workdays in the calendar, which makes the table a little smaller. Then they use the table to get the number of days to be excluded from a simple DATEDIFF().
Actually it makes the table much smaller. The only column in the table is a non-nullable date = 3 bytes. Even with the row overhead bytes, taking it to 12 bytes per row (max), you can get more than 600 days in a page, or ~5 years worth of nonwork days per page.
Some businesses/departments might work, say, on Saturday’s. Simple DATEDIFF won’t work for them.
Therefore I prefer to have a full set of days in the Calendar table, from 0 date to date No 2^16-1. Which ones to exclude from counting becomes then the matter of different parameters, not changed code. And I can easily exclude from counting a “maintenance day”, which they define as last Monday of each month.
The whole table won’t fit into one page, but 3 month worth of dates which we are usually querying for, will.
_____________
Code for TallyGenerator
March 13, 2021 at 4:42 pm
Thanks to everyone for their input , I'm going to go with the weekend steps first, and then circle back and revert to the above recommendations on the Public holiday setup. its just a matter of time before they ask for this, so its unavoidable. have a few irons in the fire...Just pacing myself.
thanks again !
March 14, 2021 at 2:52 pm
The linked article states that "If a date is neither a holiday, nor a weekend day, it is a workday." But that's not true. Businesses may be shut because of: a pandemic, a natural disaster, a local power failure, or any other number of reasons. At the very least, you need an "other" flag in the table.
Also, the work/no work checks are now in code all over the place. What if we decide to work on Saturdays for a given month? Say, to catch back up after a pandemic shutdown. All those places in code deciding "it's a weekend, so we don't work" are all wrong now. More likely, you'll go into the table and mark those Saturdays as "not a weekend". But of course that's technically not correct either, it's still a weekend day, all right, you're just working on it this time.
For each non-work day, I use a tinyint column that identifies the reason, such as which specific holiday it is. You don't want to clutter up the table with text descriptions like "New Year's Day", "Weekend Day", "Covid Catch-Up Days", etc..
You will likely still need a full calendar table for many determinations, but for just work/no work, such as for order fulfillment, a non-workdays table makes more sense and has less potential bugs.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 14, 2021 at 3:58 pm
Suppose there's a table of weekend workdays with "Makeup day after St. Patricks Day" in it. The weekend workday(s) could be included in the query using OR EXISTS and a correlated subquery. The holiday shipments are made up for by the additional weekend workday so the projection remains constant.
/* table of holidays */
drop table if exists #holidays;
go
create table #holidays(
holiday_dt date primary key not null,
holiday_name nvarchar(40));
insert into #holidays(holiday_dt, holiday_name) values
('2021-03-17', 'St. Patricks Day');
--select * from #holidays;
/* table of weekend_workdays */
drop table if exists #weekend_workdays;
go
create table #weekend_workdays(
wkend_dt date primary key not null,
work_reason nvarchar(40),
constraint chck_ww_wkend check (datepart(weekday, wkend_dt) in (1, 7)));
insert into #weekend_workdays(wkend_dt, work_reason) values
('2021-03-20', 'Makeup day after St. Patricks Day');
--select * from #weekend_workdays;
declare
@revMTD int = 88720,
@today date = '2021-03-11';
declare
@curr_month_day_1 date=datefromparts(year(@today), month(@today), 1),
@curr_month_last date=eomonth(@today);
select sum(mtd.day_count) mtd_day_count,
count(*) wk_day_count,
cast((@revMTD/(sum(mtd.day_count)*1.0)*count(*)) as decimal(14, 2)) as monthly_project_total
from dbo.fnTally(0, datediff(day, @curr_month_day_1, @curr_month_last)) fn
cross apply (values (dateadd(day, fn.n, @curr_month_day_1))) v(dt)
cross apply (values (case when v.dt<=@today then 1 else 0 end)) mtd(day_count)
where datepart(weekday, v.dt) between 2 and 6
and not exists(select 1
from #holidays h
where v.dt=h.holiday_dt)
or exists(select 1
from #weekend_workdays ww
where v.dt=ww.wkend_dt);
mtd_day_countwk_day_countmonthly_project_total
923226728.89
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
March 14, 2021 at 10:32 pm
The linked article states that "If a date is neither a holiday, nor a weekend day, it is a workday." But that's not true. Businesses may be shut because of: a pandemic, a natural disaster, a local power failure, or any other number of reasons. At the very least, you need an "other" flag in the table.
Also, the work/no work checks are now in code all over the place. What if we decide to work on Saturdays for a given month? Say, to catch back up after a pandemic shutdown. All those places in code deciding "it's a weekend, so we don't work" are all wrong now. More likely, you'll go into the table and mark those Saturdays as "not a weekend". But of course that's technically not correct either, it's still a weekend day, all right, you're just working on it this time.
For each non-work day, I use a tinyint column that identifies the reason, such as which specific holiday it is. You don't want to clutter up the table with text descriptions like "New Year's Day", "Weekend Day", "Covid Catch-Up Days", etc..
You will likely still need a full calendar table for many determinations, but for just work/no work, such as for order fulfillment, a non-workdays table makes more sense and has less potential bugs.
So very, very true across the board. Well put, Scott.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2021 at 11:44 pm
I feel compelled to confess that the calendar table I use has all the dates. I'm actually delighted that the "Some people prefer" invited such rebuttals. Also those are some great ideas to deal with "makeup" days, working weekends, etc. Thanks guys. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 15, 2021 at 3:05 am
Not to forget - there are situations when definitions of workdays are different within the same organisation. Health, logistics, hospitality business, retail trading, etc. - they all typically have different workday schedules for “office” and “in-field” staff.
so, the same day may be a weekend day for “category 1” employees and a workday for “category 2” ones.
_____________
Code for TallyGenerator
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply