Hi guys, got a conundrum here that i'm pretty sure is possilbe but can't get my head around.
I have a table that contains 5 fields - engineername, startdate, starttime, enddate, endtime (varchar, datetime, datetime, datetime, datetime) - see "capture.jpg" attachment.
i want to be able to produce a list of results that shows the total number of days for each person in a particular month, and for it not to include weekend days. the startdate and enddate may be in different months, so it has to take that into account and not include those days - see "capture2.jpg" attachment.
is this possible? basically i'll be passing 2 variables in to dictate which month is to checked @month and @year - example being month = 9 and year = 2019.
October 6, 2019 at 9:12 pm
Something like this? (Use a Calendar table).
use tempdb;
GO
-- count the dates between two days excluding weekends
CREATE TABLE #Calendar (TheDate DATE PRIMARY KEY CLUSTERED);
GO
DECLARE @TheDate DATE = '01-Jan-2019'
WHILE @TheDate < '01-Feb-2019'
BEGIN
INSERT INTO #Calendar(TheDate) VALUES (@TheDate);
SET @TheDate = DATEADD(day,1,@TheDate);
END
CREATE TABLE #Events (EventID INT IDENTITY
, PersonID INT NOT NULL
, StartDate DATE NOT NULL
, EndDate DATE NOT NULL);
GO
INSERT INTO #Events (PersonID, StartDate, EndDate) VALUES (100,'01-01-2019','01-09-2019'),(100,'01-12-2019','01-15-2019');
SELECT PersonID
, StartDate
, EndDate
, COUNT(c.TheDate) AS DayCount
FROM #Events e INNER JOIN #Calendar c ON (c.TheDate>=e.StartDate AND c.TheDate<=e.EndDate)
WHERE DATEPART(WEEKDAY, c.TheDate) BETWEEN 2 AND 6
GROUP BY PersonID, StartDate, EndDate;
October 6, 2019 at 11:44 pm
This is an application for Jonathan's daterange function. Here is his article:
https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function
Something like this:
declare
@input_yearint=2019,
@input_monthint=9;
declare
@test_monthdate=datefromparts(@input_year,@input_month,1);
select
count(*) days_in_month_excluding_wkends
from
dbo.daterange(@test_month,eomonth(@test_month),'dd',1)
where
datepart(dw, [value]) not in(1,7);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 7, 2019 at 2:30 am
this question was asked several times, if you have other holidays besides weekends(new year's day, christmas, labor day, etc), a calendar table is probably the best approach. You can create such a table for 100 years very easily. I keep such a table in utility database from 2000 - 2050, enough for another 30 years.
October 7, 2019 at 10:08 am
the only days i would be exluding are weekend days (so no other holidays to be excluded).
so would a calendar still be the most appropriate way to do this?
I currently don't have calendar table at all, but i guess creating one would be quick and handy to have anyway?
I've created one anyway now (100 years from the start of this year).
October 8, 2019 at 12:48 pm
This is an application for Jonathan's daterange function. Here is his article:
https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function
Something like this:
declare
@input_yearint=2019,
@input_monthint=9;
declare
@test_monthdate=datefromparts(@input_year,@input_month,1);
select
count(*) days_in_month_excluding_wkends
from
dbo.daterange(@test_month,eomonth(@test_month),'dd',1)
where
datepart(dw, [value]) not in(1,7);
not SQL 2000 compatible unfortunately.
October 8, 2019 at 2:01 pm
Yea yea, I know or I noticed after I posted that. Instead of eomonth() you could use:
dateadd(day,-1,dateadd(month,1,@test_month))
Then you could use a tally table and dateadd function. Or just create a calendar table like the others have posted.
Seriously tho, Sql 2000 is obsolete.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 8, 2019 at 2:04 pm
yeah i know it's obsolete, but it's what i have to work with.
a legacy application uses it and there's no requirement to upgrade. the work involved would greatly outweigh the benefits.
but yeah i've created a calendar table (as noted above).
however, not sure how to use that in conjuction with what i want to achieve.
October 8, 2019 at 2:28 pm
It would be similar to what was posted except using your calendar table instead of the daterange function. Eomonth can't be used either because it's Sql 2016+.
Something like this:
declare
@input_yearint=2019,
@input_monthint=9;
declare
@test_monthdate=datefromparts(@input_year,@input_month,1);
select
count(*) days_in_month_excluding_wkends
from
YourCalendarTable
where
TheDate between @test_month and dateadd(day,-1,dateadd(month,1,@test_month))
and datepart(dw, TheDate) not in(1,7);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 8, 2019 at 2:45 pm
the only days i would be exluding are weekend days (so no other holidays to be excluded).
so would a calendar still be the most appropriate way to do this?
I currently don't have calendar table at all, but i guess creating one would be quick and handy to have anyway?
I've created one anyway now (100 years from the start of this year).
Considering your use case, I would add a calculated field to indicate whether the date is a weekend. Then use that in your WHERE clause, or else use a SUM instead of a COUNT
ALTER TABLE YOUR_TABLE_NAME
ADD isWeekend AS CAST(CASE WHEN DATEDIFF(dd, 0, CalendarDate) %7 >= 5 THEN 1 ELSE 0 END AS int) PERSISTED
October 8, 2019 at 3:27 pm
A calendar table is definitely the better option.
But, just for fun, I have come up with 2 options that *SHOULD* work with SQL2000. Unfortunately I do not have SQL2000 to test on.
Sample Data
IF OBJECT_ID(N'tempdb..#diary', N'U') IS NOT NULL
BEGIN
DROP TABLE #diary
END
CREATE TABLE #diary (
diary_id int NOT NULL
, diary_engineer_id char(7) NOT NULL
, diary_start_date datetime NOT NULL
, diary_end_date datetime NOT NULL
)
INSERT INTO #diary ( diary_id, diary_engineer_id, diary_start_date, diary_end_date )
VALUES ( 37, 'PERSON1', '2019-08-25', '2019-09-02' )
, ( 32, 'PERSON1', '2019-09-04', '2019-09-05' )
, ( 34, 'PERSON1', '2019-09-10', '2019-09-10' )
, ( 35, 'PERSON1', '2019-09-16', '2019-09-20' )
, ( 36, 'PERSON1', '2019-09-27', '2019-09-30' )
, ( 39, 'PERSON2', '2019-09-11', '2019-09-17' )
, ( 38, 'PERSON3', '2019-09-25', '2019-10-03' )
, ( 40, 'PERSON3', '2019-09-05', '2019-09-12' )
, ( 41, 'PERSON4', '2019-08-30', '2019-09-02' )
, ( 42, 'PERSON4', '2019-09-14', '2019-09-14' )
Option 1 (COUNT)
DECLARE @year int = 2019;
DECLARE @month tinyint = 09;
SELECT d.diary_engineer_id, WorkingDays = COUNT(*)
FROM #diary AS d
INNER JOIN (
SELECT CalDates.CalendarDate, isWeekday = CAST(CASE WHEN DATEDIFF(dd, 0, CalDates.CalendarDate) %7 < 5 THEN 1 ELSE 0 END AS int)
FROM (
SELECT CalendarDate = DATEADD(dd, CalDays.MonthDay -1, DATEADD(mm, @month -1, DATEADD(yy, @year-1900, 0)))
FROM (
SELECT 1 AS MonthDay UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL
SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL
SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31
) AS CalDays
) AS CalDates
WHERE CalDates.CalendarDate < DATEADD(mm, @month, DATEADD(yy, @year-1900, 0))
) AS c
ON c.CalendarDate >= d.diary_start_date
AND c.CalendarDate <= d.diary_end_date
WHERE c.isWeekday = 1
GROUP BY d.diary_engineer_id
ORDER BY d.diary_engineer_id;
Option 2 (SUM)
DECLARE @year int = 2019;
DECLARE @month tinyint = 09;
SELECT d.diary_engineer_id, WorkingDays = SUM(c.isWeekday)
FROM #diary AS d
INNER JOIN (
SELECT CalDates.CalendarDate, isWeekday = CAST(CASE WHEN DATEDIFF(dd, 0, CalDates.CalendarDate) %7 < 5 THEN 1 ELSE 0 END AS int)
FROM (
SELECT CalendarDate = DATEADD(dd, CalDays.MonthDay -1, DATEADD(mm, @month -1, DATEADD(yy, @year-1900, 0)))
FROM (
SELECT 1 AS MonthDay UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL
SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL
SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31
) AS CalDays
) AS CalDates
WHERE CalDates.CalendarDate < DATEADD(mm, @month, DATEADD(yy, @year-1900, 0))
) AS c
ON c.CalendarDate >= d.diary_start_date
AND c.CalendarDate <= d.diary_end_date
GROUP BY d.diary_engineer_id
ORDER BY d.diary_engineer_id;
October 9, 2019 at 1:43 am
Had to take my non-calendar table solution down (previously posted in this spot). It had an error when more than 1 month was spanned.
--Jeff Moden
Change is inevitable... Change for the better is not.
a working solution has been found thanks to @aaron-n-cutshall
has been tested and works.
declare @month int;
declare @year int;
select @month = 9, @year = 2019;
select d.Diary_Engineer_ID, sum(1-c.isWeekend) as TotalWeekDays
from Diary d
inner join Calendar c
on c.CalendarDate between convert(datetime, cast(@year as varchar) + '-' + cast(@month as varchar) + '-01', 101)
and convert(datetime, cast(case when @month = 12 then @year+1 else @year end as varchar) + '-' + cast(case when @month = 12 then 1 else @month+1 end as varchar) + '-01', 101) - 1
and c.CalendarDate between d.Diary_Start_Date and d.Diary_End_Date
group by d.Diary_Engineer_ID;
February 20, 2020 at 3:43 pm
Hey there... so further on down the road... I was wondering how the Calendar table was defined in this answer. Any help would be much appreciated. This thread explains the interest:
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply