CAST Varchar To Integer in T-SQL

CAST(CASE WHEN ISNUMERIC(variab)=1 THEN variab ELSE 0 END AS INT)

This changes all non numeric string to 0 when deal with not so cleaned data.

Summation of Hour Numbers in T-SQL

This blog article is nothing but to record a standard script how to calculate the summation of hour numbers in report, for purpose not to think again in future.

Hour number need to be accumulated in minutes before present the final result. Else, it might be not accurate enough.

In inner query:

, SUM(DATEDIFF(MI, Departure, Arrival)) AS Mins

Note: DATEDIFF to work out every leg/trip's minutes. SUM() is to work out summation in this category togethere with GROUP BY.

In presentation outer query:

, SUM(CASE WHEN ... Mins ... END)/60 AS ...

Note: SUM() together with CASE statment is to format the presentation of the report.

In case wanting to keep decimal places, use:

, SUM(CASE WHEN ... Mins ... END)/60.0 AS ...
or
, SUM(CASE WHEN ... Mins ... END)/60.00 AS ...

For more about decimal place, please refer to http://koncordpartners.blogspot.com/2010/09/decimal-place-in-t-sql.html.

Strange Behavior Between onkeydown And onkeypress Events

A strange behavior between onkeydown and onkeypress events in JavaScript has been noticed. Here is a function to find out the keycode:

; function getCode(e)
{
; var evt = window.event ? event : e
; var code
; if(window.event) code = evt.keyCode
else code = evt.which
; return code
}

If the event is onkeypress, this function would generate ASCII key code. However, if the event is onkeydown, this function would generate JavaScript keycode, which does not discriminated the cases. Indeed, the latter generate keycode for capital case only.

PIVOT – Turning Rows into Columns

Here we exam various PIVOT functions/methods in MS Access, Oracle and SQL Server.

MS Access:

TRANSFORM does not work properly because it would generate many rows with a lot of zeros. Let us call it seggragation. However, covring it by a SUM() GROUP BY statement will do.

SELECT with IIF() does work perfect. Example: http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html

Oracle:

SELECT PIVOT does not work properly because of seggragation. However, covring it by a SUM() GROUP BY statement will do.

SELECT with CASE does work perfect. Example: http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html

OVER PARTITION BY does work fine, but not recommended because lack of flexibility. Example: http://baurdotnet.wordpress.com/2009/08/26/select/

SQL Server:

PIVOT does work fine, but strongly not recommended for two reasons: 1, Aggragate function whtin PIVOT can not be recalculated according to your will. 2, T-SQL does not allow ORDER BY working within the inner query, which does cause a lot of difficulties for layout arrangement.

A Practical Case of Transpose Query

Let us say a pension fund wants to generate a summary consists of total contributions had been made so far for each member, by their employers. The fund does not care about exact contribution made by each employers for each member. However, the fund wants to know the three employers paid the contributions most for each member. As the result, the summary table should have 5 columns: Member_Id (unique), Total_Contributions, Employer_1, Employer_2, Employer_3. If a member has less than three employers, leave it blank. Each member should have occupied one row only.

Simple SQL query of SELECT GROUP BY won't be able to accomplish this task, since it is a transpose requirement, namely, turning the row into column. However, CASE or DECODE in PL/SQL will do. The equivalent of CASE in Microsoft Office Access is IIF. Alternatively, TRANSFORM in Access and SELECT PIVOT in PL/SQL can be used. By use CASE or DECODE, it is possible to avoid the complex programming. There are five steps:

1. To get dataset first which should consist of all necessary information. Let us use Dataset_All to describe it:

SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contribution
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id

ORDER BY is important since it would make sure the ROWNUM is in align with the Member_Id.

2. Get a dataset which consists of the Member_Id and their last ROWNUM in Dataset_All. Let us name it Dataset_Uni:

SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id

Using GROUP BY to get largest ROWNUM if same Member_Id having multiple rows.

3. To flag each employer according their rank, start from 1. Let us name it Dataset_Rank:

SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id

4. Transpose process. We also use the numerical feather of Employer_Id which was generated from sequence:

SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM Dataset_Rank
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id

Put everything together:

WITH Dataset_All AS
( SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contributio
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id
)
SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM
( SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM
( SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id
) Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id
)
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id

Done.