April 14, 2010 at 9:18 am
OK, I've been looking online and reading many articles on how to exclude weekends, however, most of the articles are assuming that your start date or end date begin or end on a weekend. That won't work for me, so is there a way to exclude weekends between a start date and an end date?
if start date begins on a Monday and the end date is Thursday of the following week, I want to return 7 for the days between them and not 9 days between them which includes sat and sun.
I only want the weekdays including in my calc.
I'm using this and it works but now I need to exclude weekend days.
select datediff("d", OpenDate, ClosedDate) as DayDifference
from Sales
April 14, 2010 at 9:45 am
There are several ways to do it. One would be to join to a calendar table that would include a column where weekdays can be identified.
An example of a calendar table can be found in the Tally table article referenced in my signature.
Such a table has many more advantages: e.g. you could exclude holidays, build your own fiscal year a.s.o.
April 14, 2010 at 9:46 am
i have this saved in my snippets to get the next business day:
this assumes you have a Calendar table with some specific columns, since business days are also dependant on Holidays as well.
select
NextBusinessDay = min(a.MyDate)
from
MyDateTable a
where
a.MyDate > @MyStartDate and
a.MyDayOfWeek not in ('Saturday','Sunday') and
a.MyHolidayIndicator <> 1
Lowell
April 14, 2010 at 10:10 am
how would I do a "calendar" table?
April 14, 2010 at 10:14 am
here is a link to one that i built after a few posts on the subject here on SSC; it's got all the code to insert all the known holidays I could think of:
Lowell
April 14, 2010 at 11:20 am
ok so i now have my date table with all of the date from 1/31/2005 to 12/31/2015, so now, how can I determine if a date between 4/1/2010 and 4/15/2010 is a weekend not to count in my datediff call of my open and closed dates?
my query actually looks like this
select datediff("d", openDate, closedDate") - tbl1.daysToComplete as DayDifference
from tblSale inner join tbl1 on tblSales.task = tbl1.Task
where task like '%New%' and tblSale.qtr = 3 and tbl1.Year = 2010
so now how can I throw my date table in the mix and make sure that the days between openDate and ClosedDate are not a saturday or sunday and I would get back 9 days (days between 4/11 - 4/15)
April 14, 2010 at 11:38 am
without the two tables (tblSale and tbl1 ) and some sample data, i can only make a guess that is syntactically correct;
my assumption in my example is that the Closeddate should be 10 BUSINESS days more than the StartDate;
select
datediff("d", openDate, closedDate) - tbl1.daysToComplete as DayDifference
from tblSale
inner join tbl1 on tblSales.task = tbl1.Task
where task like '%New%'
and tblSale.qtr = 3
and tbl1.Year = 2010
and closedDate NOT in --not in, because if it IS in, it is correct?
(
select
NextBusinessDay = min(a.TheDate)
from
TallyCalendar a
where a.TheDate > tblSale.openDate + 10 --two weeks of working days?
and a.DayOfWeek not in ('Saturday','Sunday')
and a.IsWorkHoliday <> 1
)
Lowell
April 14, 2010 at 12:10 pm
SQL_NuB (4/14/2010)
how would I do a "calendar" table?
Date Table Function F_TABLE_DATE
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
Function F_TABLE_DATE is a multistatement table-valued function that returns a table containing a variety of attributes of all dates from @FIRST_DATE through @LAST_DATE.
April 15, 2010 at 12:14 am
I am writing an article on this. Hope, it will very useful to you.
drop table holiday
CREATE TABLE holiday (date DATETIME ,HolidayDetails VARCHAR(100))
INSERT INTO holiday VALUES('1/1/2010','New Year')
INSERT INTO holiday VALUES('1/15/2010','Pongal Holiday')
select * from holiday
DROP TABLE SAMPLE_TABLE
CREATE TABLE SAMPLE_TABLE (ID INT,date DATETIME ,date1 DATETIME,[NAME] VARCHAR(100))
INSERT INTO SAMPLE_TABLE VALUES(1,'12/30/2009','11/30/2010','VENKAT')
INSERT INTO SAMPLE_TABLE VALUES(2,'1/10/2010','1/20/2010','SUBA')
INSERT INTO SAMPLE_TABLE VALUES(3,'1/19/2009','1/20/2010', 'KRISHIV')
INSERT INTO SAMPLE_TABLE VALUES(4,'1/25/2009', '1/20/2010', 'ARUN')
SELECT * FROM SAMPLE_TABLE
SELECT ID,datediff(day,date,date1) AS DATE_DIFF, NAME FROM SAMPLE_TABLE
DECLARE @ID INT, @DATE DATETIME,@DATE1 DATETIME,@NAME VARCHAR(100)
DECLARE @CNT INT
CREATE TABLE #TEMP (ID INT,DATE_DIFF INT,NAME VARCHAR(100))
DECLARE CUR CURSOR FOR
SELECT ID,DATE,DATE1,[NAME] FROM SAMPLE_TABLE
OPEN CUR
FETCH NEXT FROM CUR
INTO @ID, @DATE, @DATE1, @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @CNT=COUNT(1) FROM holiday WHERE DATE >=@DATE AND DATE <= @DATE1
INSERT INTO #TEMP
SELECT ID,DATEDIFF(DAY,@DATE,@DATE1)-@CNT,@NAME FROM SAMPLE_TABLE WHERE ID=@ID
FETCH NEXT FROM CUR
INTO @ID, @DATE, @DATE1, @NAME
END
SELECT * FROM #TEMP
DROP TABLE #TEMP
CLOSE CUR
DEALLOCATE CUR
Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:
http://venkattechnicalblog.blogspot.com/
April 15, 2010 at 6:00 am
select
datediff("d", openDate, closedDate) - tbl1.daysToComplete as DayDifference
from tblSale
inner join tbl1 on tblSales.task = tbl1.Task
where task like '%New%'
and tblSale.qtr = 3
and tbl1.Year = 2010
and closedDate NOT in --not in, because if it IS in, it is correct?
(
select
NextBusinessDay = min(a.TheDate)
from
TallyCalendar a
where a.TheDate > tblSale.openDate + 10 --two weeks of working days?
and a.DayOfWeek not in ('Saturday','Sunday')
and a.IsWorkHoliday <> 1
)
[/code]
I'm looking on the query you provide and I ran it, however, It looks like that its looking to see if the opendate is not a saturday or sunday. (I may be wrong on that assumption, correct me of I'm wrong)
however, I need to see if the days between openDate and ClosedDate are Saturday or Sunday and if so don't count them.
the data entry method does not allow an open date or close date be a weekend so that portion is completed, I just need to see if there are any weekends between the openDate and closedDate and not count those if they do exist.
April 15, 2010 at 6:26 am
I'm a little weak on your requirement, so help me clarify it:
you have an openDate and a closedDate.
I thought the closedDate should be x business Days greater than the openDate; am i wrong? by selecting business days, you auto-skip weekends and holidays.
i was assuming you have existing data that might be incorrect; my SQL(i think) will find things that do NOT match my assumed "next business day" of +10 biz days; swapping the not in with in would show you which data matches that assumption, vs which data does not match.
so for example, if i have 04/01/2010 as my openDate, ten biz days(14 days) more than that would be 04/16/2010, because there are two weekends between them the 03-sat/04-sun and 10-sat/11-sun
select NextBusinessDay = min(a.TheDate)
from TallyCalendar a
where a.TheDate > convert(datetime,'04/01/2010') + 14
and a.DayOfWeek not in ('Saturday','Sunday')
and a.IsWorkHoliday <> 1
--results:
2010-04-16 00:00:00.000
note i didn't use any calculation to look for those weekends between two dates...i simply used a start date to calculate what would be a "closedDate", based on a beginning openDate
SQL_NuB (4/15/2010)
if there is more than 6 days between the openDate and closedDate , of course there is at least part of a weekend in between them...so you need to define the min number of biz days between them, and not whether weekends exist or not.
Lowell
April 15, 2010 at 6:39 am
I got it working, instead of where you had the 10, I put that as my column that has the defined number of days to complete and it worked fine.
thanks for your help on this, it was greatly appreciated.
April 18, 2010 at 7:53 pm
This will return the number of days between two dates excluding weekends (i.e. Sat and Sun)
It doesn't take account of holidays / other non-working days though:
-- get number of days between two dates excluding weekends (Sat, Sun)
DECLARE @openDate DATETIME
DECLARE @closedDate DATETIME
SET @openDate = '17 apr 2010' -- this is a Saturday
SET @closedDate = '30 apr 2010';
WITH dateCTE AS
(
SELECT @openDate AS theDate
UNION ALL
SELECT theDate + 1
FROM dateCTE
WHERE theDate + 1 < @closedDate
)
SELECT COUNT(theDate) AS no_of_working_days
FROM dateCTE
WHERE DATEPART(weekday,theDate) NOT IN (1,7) -- omit: 1=Sun, 7=Sat
also... depending on whether 'between' includes the starting date or not you may need to add or subtract 1 like this:
WHERE theDate + 1 < (@closedDate -1)
hope its useful 🙂
April 18, 2010 at 9:57 pm
SQL_NuB (4/14/2010)
OK, I've been looking online and reading many articles on how to exclude weekends, however, most of the articles are assuming that your start date or end date begin or end on a weekend. That won't work for me, so is there a way to exclude weekends between a start date and an end date?if start date begins on a Monday and the end date is Thursday of the following week, I want to return 7 for the days between them and not 9 days between them which includes sat and sun.
I only want the weekdays including in my calc.
I'm using this and it works but now I need to exclude weekend days.
select datediff("d", OpenDate, ClosedDate) as DayDifference
from Sales
If you don't have holidays to worry about and don't really want to take the time to use a Calendar table, then see the following article for a relatively simple formula to take care of things...
http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/
Of course, if the language on your server isn't "English", you may have to change the day of the week. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 19, 2010 at 11:36 am
that is setup for the start date or end date begin on a weekend, I need to find out if the day between my two dates are a saturday or sunday and not count them
so if my openDate = 4/19/2010 and my closedDate = 4/27/10, I want to see 5 business days, the 19th isn't counted, saturday and sunday aren't counted and the 27th isn't counted, so I want to see 5 business days for those two dates, My open and close date will never be a weekend due to the frontend app won't allow it, however, a weekend can be in between the open and closed dates
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply