January 10, 2012 at 2:58 am
Can anybody please explain me how this stored procedure is working?
CREATE FUNCTION dbo.Getnext1stthursday(@currentDate DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @1stThurs DATETIME;
DECLARE @daysToAdd INT = CASE
WHEN ( 5 - Datepart(weekday, Dateadd(DD, 1 - Day(@currentDate), @currentDate)) ) < 0 THEN --Thursday Passed
7 + ( 5 - Datepart(weekday, Dateadd(DD, 1 - Day(@currentDate), @currentDate)) )
ELSE ( 5 - Datepart(weekday, Dateadd(DD, 1 - Day(@currentDate), @currentDate)) )
END
SET @1stThurs = CONVERT(DATE, Dateadd(dd, @daysToAdd, Dateadd(DD, 1 - Day(@currentDate), @currentDate)));
RETURN CASE
WHEN @1stThurs < @currentDate THEN dbo.Getnext1stthursday(Dateadd(DD, 1 - Day(Dateadd(mm, 1, @currentDate)), Dateadd(mm, 1, @currentDate)))
ELSE @1stThurs
END;
END
January 10, 2012 at 4:11 am
Try an in-line function instead of that scalar one:
CREATE FUNCTION dbo.GetNextMonthFirstThursday
(
@ReferenceDate date
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
/*
Returns @ReferenceDate, if @ReferenceDate is
the first Thursday of the month.
Otherwise, returns the first Thursday of the
month following @ReferenceDate
*/
-- Table of numbers from 1 to 30:
WITH Numbers (n) AS
(
SELECT
ROW_NUMBER() OVER (
ORDER BY (SELECT 0))
FROM
(
VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
) AS V (n)
)
-- Query using table of numbers:
SELECT
CASE
-- Return the passed in date if it is the first
-- Thursday of the month
WHEN DATENAME(WEEKDAY, @ReferenceDate) = N'Thursday'
AND DAY(@ReferenceDate) <= 7 THEN @ReferenceDate
ELSE
-- Otherwise, find the first Thursday of the month
-- following @ReferenceDate
(
-- Return the first date that qualifies
SELECT TOP (1)
ca.the_date
FROM Numbers
CROSS APPLY
(
-- Add 1-30 days from the reference date
SELECT
DATEADD(DAY, Numbers.n, @ReferenceDate)
) AS ca (the_date)
WHERE
-- Must be a Thursday
DATENAME(WEEKDAY, ca.the_date) = N'Thursday'
-- First Thursday of the month is always be between the 1st and 7th
AND DAY(the_date) <= 7
-- Check potential days in numbers table order
ORDER BY
Numbers.n
)
END AS Result;
Examples:
DECLARE
@test_date date = GETDATE()
SELECT
gnmft.Result
FROM dbo.GetNextMonthFirstThursday(@test_date) AS gnmft
DECLARE @Example TABLE
(
the_date DATE
)
INSERT @Example
(the_date)
VALUES
('2012-01-01'),
('2012-01-02'),
('2012-01-03'),
('2012-01-04'),
('2012-01-05'),
('2012-01-06'),
('2012-01-07')
SELECT
e.the_date,
gnmft.Result
FROM @Example AS e
CROSS APPLY dbo.GetNextMonthFirstThursday(e.the_date) AS gnmft
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 10, 2012 at 1:18 pm
Thanks Paul
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply