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.

Decimal Place in T-SQL

Sometime decimal place can drive you into mad in T-SQL because different machine can show different result.

In SELECT statement, a formula would generate a result without decimal place, because it had been automatically rounded to integer:

Value_Is_200/3 would generate 67

The easiest way to control decimal place is to place decimal point in one of the constants in your formula:

Value_Is_200/3.0 would generate 66.7
Value_Is_200/3.00 would generate 66.67

If it does not work, you can try:

ROUND(Value_Is_200/3.0, 1) would generate 66.7
ROUND(Value_Is_200/3.0, 2) would generate 66.67

It may not work sometimes, because ROUND() is not for the purpose of decimal control. If it does not work, or if you need to truncate rather than round, try:

ROUND(Value_Is_200/3.0, 2, 1) would generate 66.6
ROUND(Value_Is_200/3.0, 3, 2) would generate 66.66

Sometimes, it still does not work. Then try:

CONVERT(DECIMAL(12,1), Value_Is_200/3.0) would generate 66.7
CONVERT(DECIMAL(12,2), Value_Is_200/3.0) would generate 66.67
or
CONVERT(DECIMAL(12,1), ROUND(Value_Is_200/3.0, 2, 1)) would generate 66.6
CONVERT(DECIMAL(12,2), ROUND(Value_Is_200/3.0, 3, 2)) would generate 66.66

Date Format of Parameters for SSRS

While SQL Server does accept the date format as string, it is suggested to convert it to datetime data type. This will allow you to use a calendar control as well as avoiding any date format errors that may occur when running your query. Indeed, it does cause minor errors internally. The period, it can be:

t.add_datetime >= @StartDate AND t.add_datetime < DATEADD(D, 1, @EndDate)

The trick here is for general public both dates should be inclusive. Above code indeed includes the time from 0:00:00 of the StartDate to 23:59:59 of the EndDate.


http://www.sqlusa.com/bestpractices/datetimeconversion/

Percentage Format in T-SQL

There is no function to present the percentage format in T-SQL, something like 17.25%. There are various ways to do this. However, for reporting purpose, the easiest way might be:

CONVERT(VARCHAR, Result_From_Calc)+'%'

Following is an example how to add Total row at bottom of the selected dataset as will as add percentage column for each of rows.

The original dataset is:

SELECT Leg_Id
, Statute_Miles
FROM dw_Legs
GO

The final script is as follows:

WITH Totaling
AS
(
SELECT SUM(Statute_Miles) AS Milage
FROM dw_Legs
)
SELECT Leg
, Milage
, CONVERT(VARCHAR, Milage*100/(SELECT Milage FROM Totaling))+'%' AS Percentage
FROM
(
SELECT CONVERT(VARCHAR, Leg_Id) AS Leg
, Statute_Miles AS Milage
FROM dw_Legs
UNION
SELECT 'Total' AS Leg
, Milage
FROM Totaling
) AS Dataset
GO

Explaination:

1. Total milage will be used more than once, so it has been in put into WITH clause.
2. The reason convert Leg_Id into VARCHAR is because word ‘Total’ is a VARHCAR. Else the UNION won’t work.
3. Make sure there is only one row being selected into WITH clause. That is where people usually create a bug.

Following is the result: