July 31, 2012 at 5:41 pm
We have situations in our application where the 1st business day of September of
specified year is the due date for a tax bill. Write a user defined function in SQL that does the
following:
a. Takes in a year value as a parameter input – (Note you can assume that only years after
the year 1980 will be passed in via this parameter)
b. Returns the first business day of September of the input year
c. Be sure to account for Labor Day which is a holiday defined as the first Monday in
September.
d. 1 Business Day is defined as any day that is not Saturday, Sunday, or a holiday.
e. Make this easy to understand such as (1) state any assumptions made about data
present in the system and (2) utilize existing built-in SQL functions. (Note if you rewrite
a function from scratch that SQL Server already provides, this question will be marked as
wrong).
July 31, 2012 at 6:03 pm
ramkumar1336 (7/31/2012)
We have situations in our application where the 1st business day of September ofspecified year is the due date for a tax bill. Write a user defined function in SQL that does the
following:
a. Takes in a year value as a parameter input – (Note you can assume that only years after
the year 1980 will be passed in via this parameter)
b. Returns the first business day of September of the input year
c. Be sure to account for Labor Day which is a holiday defined as the first Monday in
September.
d. 1 Business Day is defined as any day that is not Saturday, Sunday, or a holiday.
e. Make this easy to understand such as (1) state any assumptions made about data
present in the system and (2) utilize existing built-in SQL functions. (Note if you rewrite
a function from scratch that SQL Server already provides, this question will be marked as
wrong).
Homework or test?
(Note if you rewrite a function from scratch that SQL Server already provides, this question will be marked as
wrong).
Show us what you have tried so far and where you are having problems. We are willing to help you but we aren't going to do it for you.
July 31, 2012 at 7:13 pm
Hi,
Sorry I forgot to Paste What I worked on so far.
Well the general picture I got it with this code
DECLARE @Year VARCHAR(4)
DECLARE @FullDate DATETIME
DECLARE @ReturnDay VARCHAR(10)
SET @Year = '2012'
SET @FullDate = CAST(DATEADD(MM,8,@Year) AS DATETIME)
SET @ReturnDay = DATENAME(DW,@FullDate)
IF ( @ReturnDay = 'Monday' )
BEGIN
SET @FullDate = DATEADD(DD,1,@FullDate)
SET @ReturnDay = DATENAME(DW,@FullDate)
END
ELSE IF ( @ReturnDay = 'Saturday' )
BEGIN
SET @FullDate = DATEADD(DD,3,@FullDate)
SET @ReturnDay = DATENAME(DW,@FullDate)
END
ELSE IF ( @ReturnDay = 'Sunday' )
BEGIN
SET @FullDate = DATEADD(DD,2,@FullDate)
SET @ReturnDay = DATENAME(DW,@FullDate)
END
ELSE
BEGIN
SET @ReturnDay = DATENAME(DW,@FullDate)
END
SELECT @ReturnDay
The Only problem I am having is how to create a Function with the above code?
And Please Tell me if I did anything wrong or Is there any other easier way than this?
July 31, 2012 at 8:21 pm
Would this work?
CREATE FUNCTION FirstBusinessDay
(
@Year CHAR(4)
)
RETURNS VARCHAR(10)
AS
BEGIN
-- We will return this:
DECLARE @ReturnDay VARCHAR(10)
;
DECLARE @FullDate DATETIME = DATEADD(MM, 8, @Year)
;
SET @ReturnDay = DATENAME(DW,@FullDate)
;
DECLARE @Days VARCHAR(40) = '3Saturday 2Sunday 1Monday'
;
DECLARE @Index INT = CHARINDEX( @ReturnDay, @Days )
;
IF ( @Index > 0 )
BEGIN
DECLARE @Offset INT = CONVERT(int, SUBSTRING(@Days, @Index - 1, 1))
;
SET @FullDate = DATEADD(DD, @Offset, @FullDate)
;
SET @ReturnDay = DATENAME(DW,@FullDate)
END
RETURN @ReturnDay;
END
I tried to write a readable code, not the most concise one.
July 31, 2012 at 8:38 pm
Yes. It worked perfectly. I found the CharIndex(...) but I need some more practice to use it the way you have given. I got these questions from my friend. I am trying to learn.
July 31, 2012 at 8:59 pm
We all do.
The best of luck to you.
August 1, 2012 at 6:55 pm
Here are two more choices, one a scalar function and the second an in-line table valued function.
create function dbo.scFirstBusinessDay(
@YEAR int
)
returns datetime
as
begin
declare @FirstDay datetime;
with
quickTally(
n
) as (
select
0
union all
select
row_number() over (order by (select null))
from
(values (1),(1),(1),(1),(1),(1),(1))dt(n)
)
select
@FirstDay = min(MyDate)
from
(select
DATEADD(dd,n,DATEADD(MM,8,dateadd(yy,@Year - 1900,0)))
from
quickTally
)dt(MyDate)
where
datename(dw,MyDate) not in ('Monday','Sunday','Saturday');
return(@FirstDay);
end
go
-- or as an itvf
create function dbo.itvfFirstBusinessDay(
@YEAR int
)
returns table
as
Return(
with
quickTally(
n
) as (
select
0
union all
select
row_number() over (order by (select null))
from
(values (1),(1),(1),(1),(1),(1),(1))dt(n)
)
select
FirstDay = min(MyDate)
from
(select
DATEADD(dd,n,DATEADD(MM,8,dateadd(yy,@Year - 1900,0)))
from
quickTally
)dt(MyDate)
where
datename(dw,MyDate) not in ('Monday','Sunday','Saturday')
);
go
August 1, 2012 at 7:11 pm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply