November 10, 2010 at 2:25 am
I have a calendar table like this:
SmallDateDayNameDayDescription
04/11/2010ThursdayWorking Day
05/11/2010FridayWorking Day
06/11/2010SaturdayWeekend
07/11/2010SundayWeekend
08/11/2010MondayWorking Day
09/11/2010TuesdayWorking Day
I would like to use maybe the DateAdd function to calculate 3 working days after a particular date.
Eg 04/11/2010 should return 09/11/2010. ie. it should not include "Weekend" in the calculation.
Any advice on this would be very welcome.
Thankyou.
November 10, 2010 at 3:43 am
Please paste some DDL code and sample data.
In theory, I would have the working day column as a bit data type. if 0 non working day, if 1 working day.
on your select query retreive the row_Number() of the date where your working day column is set to 1.
return your row_number for the currentdate + 3.
November 10, 2010 at 6:55 am
Hi, Thankyou for your reply.
I have changed the calendar table so that it now has a BIT field for IsWorkingDay.
I dont really have any code that would make sense to show you (other than below).
When you say i should get the RowNumber, do you mean as in the RANK() function?
So far i have this:
SELECT RANK() OVER (ORDER BY [RowNumber]) as RowNum, SmallDate
from ltbl_Calendar_Working_Days
WHERE SmallDate = '2010-11-04' AND isWorkingDay = 1
order by RowNum Which from the above data extract just returns:
RowNumSmallDate
12010-11-04
I need to create a function that passes in the date (say) 2010-11-04 and then returns the date 2010-11-09.
The data below is derived from:
SELECT RANK() OVER (ORDER BY [RowNumber]) as RowNum, SmallDate
from ltbl_Calendar_Working_Days
WHERE SmallDate BEWTWEEN '2010-11-02' AND '2010-11-12' AND isWorkingDay = 1
order by RowNum
RowNumSmallDate
12010-11-02
22010-11-03
32010-11-04
42010-11-05
52010-11-08
62010-11-09
72010-11-10
82010-11-11
92010-11-12
The bit i'm struggling with is adding the 3 working days that i need
November 10, 2010 at 7:19 am
If you are concerned about optimizing query performance and/or keeping your working day queries simple, you could amend your calendar table to include an additional indexed column that increments by 1 on each working day. Something like the following might work for you:
CREATE TABLE MyCalendar (
SmallDate smalldatetime NOT NULL PRIMARY KEY,
IsWorkingDay bit NOT NULL,
WorkingDayNumber int NOT NULL
/* other columns */
)
SmallDate DayName DayDescription IsWorkingDay WorkingDayNumber
04/11/2010 Thursday Working Day 1 145
05/11/2010 Friday Working Day 1 146
06/11/2010 Saturday Weekend 0 146
07/11/2010 Sunday Weekend 0 146
08/11/2010 Monday Working Day 1 147
09/11/2010 Tuesday Working Day 1 148
The absolute value of the WorkingDayNumber column is not particularly important, only the difference between the column values for pairs of rows. With this column available, your query is now quite straightforward.
SELECT C.SmallDate
FROM MyCalendar C
JOIN MyCalendar CREF ON (C.WorkingDayNumber = CREF.WorkingDayNumber + 3)
WHERE (CREF.SmallDate = @ReferenceDate)
AND (C.IsWorkingDay = 1)
November 10, 2010 at 7:28 am
Thankyou Andrew,
that makes perfect sense...and so very simple (when you know how!)
I don't know though how to populate the new field with an incremented value.
Can you help with this or should i really be looking elsewhere?
November 10, 2010 at 7:29 am
the Code i was asking for is the create statements and insert dummy data, created below
this should resolve your problem. let me know how it goes
---Create Table
create table test( smalldate datetime, [DayName] nvarchar(50),WorkingDay bit, RowNum int)
---Insert Values
insert into test(smalldate, DayName, WorkingDay)
select '2010/11/04',' Thursday', 1
insert into test(smalldate, DayName, WorkingDay)
select '2010/11/05' ,'Friday',1
insert into test(smalldate, DayName, WorkingDay)
select '2010/11/06' ,'Saturday', 0
insert into test(smalldate, DayName, WorkingDay)
select '2010/11/07' ,'Sunday', 0
insert into test(smalldate, DayName, WorkingDay)
select '2010/11/08' ,'Monday', 1
insert into test(smalldate, DayName, WorkingDay)
select '2010/11/09', 'Tuesday', 1
--test select
select * from test
---Update with Row Number
update test
set RowNum=b.RowNum
from (SELECT RANK() OVER (ORDER BY [smalldate]) as RowNum, SmallDate
from test
WHERE WorkingDay = 1 )b
where test.smalldate=b.smalldate
update test
set RowNum=0
where RowNum is null
-- You can probably create a stored proc to accept a date on this query
declare @day datetime
set @day = '2010-11-04 00:00:00.000'
select *
from test
where rownum in (
select RowNum + 3
from test
where smalldate=@day)
November 10, 2010 at 8:00 am
ah sorry. Here's the code i used to create the initial calendar table.
Its 2 functions and then an SP. Most of the code i found on this forum and just added bits to it.
Its very useful..
Function for getting the week number:
CREATE FUNCTION fcnWeekNumber (@DATE datetime)
RETURNS int
AS
BEGIN
DECLARE @WeekNumber int
SET @WeekNumber= DATEPART(wk,@DATE)+1-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@WeekNumber=0)
SET @WeekNumber=dbo.fcnWeekNumber(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @WeekNumber=1
RETURN(@WeekNumber)
END
GO
Function for getting Easter dates:
CREATE FUNCTION fcnGetEasterdate(@year INT)
RETURNS CHAR (8)
AS
BEGIN
DECLARE @a INT,@B INT,@C INT,@D INT,@E INT,@F INT,@G INT,@H INT,@I INT,@K INT,@L INT,@M INT,@O INT,@R INT
SET @a = @YEAR%19
SET @b-2 = @YEAR / 100
SET @C = @YEAR%100
SET @D = @b-2 / 4
SET @E = @b-2%4
SET @F = (@B + 8) / 25
SET @G = (@B - @F + 1) / 3
SET @h = ( 19 * @a + @b-2 - @D - @G + 15)%30
SET @I = @C / 4
SET @L = (32 + 2 * @E + 2 * @I - @h - @k)%7
SET @m = (@A + 11 * @h + 22 * @L) / 451
SET @O = 22 + @h + @L - 7 * @m
IF @O > 31
BEGIN
SET @r = @O - 31 + 400 + @YEAR * 10000
END
ELSE
BEGIN
SET @r = @O + 300 + @YEAR * 10000
END
RETURN @r
END
GO
SP to create caledar table:
SET DATEFIRST 1
SET NOCOUNT ON
GO
CREATE TABLE tblCalendarWorkingDays
(
RowNumber int IDENTITY (1, 1),
FullDate datetime,
SmallDate DATE,
Period INTEGER,
DayDescription varchar(50) CONSTRAINT DF_tblCalendarWorkingDays_WorkDay DEFAULT 'Working Day',
IsWorkingDay BIT CONSTRAINT DF_tblCalendarWorkingDays_IsWorkingDay DEFAULT 0,
DayOfYear INTEGER,
DayNo INTEGER,
DayName varchar(20),
WeekNo INTEGER,
MonthNo INTEGER,
MonthName varchar(20),
QuarterNo INTEGER,
YearNo INTEGER
)
GO
--Populate table with required dates
DECLARE @DateFrom datetime, @DateTo datetime, @Period int
SET @DateFrom = CONVERT(datetime,'20100101') --yyyymmdd (1st Jan 2010) amend as required
SET @DateTo = CONVERT(datetime,'20201231') --yyyymmdd (31st Dec 2020) amend as required
WHILE @DateFrom <= @DateTo
BEGIN
SET @Period = CONVERT(int,LEFT(CONVERT(varchar(10),@DateFrom,112),6))
INSERT tblCalendarWorkingDays
(FullDate, SmallDate, Period, DayOfYear,DayNo,DayName,WeekNo,MonthNo,MonthName,QuarterNo,YearNo)
SELECT @DateFrom,@DateFrom, @Period,
DATENAME(DayOfYear,@DateFrom), -- DayOfYear
DATENAME(day,@DateFrom), -- DayNo
DATENAME(weekday,@DateFrom), -- DayName
dbo.fcnWeekNumber(@DateFrom), --WeekNumber
DATEPART(month,@DateFrom), -- MonthNo
DATENAME(month,@DateFrom), -- MonthName
DATENAME(Quarter,@DateFrom), -- QuarterNo
DATENAME(year,@DateFrom) -- YearNo
SET @DateFrom = DATEADD(dd,+1,@DateFrom)
END
GO
--Start of DayDescriptions UPDATE
---------------------------------------WEEKENDS--------------------------------------
UPDATE tblCalendarWorkingDays
SET DayDescription = 'Weekend'
WHERE DATEPART(dw,FullDate) IN (6,7)
GO
--------------------------------EASTER---------------------------------------------
UPDATE tblCalendarWorkingDays
SET DayDescription = 'Good Friday'
WHERE FullDate = DATEADD(dd,-2,CONVERT(datetime,dbo.fcnGetEasterdate(DATEPART(yy,FullDate))))
UPDATE tblCalendarWorkingDays
SET DayDescription = 'Easter Monday'
WHERE FullDate = DATEADD(dd,+1,CONVERT(datetime,dbo.fcnGetEasterdate(DATEPART(yy,FullDate))))
GO
--------------------------------NEW YEAR-------------------------------------------
UPDATE tblCalendarWorkingDays
SET DayDescription = 'New Year Bank Holiday'
WHERE FullDate IN (SELECT CASE WHEN DATEPART(dw,FullDate) IN (6,7) THEN
DATEADD(dd,+2,FullDate) ELSE FullDate END
FROM tblCalendarWorkingDays
WHERE DATEPART(mm,FullDate) = 1 AND DATEPART(dd,FullDate) IN (1))
GO
UPDATE tblCalendarWorkingDays
SET DayDescription = 'New Years Eve'
FROM tblCalendarWorkingDays
WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (31)
GO
UPDATE tblCalendarWorkingDays
SET DayDescription = 'New Years Day'
FROM tblCalendarWorkingDays
WHERE DATEPART(mm,FullDate) = 1 AND DATEPART(dd,FullDate) IN (1)
GO
---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------
UPDATE tblCalendarWorkingDays
SET DayDescription = 'May Bank Holiday'
WHERE FullDate IN
(SELECT MIN(FullDate) FROM tblCalendarWorkingDays
WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
OR FullDate IN
(SELECT MAX(FullDate) FROM tblCalendarWorkingDays
WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
GO
--------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------
UPDATE tblCalendarWorkingDays
SET DayDescription = 'August Bank Holiday'
WHERE FullDate IN
(SELECT MAX(FullDate) FROM tblCalendarWorkingDays
WHERE DATEPART(mm,FullDate) = 8 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
GO
--------------------XMAS(Create Holiday on next working day if on Sat/Sun)--------------------
UPDATE tblCalendarWorkingDays
SET DayDescription = 'Christmas Bank Holiday'
WHERE FullDate IN
(SELECT CASE WHEN DATEPART(dw,FullDate) IN (6,7) THEN
DATEADD(dd,+2,FullDate) ELSE FullDate END
FROM tblCalendarWorkingDays
WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (25,26))
GO
UPDATE tblCalendarWorkingDays
SET DayDescription = 'Christmas Eve'
FROM tblCalendarWorkingDays
WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (24)
GO
UPDATE tblCalendarWorkingDays
SET DayDescription = 'Christmas Day'
FROM tblCalendarWorkingDays
WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (25)
GO
UPDATE tblCalendarWorkingDays
SET DayDescription = 'Boxing Day'
FROM tblCalendarWorkingDays
WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (26)
GO
--------------------Set working day flag------------------------------
UPDATE tblCalendarWorkingDays
SET IsWorkingDay = 1
WHERE DayDescription = 'Working Day'
GO
SET NOCOUNT OFF
I'd now like to add another field which will increment the working day only value like what Andrew mentioned in his last post. I think this table will then be very handy for allsorts of date type queries
November 14, 2010 at 10:16 pm
Howdy EML,
Did the solution provide resolution?
(sound like a rapper :-))
January 12, 2011 at 3:43 am
Hi Shanu,
I'm really sorry i did not reply to your message straight away.
I did get it all working in the end. Very similar to the code above.
If you need the full set of code i will be more than happy to post if you let me know.
Thanks for all you help 🙂
January 12, 2011 at 3:49 am
EML (1/12/2011)
Hi Shanu,I'm really sorry i did not reply to your message straight away.
I did get it all working in the end. Very similar to the code above.
If you need the full set of code i will be more than happy to post if you let me know.
Thanks for all you help 🙂
Pleasure,
Probably would be a good idea to post your solution as a reference for anyone else having a similar issue.
cheers,
February 22, 2018 at 2:36 pm
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: |:-)
-- Create date: 2018-02-22
-- Last update: 2018-02-22
-- Description: Version 1, calculates date from current date
--
-- =============================================
--Example: EXEC [stored procedure] [days], [weeks], [months] --you must supply a number other than '0' in at least one field
--EXEC sp_calculate_date 1, 0, 0
--drop procedure sp_calculate_date
CREATE PROCEDURE [dbo].[sp_calculate_date]
(
@pi_days INT,
@pi_weeks INT,
@pi_months INT
)
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @pi_count INT,
@date DATE,
@new_date DATE
SET @date = CONVERT(DATE, GETDATE())
SET @pi_days = ((@pi_days) + (@pi_weeks * 7) + (DATEDIFF(DAY, GETDATE(), (DATEADD(MONTH, @pi_months, CONVERT(DATE, @date))))))
SET @pi_count = 1
WHILE (@pi_days <> 0)
BEGIN
SET @new_date = (DATEADD(DAY, @pi_count, CONVERT(DATE, @date)))
IF (DATEPART(dw, @new_date) in (1, 7))
BEGIN
SET @pi_count = @pi_count + 1
END
ELSE
BEGIN
SET @pi_count = @pi_count + 1
SET @pi_days = @pi_days - 1
END
END
SELECT @new_date
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply