Category: SQL Server

Select Distinct works fine when you are retrieving just one column.

For example, I have a table called tablea that has two columns called col1 and col2 with two rows. They are:

col1 = January
col2 =  2012

col1 = …

I came across a scenario where I needed to write conditional statements in my WHERE clause of my SQL that was more complex than what a ‘CASE WHEN’ statement could provide.

I have two parameters, @FieldA and @FieldB. Both …

Let’s say you have 5 rows in a table. Column1 is populated for 4 out of the 5 rows with the following data:

599
abc
xyz
456

The following SQL will provide the results as such:

599, abc, xyz, 456…

The first SELECT statement gives you a value of ‘NULL’.
The second statement gives you a value of ‘0’ because the true value returned is
‘NULL’

SELECT SUM(number_of_whatever) AS CalculatedValue
        FROM some_table
        WHERE some_column = 'some_value'
          AND (some_date_column = '2011-12-01' 

…

Let’s say you have a table called ‘car_make’ and another table called ‘car_model’. There is a one to many relationship between make and model. You want to bring back a result set of all makes, 1 row per make, but …

Let’s say you have a database column called date_of_birth and you want to calculate the person’s age and create a new column called Age. It can be done like this:

(CONVERT(char,GETDATE(),112)*1-CONVERT(char, date_of_birth,112))/10000 As Age

…

Get first day of current month
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AS FirstDayOfCurrentMonth

Get last day of current month
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) +1, -1) AS LastDayOfCurrentMonth

Get first day of previous month
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, …

IF (SELECT product from product_table WHERE product_type = @productType) = 'HAM'
BEGIN 

SELECT product 
  FROM product_table
  LEFT OUTER JOIN meat_table b on b.product = product_table.product
 WHERE vendor = @vendor END 
ELSE

SELECT product
  FROM product_table
  LEFT OUTER JOIN veggie_table b 

…

Let’s say you have a varchar field on a SQL table that is a length of 10 and it contains only integers.
If the value of one of the fields is 5, it is stored as ‘5’, not ‘0000000005’. This …

This SQL statement will produce one row for each record found on the account_table for a given account number

We want also want to join the name and address table to the row. We only want one row from the …