Rounding Up in SQL

Working in the telecommunications industry, you run into situations not always found in the real world.  For example, when billing a call or applying taxes to an account, you always round up.  We can't charge a customer anything less than 1¢ (since they can't pay less than 1¢), so you have to round up.

SQL has a function that allows you to perform rounding on your values.  This function is called ROUND(), but only does true rounding (below 5 = round down, above 5 = round up).  So using this function, how do we always round up?

First, we have to figure out how many decimal places we will be billing our customer.  Let's say, for example, that our customer's calls are rounded to the nearest two decimals.  Once we know how many decimals we need to round, we can apply our logic.  The logic is then to add 49 one digit past the decimal place you are trying to round.  In this case, since we want to round to the nearest two decimal places, we will add $0.0049 to our value and then round.  If we wanted to go three decimal places, we would add $0.00049, $0.000049 to four decimal places, etc.

The following will round to $0.01:

select ROUND(0.013, 2)

But if we want to always round up, we need to add $0.0049:

select ROUND(0.013+0.0049, 2)

The above rounds to $0.02.

Now, you might be asking yourself: why do we add $0.0049 instead of $0.005?  Well, adding $0.005 might inadvertently makes us round higher than we want to go.  Look below for an example, which will round up to $0.03:

select ROUND(0.020+0.005, 2)

When we actually want it to stay at $0.02:

select ROUND(0.020+0.0049, 2)

I've found it is more productive to add $0.0049 to the number and then round, as opposed to trying to figure out a way for SQL to round up only those values that need it.

To read up more on rounding, go here: SQL ROUNDING

SQL SUBSTRING

There may be reasons why you may only need part of the text and not the whole thing.  For example, if you wanted to organize your customer list by first initial of their company name.  How would you do this?  SQL Server has a function called SUBSTRING().  This function allows for you to specify your starting position and the number of digits you want to take.  If you want the first digit of a customer's company name, you would write your query like this:

SELECT SUBSTRING(CompanyName, 1, 1), *
from CustomerList

You could also use it to find out the Area Code of your customers, by getting the first 3 digits of their phone number:


SELECT SUBSTRING(PhoneNumber, 1, 3), *
from CustomerList


Unlike other programming languages, SQL's SUBSTRING() function is 1-based, while other programming languages are 0-based.  This means that if you want the first digit in SQL, you use 1.  With C#, for example, if you wanted the first digit, you would use 0.

You can also run a query to see how many customers you have for each letter in the alphabet.  This could be done by combining the SUBSTRING() function with a group by, like so:


SELECT SUBSTRING(CompanyName, 1, 1), count(*)
from CustomerList
group by SUBSTRING(CompanyName, 1, 1)
order by SUBSTRING(CompanyName, 1, 1)


SQL SUBSTRING()

What data could you see using the SUBSTRING() function on?

The Magic of Triggers

I was recently asked a question at work by one of our developers.  He needed some help on updating one of his stored procedures.  He was looking to see if there was some kind of variable that he could use to pass the current table he was querying.  This made me curious as to why he would pass such a variable.  After some discussion, I realized that he was looking to create audit trail for our internal website using that stored procedure.

My suggestion is quite uncommonly known for people new to Microsoft SQL Server.  I introduced him to the idea of triggers.

SQL Triggers

Now, what are triggers and why are they so useful?  Triggers are a special kind of stored procedure that occurs (is triggered) when a certain event happens on a given table.  Different events that can activate a Trigger include:

• Insert
• Update
• Delete

So, now onto why they are useful.  Let's say, for instance, that you have a Customer table and every time a new customer is added, a contact is created in your Contact table.  Now, you could write a stored procedure that you would call once you've added the customer account.  On the other hand, why not create a trigger that would insert the new contact into the Contact table automatically, without a need for calling anything.  The trigger would look something like this:

CREATE TRIGGER tContact
ON Customer
AFTER INSERT 
AS 

INSERT INTO Contact (CustomerId, FirstName, LastName, EMail, PhoneNumber)
SELECT CustomerId, FirstName, LastName, Email, PhoneNumber

from Inserted

GO

SQL Server also creates two temporary tables that can be used during the Trigger: Deleted and Inserted.  The Deleted table contains original data from the table during Update and Delete statements.  The Inserted table contains data that is effected during Insert and Delete statements.  These tables could be used to grab data for whatever the purpose of your trigger is.

Overall, Triggers can be very useful, depending on the purpose.  Don't create too many triggers, though, as this could create too much overhead on the system.  Also, triggers are extremely hard to debug, so for debugging purposes, stored procedures might be better.  For more on the disadvantages triggers, visit the following blog:

Disadvantages of Triggers

Common Table Expressions or CTE

At work, I've created over 40 reports that run on a daily, weekly or monthly basis.  I've created these reports using a SQL Server tool called SQL Server Integration Services, or SSIS.  The one problem I've found with SSIS is that SQL won't allow the use of temporary tables (tables that are generated on the fly and are dropped as soon as your query is over).  Through my research, there is one option within SQL Server that allows for table called Common Table Expressions, or CTE.

Using Common Table Expressions

A CTE is much like a temporary table, it is generated on the fly and disappears as soon as your query is over.  Creating a CTE is pretty simple.  All you need to provide is the following:

• Expression Name (table name)
• Column name list (not required)
• CTE Query Definition (statement selecting your records)
• Your actual query on the new CTE

From there, a query can then be run off of the CTE data to summarize or join with other tables.  Here is an example:

WITH Sales_CTE (SalesPersonID, OrderID, SalesDate)

AS

(

    SELECT SalesPersonID, SalesOrderID, YEAR(SalesDate) AS SalesDate

    FROM Sales

    WHERE SalesPersonID IS NOT NULL

)

SELECT SalesPersonID, COUNT(OrderID) AS TotalSales, SalesDateFROM Sales_CTE

GROUP BY SalesDate, SalesPersonID

ORDER BY SalesPersonID, SalesDate

There are a few differences I've seen between CTE's and temporary tables that may discourage you from using them on a regular basis.

• No option to alter (add or remove columns) the CTE and it's contents
• No option to update or delete rows from the CTE

If you don't need to do either, then a CTE is your best option.  There are also many advantages to using CTE's

• Use the CTE multiple times within the same query, resulting in not having to write the same query over and over.
• Avoid making mistakes like referencing incorrect columns and/or tables.

SQL COALESCE

I know it seems to be a recurring theme with me dealing with NULLs, probably because I come up with situations dealing with NULLs all the time at work.  You can't use equality symbols on NULLs, so if you have NULL, you need to figure out how to deal with them.

Here is another fancy way of dealing with NULLs if you have some equations you have to run.

Let's say that you work for an organization that deals with different salary types:

• Hourly
• Salary
• Commissions

If you work at a company that has a sales force, you more than likely have all three of these salary types.  Now, do you keep a different table for each salary type and store the employees in those different tables?  Sounds like a little too much work for such an easy concept.  That is where SQL COALESCE() comes into play.

SQL COALESCE

COALECE allows you to specify a comma delimited list of expressions and it will return the first non-NULL expression.  So, in our example above, we have columns that show an employee's: hourly wage, salary, commission amount and number of sales.  We can then specify each expression with the wage calculation, as seen below:


SELECT COALESCE(hourly_wage * 40 * 52, salary, commission * salesnum) AS 'Total Salary'
FROM wages

This will then return the wage for each employee, no matter if they are hourly, salary or commission based.

Working with NULLS in your CASE statement

Lately I've been trying to focus more on database administration (DBA) techniques and how to improve my work.  I've signed up for a few Microsoft SQL Server websites and try to learn something new each day by reading their articles.  Today I came across the following link and am proud to know that I came up with this method for dealing with NULLs long before this article was written:

Writing CASE expressions properly when NULLs are involved

My situation was much like this persons: I went to investigate why a stored procedure wasn't returning values as I expected.  Once I jumped into the code, I found that the NULL in my case wasn't being recognized.  This is the best way to deal with that situation.

SSIS vs SSRS

From all my SQL experience, I've worked mostly with SSIS (SQL Server Integration Services).  This seems  to have become an antiquated technology.  Most organizations are now looking for people who have experience in SSRS (SQL Server Reporting Services).  Doing my research online, I've come across a very simple YouTube video that walks through steps of setting up reports in SSRS.

Enjoy:

Here is some even more basic instructions for SSRS that would be useful to know when setting up your server:

http://www.codeproject.com/Articles/291365/Basic-of-SSRS