Monthly Archives: June 2016

Drill Down SQL Query

SQL Server does not provide any drill down queries such as Category > Sub Category. There are reporting tools for it but it cannot be done in SQL Query alone without help from theird party software.

There is SQL Group By ROLLUP, CUBE and “Group Sets”. It is a way to get rid of UNIONs and give you ‘total’ figures which gives you “some” level of drilldown (rather drill up) and can be very useful when you are interested in find total numbers by other than the group clause but it does not exactly give you drill down that a reporting tool will give. It merely calculates overall sums/totals.

How to give names to group sets in SQL

You have a group set query in SQL that you are using to give sum total of certain numbers. The problem is where the row calculates the sum total, the cell values in the corresponding colums say ‘NULL’. How to change ‘NULL’ to something more meaninful, that is ‘Total’, ‘Sum’, ‘Sales in United States’, etc

It is really easy. Use ISNULL() function.

This query is standard query that will give you No of Leaves by each employee in all months. Now we are also interested in total leave in the year, for that we will use group sets

The problem here is the last column will give us the required number of leav1es in the year but it would say what it is in the EmployeeID column. In this case we know that it is total no of leaves in an year by that employee, so lets call it ‘Total’. We will modify the query as

The type of column “xxx” conflicts with the type of other columns specified in the UNPIVOT list.

When pivoting data in SQL Server, all columns must be of same time, otherwise you will get this error. You might get this error if you have imperfect case statement or one column is of integer type while the other is of type varchar.

Interesting I was getting this error even after casting my columns into varchar(10) but still the error. Turned out just casting wouldn’t fix the problem either. My problem code was

 

The above code was giving The type of column “PercentValue” conflicts with the type of other columns specified in the UNPIVOT list becaust i was unpivoting like this

 

In the above query  + ‘%’ was the one that gave me the problem. By appending the string ‘%’ with varchar(10), it  return a true varchar type but my first string was only casted value. So adding + ‘ ‘ to the first string fixed the problem, which is basically a lank string. So the fixed code looked like this

which is merely adding +’ ‘ to string value.

cannot find stored procedure SQL Server

I was creating a stored procedure on my live server and was constantly getting the error message “Cannot find stored procedure” no matter how many times i created the same stored procedure on the server. Worse if I would create the same procedure again, I would get error, the object already exists.

Found out that I was not using dbo.StoredProcedure with the stored procedure name, that is schema was missing. So remember on live server, using dbo (schema) can make a big difference esp if your site is being hosted on a shared host like mine.

Lesson learned, dbo is important with the stored procedure name!

Visual Studio 2015 stuck at Windows Update KB2999226

I had installed Visual Studio 2005 community version on a Windows laptop before and it went all good but this time when I installed it on macbook pro (Windows 7), it got stuck the Windows Update KB2999226. I waited for hours but it wont go pass it other. In a previous attempt it gave me a failed messsage on KB2999226 as well. I did some online digging and found this post which solved me problem. It basically says

  1. Download KB2999226 from here
  2. Install it manually, it might take 10 minute or so
  3. Once it is installed start your Visual Studio 2015 installation

While my installation has gone pass the above error but now I am getting stuck at hotfix for Windows (KB2664826). I hope my installation goes fine.

Reasons why the rumoured specs of macbook 2016 could be wrong

Cult of Mac has published rumored pics of macbook pro 2016 that should top view, front and side views. The rumoured specs are

  • It will have OLED touch panel for function keys
  • Will have 4 USB-C ports (no standard USB

The rumors are circling all major mac sites some even stating that OLED touch keys can be a no no for mac users. Here I will argue why these rumors could be false.