PART 1 & 2 - SQL User Defined Functions - Homework solutions

Question 1
Create a function called fnGetTimeOnly to return the time part (hh:mm) of a DateTime value

ALTER FUNCTION dbo.fnGetTimeOnly(@datefield DATETIME)
RETURNS varchar(5)
AS
    BEGIN
        RETURN CONVERT(VARCHAR(2),
        DATEPART(hh,@datefield)) + ':' + CONVERT(VARCHAR(2),DATEPART(mi,@datefield))
    END

----------------------------------------------------

SELECT     dbo.fnGetTimeOnly(GETDATE()) AS Expr1

Create a function called fnGetDateOnly to return the date part (dd/mm/yyyy) of a DateTime value

ALTER FUNCTION dbo.fnGetDateOnly
    (@datefield DATETIME)
RETURNS VARCHAR(10)
AS
    BEGIN
        RETURN CONVERT(VARCHAR(2),DATEPART(dd,@datefield))+ '/' +
        CONVERT(VARCHAR(2),DATEPART(mm,@datefield))+ '/' +
        CONVERT(VARCHAR(4),DATEPART(yyyy,@datefield))
    END

---------------------------------------------------------

SELECT     dbo.fnGetDateOnly(NOW()) AS Expr1

Create a stored procedure called OrderDateTimeSpecific to return all order dates by splitting the date part and the time part into different columns. Use the functions created above in the stored procedure.

ALTER PROCEDURE dbo.OrderDateTimeSpecific

AS
        SELECT dbo.fnGetDateOnly(OrderDate) AS OrderDate, 
        dbo.fnGetTimeOnly(OrderDate) AS OrderTime 
        FROM Orders
    RETURN

Question 2
Create a function called fnContactInfo that returns a table containing all employees full name and their contact number

ALTER FUNCTION dbo.fnContactInfo()
RETURNS @contactInfoTable TABLE (fullname varchar(30), contactnumber varchar(24))
AS
    BEGIN
        INSERT INTO @contactInfoTable
           SELECT FirstName + ' ' + LastName, HomePhone FROM Employees
    RETURN
    END

Create a query to return all values from the fnContactInfo function

SELECT     fullname, contactnumber
FROM         dbo.fnContactInfo() AS fnContactInfo_1

Question 3
Create a function called fnLastDay to get the last day in a month of a given date.

ALTER FUNCTION dbo.fnLastDay(@givendate DATETIME)
RETURNS VARCHAR(30)
AS
    BEGIN
        DECLARE @dayindex INT
        DECLARE @lastday VARCHAR(30)
        DECLARE @lastdate DATETIME
        
        SET @lastdate = DATEADD(day, - 1, DATEADD(month, DATEDIFF(month, 0, @givendate) + 1, 0))
        SET @dayindex = CONVERT(INT,DATEPART(weekday,@lastdate))
        
        SELECT @lastday =
        CASE @dayindex
            WHEN 1 
                THEN 'Family Day Sunday'
            WHEN 2 
                THEN 'Back to work Day Monday'
            WHEN 3 
                THEN 'Movie night Tuesday'
            WHEN 4
                THEN 'Borrow money day Wednesday'
            WHEN 5 
                THEN 'Shopping spree Thursday'
            WHEN 6
                THEN 'Binge drinking night Friday'
            WHEN 7
                THEN 'Activities day Saturday'
        END
        
        
        RETURN @lastday
    END

Create a query to return all employees and their pay day last month, this month, and next month. The pay day is always the last day of the month.

SELECT     LastName, FirstName, 
dbo.fnLastDay(DATEADD(month, - 1, GETDATE())) AS PreviousMonthPayDay, 
dbo.fnLastDay(GETDATE()) AS ThisMonthPayDay, 
dbo.fnLastDay(DATEADD(month, 1, GETDATE())) AS NextMonthPayDay
FROM         Employees

Question 4
Create a function called fnRoundCurrency to format the currency value into the correct monetary format. For e.g. 39.07 should be rounded to 39.10.

ALTER FUNCTION dbo.fnRoundCurrency(@currency MONEY)

RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN CAST ((ROUND((@currency * 2),1) /10) *5 AS DECIMAL(10,2))
END

Modify the stored procedure created last week and call this function to format the new price of the first ten products into the right format.

ALTER PROCEDURE dbo.spShowCorrectPrice
AS
    SELECT TOP 10 *,dbo.fnRoundCurrency(UnitPrice) as RoundedPrice FROM Products
    
    RETURN

PART II
Create a table called Books in Northwind database containing BookID, ISBN, Publisher, Author, Year, Country, Category, Description.

CREATE TABLE Books
(
BookID INT,
ISBN VARCHAR(13),
Publisher VARCHAR(30),
Author VARCHAR(30),
Year INT,
Country VARCHAR(20),
Category VARCHAR(30),
Description VARCHAR(30),
CONSTRAINT pk_BookID PRIMARY KEY (BookID)
)

Question 1: FUNCTIONS TO CHECK CONSTRAINTS IN A TABLE DEFINITION
Create a function called fnValidISBN to check the value entered into ISBN field in the Books table. The following requirements are to check for valid ISBN:
ISBN numbers can contain 10 or 13 digits number.
Assuming that the company only accepts English-language publisher books, make sure that the ISBN follows the systematic pattern.
To check if the numbers entered are valid or not, use the formula and pattern in this webpage: http://en.wikipedia.org/wiki/International_Standard_Book_Number

ALTER FUNCTION dbo.fnValidISBN 
    (@isbn VARCHAR(13))
RETURNS INT
AS
    BEGIN
        DECLARE @result INT
        DECLARE @isbn_ten INT
        DECLARE @isbn_thirteen INT
        DECLARE @isbn_sum INT
        DECLARE @startpos INT
        DECLARE @isbnlen INT
        
        
        SET @isbn_ten = 9
        SET @isbn_thirteen = 12
        SET @isbn_sum = 0
        SET @startpos = 1
        
        
        SET @isbnlen =  LEN(@isbn)
        
        IF (@isbnlen = @isbn_ten) 
        BEGIN
                WHILE (@startpos<= @isbn_ten+1)
                BEGIN
                    SET @isbn_sum = @isbn_sum + (@isbn_ten * SUBSTRING(@isbn, @startpos, 1))
                    SET @startpos = @startpos + 1
                END
                
                IF (11-@isbn_sum%11) = SUBSTRING(@isbn,10,1)
                    SET @result = 0    --Success
                ELSE 
                    SET @result= 1    --Failure
        END
        
        ELSE IF (@isbnlen = @isbn_thirteen+1)
        BEGIN
                WHILE (@startpos <= @isbn_thirteen)
                BEGIN
                    IF @startpos % 2 <> 0
                        BEGIN
                            SET @isbn_sum = @isbn_sum + (1 * SUBSTRING(@isbn, @startpos, 1))
                            SET @startpos = @startpos + 1
                        END
                    ELSE
                        BEGIN
                            SET @isbn_sum = @isbn_sum + (3 * SUBSTRING(@isbn, @startpos, 1))
                            SET @startpos = @startpos + 1
                        END
                END
                
                
                IF (10 - @isbn_sum % 10) = SUBSTRING(@isbn,13,1)
                    SET @result = 0    --Success
                ELSE 
                    SET @result= 1    --Failure
        END
        
        ELSE SET @result = 1
        
        RETURN @result
    END


---------------------------------------------

SELECT     dbo.fnValidISBN(9780306406156) AS Expr1