Monthly Archives: March 2015

SQL Server PIVOT tutorial

Pivot can be a little tricky to use. I will explain with the help of example

Here is the output

Now lets pivot this table

Here is the output

Lets understand how the PIVOT Query works

In the first part you select a query that you want to pivot. In this example, my query is

so this is the data that pivot would be applied on. If you omit some fields in select query, pivot data will change accordingly.

In the next part, you use an aggregate function, typically SUM() or COUNT(). In my example it is

It is pretty clear from the expression itself. The first select query will be pivoted on “score in each subject”. The values in IN part is actually the column data that appears in the subject column. The data is moved into header fields. In this case we have select only Biology, Chemistry, Physica and Math. If there were more subjects in the Subject column, they will not be shown in the PIVOT table, because we are not displaying them.

Dynamic PIVOT Query

There is a way to select the subject values dynamically. The trick is to read the data first and store those values in a string and write a query and plug that string in.

Note that I am using distinct when pulling subject names otherwise, the same subject will be pulled twice and will result in error.

Redirect in PHP not working

is generally enough to redirect your page but most of the time it does not work because it requires that no data should be sent to the client before the header, or rather header should be the first thing sent to the client. To fix that problem using PHP Buffering

Note that in some case even this might not work. The reason is likely something was already sent to the client before the header. Typical example would be an add-in in the browser which injects some javascript into the page. If that is the case, redirect will not occur. In that you give the user an option to manually redirect, like this

To better trouble shoot this problem, see page contents and you will like see something that has been spitted out. In my case it was

The problem was only in Safari probably because of extension. In Firefox the redirect was working fine.

Looping through gridview in C# always return empty

I had a gridview which was bounded to a datasource (datatable in my case) in code behind in C#. Code was something like this

This was always returning empty (”) no matter which column index I was pointing to. I tried different variations like

but it failed too. After pondering on it for quite a while and still not resolved, I decided to loop through the data source itself, in my case a datatable. That actually worked! That code looks like this

I am still not sure why it will not read the values from GridView in a loop but at least there is work around.

print 4 charts one page excel

This is really simple.

  1. Create four charts as you would normally do
  2. Create a new sheet
  3. Copy and past the 4 charts to the new page.
  4. Place them (and resize them) so that they appear one page. Do print preview and adjust accordingly. Go to margin and change to 0″ top and bottom to give more area.
  5. click anywhere outside the cart and click print.

Generally it is good to place 3 charts in one page one below the other. Squeezing 4 charts on one page would make them a bit too small.

IMAP vs POP3

There are two main protocol for accessing your emails via client. IMAP and POP3. The following are the main differences

IMAP POP3
IMAP coordinates with the server POP3 does not coordinates with the server
Email synched via IMAP are shown read if they are read on the server Email obtained through POP3 are marked as unread even though they are read on the server
Email sent via IMAP is stored on the server Email sent via POP3 is NOT stored on the server
You can access all your sent email from Web client You can only see those message through web client that you sent through web client

In my needs, I want to be able to access all my emails both on my local machine as well as from web on the go so I definitely prefer IMAP + the extra advantage that my message are automatically marked read once I read it on the sever.

Those people who don’t want to leave a copy on the server for security reasons, POP3 is a good option. For them someone has to get hold of their PC to check their emails. This is usually not required unless you are working in an organization or law firm where someone can be made accountable based on their emails. Generally IMAP is the way to go for work environment as snyc all you data on the server. No worries about not finding your email.

So for me IMAP is the way to go!

windows cannot print due to a problem with the current printer setup

This is a work around for the above problem, your printer cannot print because of margin set in the printer setting. A quick work around is as follows:

1. Print to PDF Printer (If you don’t have one, install one. A good one is here Foxit PDF
2. Print directly from the PDF Version.

You might be able to save the document as PDF and then print, which might work as well.

This is just a tip that worked for me :)

Getting RAW Data in LIMTON TIS Time Management System

I had to pull raw data of an employee who hare marked his/her attendance more than 2 times (probably thinking to trick the system or otherwise), the regular table HRTAttendance show only In and Out time. In case of multiple entries, this tables does not record all entries.

All entries are record in HRTMachineData. The columns in this table show only employee number, CDate, CTime and couple of other fields. Every entry made by the system is record here, even if multiple entries are made. It also show weather weather is PunchIn our PunchOUt (Status field is used for that).

Get No of Sundays in a month SQL Server

The following stored procedure returns number of sundays in given month. It takes date as parameter. Month and year is extract from the date.

Test Run

return
4

SQL Condition in WHERE Clause

How would you set a condition in where clause in SQL Server? For example you have a parameter @ProjectID. If @ProjectID is 1, then select all employee with EmployeeNo 1000. If @ProjectID=2 then select all employee with employeeNo betweeen 1000 and 2000. SQL Case Statement might not work here so you will have to implment it otherwise. Try this query

Declare @ProjectID int
set @ProjectID=1

select ...
WHERE((@ProjectID=1 and EmployeeNo < 1000) OR (@ProjectID=2 and EmployeeNo >= 1000) OR (@ProjectID=0))

Hope it helps.