Web design - Professional portfolio updates

The weekend did not rhyme with good rest and leisure...
I have added 2 more web projects to my professional portfolio.

Family Lawyers website:
(Thanks to Paul for the help)




Arthur Chen Photography website:




For more info, you can click on my professional portfolio link.

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

Question 3....

i did some browsing on the net for how to do this k so im not that good... but i do understand it now....this is a way of finding the last day of a month by:

1 - taking the date input "(@pInputDate DATETIME)"
2 - declare a date variable which you will use to output "SET @vOutputDate DATETIME"
3 - cast the year and month of the inputdate into varchar and then + /01, then assign it as the new @vOutputDate....(in other words if ur input was 23/09/2009, it will now be 01/09/2009 which is the FIRST day of the month)
4 - now add 1 month then minus 1 day (this will make it LAST day of the month)
SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))
5 - now return the output
6 - now thank louie for pasting the whole thing so u can paste it aswell LOL


ALTER FUNCTION dbo.fnGetLastDayOfMonth (@pInputDate DATETIME)
RETURNS DATETIME
BEGIN

DECLARE @vOutputDate DATETIME

SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01'
SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))

RETURN @vOutputDate

END


now this is the query for 2nd part of question 3....cant b bothered explaining it ...u figure it out!

SELECT FirstName + LastName AS Expr1, dbo.fnGetLastDayOfMonth(GETDATE()) AS thismonthpayday, dbo.fnGetLastDayOfMonth(DATEADD(mm, - 1, GETDATE()))
AS lastmonthpayday, dbo.fnGetLastDayOfMonth(DATEADD(mm, 1, GETDATE())) AS nextmonthpayday
FROM Employees

...oh yeh and the query part

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

Question 2

ALTER FUNCTION dbo.fnContactInfo()

RETURNS @contactinfo TABLE
(fullname nvarchar(30),contact nvarchar(20))
AS
BEGIN
INSERT INTO @contactinfo
SELECT firstname + ' ' + Lastname, homephone FROM dbo.Employees
RETURN
END

3rd part of question 1 (i think its right lol)

ALTER PROCEDURE dbo.OrderDateTimeSpecific

AS
select
dbo.fngetdateonly(orderdate) as orderdate,
dbo.fngettimeonly(orderdate) as ordertime
from
orders

RETURN

WHILE LOOP Stored Procedure - Class example

Add 5 new customers and date

ALTER PROCEDURE spAddCustomerIDtoOrdersFiveTimes
    @CustID NCHAR(5)
AS
    DECLARE @counter INT
    SET @counter = 0
    
    WHILE @counter < 5
    BEGIN
        INSERT INTO Orders (CustomerID, OrderDate)
        VALUES (@CustID, DATEADD(day,@counter,GETDATE()))
        
        SET @counter = @counter +1
    END
    
    RETURN

Calculate to new salary average

ALTER PROCEDURE spIncreaseSalUntilTargetReached
    @target money
AS
    DECLARE @avg MONEY
    DECLARE @count INT
    
    SET @count = 0
    SELECT @avg = AVG(SAL) FROM EMP
    
    WHILE @avg < @target
    BEGIN
        UPDATE EMP
            SET SAL = SAL + 50
        
        SELECT @avg = AVG(SAL) FROM EMP
        SET @count = @count + 1
    END
    
    PRINT 'No of records updated: ' + CAST(@count AS VARCHAR(20))
    PRINT 'Calculated AVG: ' + CAST(@avg AS VARCHAR(20))
        
    RETURN