All posts by Vaughan Mason

New Website Creation Stage 1

My new website design process has begun. Trying to reorganize my internet presence seems to be a very time consuming exercise. So at the beginning of the year I wanted to have a website and a blog up and running. This has taken a bit more time and a little bit of delay, mainly due to my changing of jobs and the lack of time I had with handing over the one and getting all the paperwork completed for the other. But that is all behind me now.

So bought my URL (my third actual) a few years ago now, www.nosam.co.za, this was going to be the start of my personalized online adventure. This was also a way for me to develop my HTML skills and design ability, as working with data mainly I tended to forget about how things look and just focus on the numbers. So the website remained a little dormant for a while, just had a cover page which I whipped up in an afternoon. Then at the beginning of this year, 2014, I decided a blog would be a great idea as well. After a lot of research settled on WordPress, vaughanmason.wordpress.com. This was not suiting my ultimate design as it was disconnected from my actual domain.

So back to the virtual drawing board.

Then a little bit more research, I decided to adjust my Afrihost account from a windows based hosting environment to a Linux based one. This resulted in wordpress being able to be installed onto the website and run from my domain. That was very easy and shocked me with the simplest of the whole exercise. The next part was moving all the content from WordPress to my own domain. Luckily this was easy as well as there is an export button on the wordpress admin site.

So very painless.

Now to begin the re-branding of my actual website. I choose to scrap the entire content of the previous website as it was all over the place and I lacked the enthusiasm to go through and figure out what I did previous. New plan put a website launch date on a development page so that I get the kick up the ass I need and launch a page. This has been done and come out pretty well.

Continuing with my goal of building an HTML5 page, I got my folder structure working first. Creating the folders for the CSS, images and JavaScript (might need a bit of code later). Then I proceeded to design a logo, which symbolize my development in Africa (which has a nice ring website is developed by me who was developed in Africa). Used a bit of CSS to get the layout working correctly and then a bit of JavaScript to calculate the date.

All and all pretty satisfied with the result.
Now just to complete the actual website.

This is my 1467 Friday!

Friday Update, look back at the week!

We will get back to the 1467 Friday later as it relates to SQL code that I was having fun with one day but let’s get back to this week. 2nd week of work of the year started off with everyone as enthusiastic as people starting there resolution for the year which means it will only go downhill from here (just kidding a few will survive). I am enjoying my job at the moment, there is a sense of freedom working alone, majority of my day is stuck in my head and I know that I am more productive for those hours. Working at home is all about routine, which I have sorted now. Trying very hard with my new year’s resolution of being happy which relates to not getting angry with people that are 8 hours behind me (time difference), I just got to understand that not being face to face with people you miss things. It is all about asking questions and getting clarity, which are lessons I am going to take back with me as we are always misunderstanding people.

So after that little brain excursion, let get back to the week.

We got our house, finally after over a year of fighting with lawyers, solving name problems and getting booted off the backs system twice, we have a house. We are the proud home owners who have now get a very big negative next to there name in terms of bank accounts but I am so stoked. Feels like I am growing up, fitting as I am 1467 Friday’s old.
Work was good, built a SSIS process to move data from one database to another on the same server (need to get it working dynamically across servers), creating SQL statements, checking if tables and databases exist. The fun stuff.
Got closure on other projects, which is nice especially if the have been a side project for almost a year because I have been juggling other things.
Studying is going well, have one more SQL exam 70-462 Administrating SQL Server 2012. I have written this twice and both times I got exactly the same mark and was short of 700 mark by a fraction. So the thought of failure is with me but I am just carrying on and hoping the questions come my way.

So enough about that…time to get to the SQL…
It is pretty easy to work out how many days you have been alive, in excel just put in your birth date and drag the column down until you get to today and then add up the columns. But this is were SQL has the edge, if you know what you are doing you can calculate it all on the fly.
This is what I did, created a temp table (could have created a normal table but this way I can change the format quite easy as I work.)

CREATE TABLE #DateInsert(
[Date] [datetime] NULL,
[WEEK] AS (datename(week,[Date])),
[WEEKDAY] AS (datename(weekday,[Date])),
[WEEKDAY_VALUE] AS (datepart(weekday,[Date])),
[DAY] AS DAY([Date]),
[MONTH] AS MONTH([Date]),
[YEAR] AS YEAR([Date])
);
As you can see it has calculated columns which I learnt in my other SQL exam (70-461 Querying SQL), they are awesome because the data is calculated as it is inserted into the table and it means that you don’t have to calculate it when you are querying the table.

Now to populate the table, First I setup a few varibles:

declare @startDate datetime
declare @endDate datetime
declare @date datetime
set @startdate = ’1985-12-09′
set @endDate = CONVERT(nvarchar(11),GETDATE())
set @date =@startDate
Just setting up my birthday and today’s date.

Then it is a simple while statement to insert the records, adding a day onto the @date variable after it has been inserted until we get all the records up to today, which is 10267 days if you were wondering as I was.

while (SELECT ISNULL(MAX(DATE),@date) FROM #DateInsert)<@endDate
BEGIN
INSERT INTO #DateInsert
Values(@date);
set @date=@date+1
END;

Then because I have used the calculated columns, the select statement is simplified:

SELECT weekday, count(*) NumberOfDays
FROM #DateInsert
GROUP BY weekday,weekday_value
ORDER BY WEEKDAY_VALUE
;

And the results are:

weekday NumberOfDays
Sunday 1466
Monday 1467
Tuesday 1467
Wednesday 1467
Thursday 1467
Friday 1467
Saturday 1466

(Yes I did use html to create the nice looking table).

So that is it, I am 1467 Friday’s old today.

(The script is also usefully if you need to create a table with date information.)
–Author: Vaughan Mason
declare @startDate datetime
declare @endDate datetime
declare @date datetime
set @startdate = ’1985-12-09′
set @endDate = CONVERT(nvarchar(11),GETDATE())
set @date =@startDate
if exists(select * from tempdb.sys.objects where name like ‘#DateInsert%’)
drop table #DateInsert;
CREATE TABLE #DateInsert(
[Date] [datetime] NULL,
[WEEK] AS (datename(week,[Date])),
[WEEKDAY] AS (datename(weekday,[Date])),
[WEEKDAY_VALUE] AS (datepart(weekday,[Date])),
[DAY] AS DAY([Date]),
[MONTH] AS MONTH([Date]),
[YEAR] AS YEAR([Date])
);

while (SELECT ISNULL(MAX(DATE),@date) FROM #DateInsert)<@endDate
BEGIN
INSERT INTO #DateInsert
Values(@date);
set @date=@date+1
END;
/****** Script for SelectTopNRows command from SSMS ******/
SELECT weekday, count(*) NumberOfDays
FROM #DateInsert
GROUP BY weekday,weekday_value
ORDER BY WEEKDAY_VALUE
;
SELECT * FROM #DateInsert;