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;

 

First Snorkel Trip of 2014

So good first snorkel trip of the year, went to Fish Trap and Hairy Gardens (names of spots given by POMSAC). Best part of the weekend, waking up snorkeling and then relaxing for the rest of the day. Had about 8 people on the trip. Just being able to jump off the boat and swim in any directions and discover new things is great. Need to get my free-diving time under water up, think I’ll add a new resolution.

Visibility: Not bad about 10m
Location: Port Moresby, Papua New Guinea
Spots: Hairy Gardens and Fish Trap

Connecting iPhone to Windows 8.1

So I need to get pictures from my iPhone to my Windows 8.1 OS. Tried using the Bluetooth and that was not finding my phone and I really get annoyed trying to do that integration, should be simple but is not. So hit a wall a little bit and need a solution, enter SkyDrive….

What is SkyDrive? Well it is the iCloud, Google Drive equivalent on Windows, place where you can store you documents, pictures and other info and sync between devices. But wait you have an iPhone, so how is it going to sync. This is where the app comes in, SkyDrive App for iPhone. Downloaded from the iTunes app store (Link at bottom of Page)

Using the same account I used in setting up my Windows machine (outlook.com) and added it to the login on the App, I have now created a link between my iPhone and Windows. There are a whole bunch of options that can be configured, the one I would recommend turning off if you do not have a lot of data/bandwidth like I do is the Camera Backup as this will save all your pictures you take automatically to the SkyDrive.

So moving a picture from my iPhone to Windows:

1.Download App
2.Open AppOpenApp
3.Click the 3 ooo to get optionsooAdd
4.Then select add item:AddItem
5.File will be uploaded
6.Access File from SkyDrive on your Windows OS

Simple
Think this is the easiest way as you can upload pictures any time and then access them.
Side note: Whilst creating the pictures on my phone used an App called Skitch to add the arrows, created by Evernote and is pretty cool. Link below.

Useful Links:

20140106_084608540_iOS

Creating Windows out of Apples

(This is not an instruction guide but my own experience)20140106_084608540_iOS

So I began my process of converting my humble 11 inch MacBook Air into a dual OS machine of purpose. All started with doing a bit of research, which Windows operating system to install, how to get it done and how much it would cost.

I decided on the latest Windows operating system 8.1, which can be bought online for $119 for Windows 8.1 and $199 for the Pro. This is a little excessive for just testing out a process and unsure if it will work. Thanks to CNET, I uncovered the possibility to Evaluate Windows 8.1 for 90 days, which will give me enough time to determine if I want to fork over the cash. So after a little bit of personal information I was able to download my operating system (3.8 GBs).

So now for the process, there are tons of people who have done this before me so I just followed the instructions (after a first failed attempt in which I just downloaded the Apple Software/Drivers for Windows) Back to my process and steps:

First, get a USB with a size greater than 5 Gb as it has to hold the Windows operating system and the Apple Driver information. Then using Apple Bootcamp (5.0.5033), I created the USB installation drive.(Have to create a USB drive as my Air does not have a disk drive.) Once the Bootcamp had created the USB, it did all work of partitioning the drive and restarting the machine for the installation. The installation took a bit of time but was all successful, mostly.

Second step, was only necessary because the current version of Bootcamp has a few limitations as it was designed for Windows 8 and not 8.1. This only became apparent when I was unable to connect to a WiFi network, which was caused by a lack of driver as the new Air uses 802.11 AC and this was not installed successfully in the BootCamp process. After some more research, lots of forums going in the wrong way and websites with little to no information but did provide direction to the correct answer. All I had to do was access the USB and go to the folder BootCampDriversBroadcom(64x file), double clicking and installing the driver worked successfully and the Windows 8.1 was online.

So all in all pretty easy and successfully process.

Things learnt:

  • If you want to right-click just use 2 finger click on MacBook Touchpad.
  • A lot of information is on the web, you have to go through a lot and put little pieces together to get the correct answer.

Useful Links

Please let me know if you have any questions

New Year —-> New toy —-> New possibilities

So 2014 has started, after an interesting 2013 (I’ll cover PNG and our adventure at a later time). I am looking forward to this year as I am settled in my work and routine and I am looking to carve my name into the world. So to get things rolling just acquired a mid 2013 MacBook Air. Very sweet little machine. This machine is going to be converted into a OS/Windows hybrid and will be the instrument of my future creations.
So let the fun begin!