TOP Operator using a local variable
-
Yeah Remi is right. The other one is very procedural - not very SQL friendly.
-
Gentlemen,
Thanks for the comments.
I don't think RGR'us' solution meets the needs despite being neat code, as usual.
The problem setting was:
"I need to pass a StartDate parameter and count of businessdays. I am trying to write a function to get the enddate."
The procedural code works for any start date and any number of days > 0 without needing [to populate] a calendar table.
As for performance, I tried the following:
------------------------ snip -----------------------------------------------------------------------
-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'dbo.BusDays2')
DROP FUNCTION dbo.BusDays2
GO
CREATE FUNCTION dbo.BusDays2
(@StartDate DateTime,
@BusinessDays int)
RETURNS DateTime
AS
BEGIN
Declare @EndDate DateTime
DECLARE @Table table (Date smalldatetime not null primary key clustered, BD bit not null)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-01', 1)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-02', 1)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-03', 1)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-04', 1)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-05', 0)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-06', 0)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-07', 1)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-08', 1)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-09', 1)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-10', 1)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-11', 1)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-12', 0)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-13', 0)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-14', 1)
INSERT INTO @Table (Date, BD) VALUES ('1980-01-15', 1)
select @endDate = (
Select Top 1 T1.Date AS Row from @Table T1
INNER JOIN @Table T2 ON T2.Date <=T1.Date AND T1.BD = CAST(1 AS BIT) AND T2.BD = CAST(1 AS BIT)
GROUP BY T1.Date HAVING COUNT(*) <= @BusinessDays
ORDER BY T1.Date desc
)
return @endDate
END
GO
-- =============================================
-- Example to execute function
-- =============================================
declare @cnt int
set @cnt = 0
While @cnt < 1000
Begin
print Cast(dbo.BusDays2('1980/01/01', 10) as Varchar)
set @cnt = @cnt + 1
end
GO
------------------------ snip -----------------------------------------------------------------------
and
------------------------ snip -----------------------------------------------------------------------
-- =============================================
-- Create inline function (IF)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'dbo.BusDays1')
DROP FUNCTION dbo.BusDays1
GO
CREATE FUNCTION dbo.BusDays1
(@StartDate DateTime,
@NumDays int)
RETURNS DateTime
AS
Begin
Declare @d datetime
Declare @businessDays int
Declare @cnt int
set @d = @StartDate -- start date
set @BusinessDays = @NumDays -- num days
set @cnt = 0
while @cnt < @BusinessDays
Begin
if Not DatePart(dw, @d) in (6, 7) -- careful with Set DateFirst
set @cnt = @cnt + 1
if @cnt < @BusinessDays
set @d = DateAdd(d, 1, @d)
End
return @d
End
GO
-- =============================================
-- Example to execute function
-- =============================================
declare @cnt int
set @cnt = 0
While @cnt < 1000
Begin
print Cast(dbo.BusDays1('1980/1/1', 10) as Varchar)
set @cnt = @cnt + 1
end
GO
------------------------ snip -----------------------------------------------------------------------
Results on my laptop were:
BusDays1 (Procedural) - 1000 lines Jan 14 1980 12:00AM elapsed 0:00:01 (no disk activity)
BusDays2 (SQL) - 1000 lines Jan 14 1980 12:00AM elapsed 0:00:05 (Disk activity)
Hardly time to make the coffee, in either case.
Quote: "Yeah Remi is right. The other one is very procedural - not very SQL friendly."
My point, exactly - Quote "No tables, no SQL - looks like unemployment [mine] looming large
"also no calendar table. I don't see the point of filling the database with dates (most of which are never used) if I can avoid it.
Enjoy your morning coffee
Regards
Otto
Best Regards,Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke) -
First of all you are right; My solution didn't provide exactly what was needed (actually was shipping out too much). This opens the door for a real set based solution. And this is also the last part where you are right >>
You are using my code WHILE inserting in a calendar table. That will kill the point of the query to use an existing table and save time that way. I'm sure both solution can work at somewhat the same speed but 5 to 1 (slower) is just a ridiculous number.
Second of all, the calendar table has the advantage of flagging holidays which is also a big concern when talking about business days. While I'm only assuming that this was his intention in the first place, that just makes sens to do it using a perm table with all the dates already flagged so you don't repeat those operations everytime you check for some dates.
Lastly I corrected my version of the code to meet the correct requirements and reran one test. Find the next business day 10 YEARS after the some date. Guess which one is faster by 36 to 1???
--pre clean up
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[BusDays1]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[BusDays1]
GO
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnvwHolidayList]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnvwHolidayList]
GO
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnFloatingDate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnFloatingDate]
GO
IF EXISTS (Select * from dbo.SysObjects where name = 'DemoCalendar' and XType = 'U')
DROP TABLE dbo.DemoCalendar
GO
IF EXISTS (Select * from dbo.SysObjects where name = 'Numbers' and XType = 'U')
DROP TABLE dbo.Numbers
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.fnFloatingDate(@Occur as int,@WeekDay as int,@Month as int,@Year as int)
RETURNS SmallDatetime
AS
BEGIN
DECLARE @Result as SmallDatetime
DECLARE @StartDate as SmallDatetime
DECLARE @DayOfWeek as int
SET @StartDate = DATEADD(M, @Month - 1,DATEADD(YYYY, @Year - 1900, 0))--generate the first day of the requested month and year
SET @DayOfWeek = DatePart(dw,@StartDate)
IF @DayOfWeek @weekDay
BEGIN
SET @StartDate = DateAdd(d,@weekDay - @DayOfWeek,@StartDate) -- Adjust to requested day of week
IF MONTH(@StartDate) @Month
BEGIN
SET @StartDate = DATEADD(d, 7, @StartDate)
END
END
SET @Result = DateAdd(wk,@Occur-1,@StartDate)--Adjust to the requested week of the month
RETURN @Result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[fnvwHolidayList] (@nYear as int)
RETURNS @Holidays TABLE
(
Holiday_date SmallDatetime PRIMARY KEY,
Holiday_name Varchar(32),
CCQ_Only bit
)
AS
BEGIN
-- Calculate Easter Sunday
DECLARE @g as int
DECLARE @C as int
DECLARE @h as int
DECLARE @i as int
DECLARE @j-2 as int
DECLARE @l as int
DECLARE @Month as int
DECLARE @Day as int
DECLARE @Easter as SmallDatetime
DECLARE @WorkDT as SmallDatetime
DECLARE @FeteDollard as SmallDatetime
------------------------------------------------------------------------------------------------
-- Bizarre Algorithm to determine Easter Sunday
SET @g = @nYear % 19
SET @C = @nYear / 100
SET @h = ((@c - (@c / 4) - ((8 * @C + 13) / 25) + (19 * @g) + 15) % 30)
SET @i = @h - ((@h / 28) * (1 - (@h /28) * (29 / (@h + 1)) * ((21 - @g) / 11)))
SET @j-2 = ((@nYear + (@nYear / 4) + @i + 2 - @C + (@c / 4)) % 7)
SET @l = @i - @j-2
SET @Month = 3 + ((@l + 40) / 44)
SET @Day = @l + 28 - (31 * (@Month / 4))
SET @Easter = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day As VARCHAR(2)) + '/' + CAST(@nYear AS VARCHAR(4))
-- Add Easter Sunday to holiday list, and get holidays based around Easter
INSERT INTO @Holidays (Holiday_name,Holiday_date, CCQ_Only) VALUES ('Lundi de Pâques',DateAdd(d,1,@Easter), 0)
INSERT INTO @Holidays (Holiday_name,Holiday_date, CCQ_Only) VALUES ('Vendredi Saint',DateAdd(d,-2,@Easter), 0)
-------------------------------------------------------------------------------------------------
-- Fixed date holidays are loaded next
INSERT INTO @Holidays (Holiday_name,Holiday_date, CCQ_Only) VALUES ('Nouvel An (1er)',CONVERT(SmallDateTime,'1/1/'+CAST(@nYear AS VARCHAR(4))), 0)
INSERT INTO @Holidays (Holiday_name,Holiday_date, CCQ_Only) VALUES ('Nouvel An (2)',CONVERT(SmallDateTime,'1/2/'+CAST(@nYear AS VARCHAR(4))), 0)
INSERT INTO @Holidays (Holiday_name,Holiday_date, CCQ_Only) VALUES ('Fête Nationale',CONVERT(SmallDateTime,'06/24/'+CAST(@nYear AS VARCHAR(4))), 0)
INSERT INTO @Holidays (Holiday_name,Holiday_date, CCQ_Only) VALUES ('Fête du Canada',CONVERT(SmallDateTime,'07/01/'+CAST(@nYear AS VARCHAR(4))), 0)
INSERT INTO @Holidays (Holiday_name,Holiday_date, CCQ_Only) VALUES ('Jour du Souvenir',CONVERT(SmallDateTime,'11/11/'+CAST(@nYear AS VARCHAR(4))), 1)
INSERT INTO @Holidays (Holiday_name,Holiday_date, CCQ_Only) VALUES ('Noël (25)',CONVERT(SmallDateTime,'12/25/'+CAST(@nYear AS VARCHAR(4))), 0)
INSERT INTO @Holidays (Holiday_name,Holiday_date, CCQ_Only) VALUES ('Noël (26)',CONVERT(SmallDateTime,'12/26/'+CAST(@nYear AS VARCHAR(4))), 0)
-- Holidays that fall on the same day of the week (based on the year they were officially established)
INSERT INTO @Holidays (Holiday_name,Holiday_date, CCQ_Only) VALUES ('Fête du travail',dbo.fnFloatingDate(1,1,9,@nYear), 0) -- 1er lundi de septembre
INSERT INTO @Holidays (Holiday_name,Holiday_date, CCQ_Only) VALUES ('Action de grâce',dbo.fnFloatingDate(2,1,10,@nYear), 0) -- 2èm lundi d'octobre
SET @FeteDollard = dbo.fnFloatingDate(4,1,5,@nYear)-- 4èm lundi de mai
--la fête de dollard est le premier lundi AVANT ( 25
BEGIN
SET @FeteDollard = DATEADD(d, -7, @FeteDollard)
END
INSERT INTO @Holidays (Holiday_name,Holiday_date, CCQ_Only) VALUES ('Fête de Dollard', @FeteDollard, 0)
RETURN --@Holidays
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.BusDays1
(@StartDate DateTime,
@NumDays int)
RETURNS DateTime
AS
Begin
Declare @d datetime
Declare @businessDays int
Declare @cnt int
set @d = @StartDate -- start date
set @BusinessDays = @NumDays -- num days
set @cnt = 0
while @cnt < @BusinessDays
Begin
if Not DatePart(dw, @d) in (6, 7) -- careful with Set DateFirst
set @cnt = @cnt + 1
if @cnt < @BusinessDays
set @d = DateAdd(d, 1, @d)
End
return @d
End
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1) primary key clustered, dude bit null)
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
ALTER TABLE dbo.Numbers
DROP COLUMN dude
GO
--recreate the calendar table
CREATE TABLE dbo.DemoCalendar (Date smalldatetime not null primary key clustered, BD bit not null)
GO
--fill the table with 24K Dates
INSERT INTO dbo.DemoCalendar (Date, BD)
SELECT DATEADD(D, PkNumber, '1979/12/31') AS Date, CASE WHEN DatePart(dw, DATEADD(D, PkNumber, '1979/12/31')) in (6, 7) THEN 0 ELSE 1 END AS BD FROM dbo.Numbers
UNION ALL
SELECT DATEADD(D, PkNumber, '2001/11/25') AS Date, CASE WHEN DatePart(dw, DATEADD(D, PkNumber, '2001/11/25')) in (6, 7) THEN 0 ELSE 1 END AS BD FROM dbo.Numbers
UNION ALL
SELECT DATEADD(D, PkNumber, '2023/10/21') AS Date, CASE WHEN DatePart(dw, DATEADD(D, PkNumber, '2001/11/25')) in (6, 7) THEN 0 ELSE 1 END AS BD FROM dbo.Numbers
GO
--Update all the holidays for the whole table
DECLARE @Years AS INT
SET @Years = 1980
WHILE @Years = @StartDate AND C.BD = CAST(1 AS BIT) ORDER BY C.Date
SET ROWCOUNT 0
Select @Date AS TheDay where @Count = @BusinessDays
GO
/*
this is where the speed testing can start
*/
declare @cnt int
set @cnt = 0
While @cnt < 100
Begin
print dbo.BusDays1('1980/1/1', 2480)
set @cnt = @cnt + 1
end
GO
--36 seconds
DECLARE @BusinessDays AS INT
DECLARE @Date AS DATETIME
DECLARE @StartDateAS DATETIME
DECLARE @CountAS INT
SET @BusinessDays = 2480 --10 years worth of business days
--SET @StartDate = DATEADD(D, 0, DATEDIFF(D, 0, GetDate()))
SET @StartDate = '1980/01/01'
declare @cnt int
set @cnt = 0
While @cnt = @StartDate AND C.BD = CAST(1 AS BIT) ORDER BY C.Date
SET ROWCOUNT 0
IF @Count = @BusinessDays
print @Date
set @cnt = @cnt + 1
end
--1 sec
/*
--clean up
DROP TABLE dbo.Numbers
DROP TABLE dbo.DemoCalendar
DROP FUNCTION dbo.fnvwHolidayList
DROP FUNCTION dbo.fnFloatingDate
DROP FUNCTION dbo.BusDays1
*/
-
Oh, I didn't turn up after. I got my function this way.
CREATE FUNCTION DBO.BUSINESS_DATEADD (@STARTDATE DATETIME, @DAYS BIGINT )
RETURNS DATETIME
AS
BEGIN
DECLARE @ENDDATE DATETIME
SET @ENDDATE = (SELECT DD FROM(SELECT T1.BDATE AS DD, COUNT(*) AS ROW FROM NAT_BUSINESSCALENDAR T1
INNER JOIN NAT_BUSINESSCALENDAR T2 ON T2.BDATE <=T1.BDATE AND T1.BUSINESS_DAY = 1 AND T2.BUSINESS_DAY = 1
WHERE T1.BDATE > CONVERT(VARCHAR(10),@STARTDATE,101) AND
T2.BDATE > CONVERT(VARCHAR(10),@STARTDATE,101)
GROUP BY T1.BDATE HAVING COUNT(*) <= @DAYS
)TT WHERE ROW = @DAYS)
RETURN @ENDDATE
END
-
Check my latest version, it'll perfom faster than the first one I did. Make sure understand the where clause in the final select... it's the validation part and it shouldn't be ignored
. -
Hi Remi,
is there any way that I can make this function work for negative intigers too?suppose if I say dbo.business_dateadd(getdate(), -3), then I should be able to get 09/26/05. Thanks for any help.
-
Ya, just make 2 different functions. One with the order by desc and <= @Date and the other one like I did. Cal the one depending on the number being negative. It could be done in a single statement but I would think that performance would suffer from this.
-
I am unable to use the SET ROWCOUNT with in a function Remi, getting this error.
Server: Msg 443, Level 16, State 2, Procedure BUSINESS_DATEADD1, Line 12
Invalid use of 'UNKNOWN TOKEN' within a function.
-
Forgot that one... can't use set options in functions... any way you can make this a proc?
-
I don't think that I can make it a procedure as I need to use this function to update a date field in a table. I tried to make it a procedure but it failed when I tried to execute this procedure for (getdate(), 10) .Also procedure doesn't take the column names like I can't do
select(exec proc1(datecolumn, 10) ) from tab1
-
K, go back to my original solution or repost the actual needs you have so that you can update all those rows in a single pass.
-
The nat_BusinessCalendar table looks like this as you know.
BDate BUSINESS_DAY
--------------------------------------- ------------ ----------- ---
1980-01-01 00:00:00.000 1
1980-01-02 00:00:00.000 1
1980-01-03 00:00:00.000 1
1980-01-04 00:00:00.000 1
1980-01-05 00:00:00.000 0
1980-01-06 00:00:00.000 0
1980-01-07 00:00:00.000 1
1980-01-08 00:00:00.000 1
1980-01-09 00:00:00.000 1
This is the function I created
CREATE FUNCTION DBO.BUSINESS_DATE_ADD (@STARTDATE DATETIME, @DAYS BIGINT )
RETURNS DATETIME
AS
BEGIN
DECLARE @ENDDATE DATETIME
SET @ENDDATE = (SELECT DD FROM(SELECT T1.BDATE AS DD, COUNT(*) AS ROW FROM NAT_BUSINESSCALENDAR T1
INNER JOIN NAT_BUSINESSCALENDAR T2 ON T2.BDATE <=T1.BDATE AND T1.BUSINESS_DAY = 1 AND T2.BUSINESS_DAY = 1
WHERE T1.BDATE > CONVERT(VARCHAR(10),@STARTDATE,101) AND
T2.BDATE > CONVERT(VARCHAR(10),@STARTDATE,101)
GROUP BY T1.BDATE HAVING COUNT(*) <= @DAYS
)TT WHERE ROW = @DAYS)
RETURN @ENDDATE
END
I am trying to run this query using this function.
update Tab_inventory set t_date = DBO.BUSINESS_DATE_ADD(getdate(),10),
set t_1st_letter = DBO.BUSINESS_DATE_ADD(t_1st_letter,10)
The above query returns around 3000 rows and takes almost 100 secs to update.
-
if you run it like that,you can run the query once the fetch the value into a variable, then use the variable in the update. I thaught that this number was dynamic in some way...
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply