SQL DATEADD() Function

It has been quite a while since I have provided a tip for you guys/gals, but now I am back on the wagon.   I will be posting on a regular basis.  I have also decided to stop numbering the tips from this day forward. That being said...

Yesterday I ran across a problem where I had to check to see if a certain file called 'Test' was received within the last two years.

To accomplish this, I used the DATEADD() Function which adds or subtracts a specific time interval from a given date.


Syntax:

DATEADD (datepart, number, date)

The datepart can be broken down by year (yy, yyyy) , quarter (qq, q) , month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw, w), hour (hh), minute (mi, n), and second (ss, s) just to name a few.

Example Code:

Select *  from Table where DocumentType = 'Test' 

and  DateReceived >= DATEADD (yy, -2, GETDATE())

If you like this post and want to see more, follow me on my website www.chadcompton.com Or if you prefer...

Concatenate 2 Integers Into 1 Varchar (SQL)

Yesterdays Just A Tip Felt so good, I thought I would do it again today.  Twice in one week.  You guys & gals get a great tip, while I get another notch under my belt.  Win.  Win.

Today's coding issue involves 2 Integer fields that need to be concatenated into 1 big field and that field needs to be Varchar.  To accomplish this, you will need to Cast each  Integer as a Varchar then concatenate them.

For Example, in an SQL table called tblPetOwners you have 2 fields (OwnerNumber & PetNumber)   which are Integers and wish to join them with an '&' in between them.  To do this, you have to CAST them both as Varchar before concatenating them.  Below is the best way I have came across to accomplish this task and I show you the data in the table, the code itself, followed by the results.

Table tblPetOwners Data:

OwnerNumber
PetNumber
223122
543224
643233
235432
332342
654523

Example Code:

SELECT CAST(OwnerNumber AS VARCHAR(10) ) 
+ ' & ' + 
CAST(PetNumber AS VARCHAR(10) ) OwnerAndPet, OwnerNumber, PetNumber 
FROM tblPetOwners

Results Data:

OwnerAndPet
OwnerNumber
PetNumber
223122 & 543224
223122
543224
643233 & 235432
643233
235432
332342 & 654523
332342
654523

If you like this post and want to see more, follow me on my website www.chadcompton.com

Or if you prefer...

Union & Count Together in A SQL Query

I know it has been a while since I have given you a good tip...So without further ado.

Today I had a situation where I needed to give a count of items that were across a couple of tables and involved a few unions in SQL.  Since my query took up about 2 pages, I figured I would make a simple version for all you folks out there reading this.  I hope it helps.

For Example, let us say you have two tables ( Results & ArchivedResults) and were needing to get a count on them both.  Let us also assume you didn't use a JOIN because of the different criteria in each SQL statement.  Based off those results you need to get a count.  Below is how this can be accomplished.

Example Code:

SELECT PullResults.Name, COUNT(*) 
FROM  (SELECT Name FROM Results
UNION ALL
SELECT Name FROM ArchivedResults) as PullResults
GROUP BY Name
ORDER BY Name

If you like this post and want to see more, follow me on my website www.chadcompton.com

Or if you prefer...

SUM Using Group By (SQL)

At some point, you will be required to use the SQL GROUP BY clause with the SQL SUM function.  My time to use this was today and I thought I would create this post to help anyone else that may be needing to accomplish the same.

For example, you could also use the SQL SUM function to return the name of the department and the total sales (in the associated department).

Example Code:

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department;

Because you have listed one column in your SQL SELECT statement that is not encapsulated in the SQL SUM function, you must use the SQL GROUP BY clause. The department field must, therefore, be listed in the SQL GROUP BY section.

If you like this post and want to see more, follow me on my website www.chadcompton.com

Or if you prefer...

AVG Function (SQL)

Over the last couple of coding posts I have showed you how to use the SUM function.  This being said, I thought I would show you how to use the AVE function in SQL.  


Use the AVG() Function to get the average of a of a specific numeric column.  Below is an example of it being used.

SQL AVG Example:

SELECT  AVG(ColumnName) 
FROM TableName;

If you like this post and want to see more, follow me on my website www.chadcompton.com

Or if you prefer...

SUM Using A Formula (SQL)

Today I was asked to pull some data and give a total difference of two different columns in a table in SQL.  Then I realized that not everyone would have came across this.  Even though in the past I have showed you how to use the SUM Function in SQL.  This time I thought I would go one step further and show you how to get a SUM while using a formula in SQL.

Use the SUM() Function to get the total of a specific numeric column.  With that being said, the Sum function doesn't need to be a single field.  In this case, you can use a formula.   Kind of like, getting total Profit by subtracting Expense (Column1) from Total Sales (Column2).   Below is the specific syntax to use.

SQL SUM Example:

SELECT  SUM(Column1 - Column2) 
FROM TableName;

Please note, you can also preform groupings while using this function as well.  I will show you those at a later date.

If you like this post and want to see more, follow me on my website www.chadcompton.com

Or if you prefer...

SUM Function (SQL)

Today I was asked to pull some data and give a total of a specific column in a table in SQL.  I then realized that not everyone would have came across this.  

With that said, I thought I would type up this little blog to explain and show it to you.

Use the SUM() Function to get the total of a specific numeric column.  Below is the specific syntax to use.

SQL SUM Example:

SELECT  SUM(ColumnName) 
FROM TableName;

Please note, you can also preform formulas and groupings while using this function as well.  I will show you those at a later date.

If you like this post and want to see more, follow me on my website www.chadcompton.com

Or if you prefer...

Multiple Random Selected Rows From A Table (SQL)

This weekend  I was asked to pull random sample of 40 rows.  I was thinking that was super easy, but then they put a kink in the request. They wanted the 40 random rows with 20 from type 'A' and 20 from type 'B' from a specific table (TableName) in Microsoft SQL Server. The first was 20 random people where ColumnName = 'A' and the second was 20 random people where ColumnName = 'B'.

Using SQL To Select Random Rows From A Table as a stepping off point.  I have altered the code below to use multiple random SELECT  with a UNION in SQL to accomplish the task.  Easy as pie.

Microsoft SQL Server:

SELECT * FROM 
(SELECT TOP 20 * FROM TableName 
WHERE ColumnName = 'A'
ORDER BY NEWID())
A
Union
SELECT * FROM 
(SELECT TOP 20 * FROM TableName
WHERE ColumnName = 'B'
ORDER BY NEWID())
B

If you like this post and want to see more, follow me on my website www.chadcompton.com Or if you prefer...

SQL To Select Random Rows From A Table

Today I was asked to randomly pull random 25 records from a database table for a client. It took me a minute or so to remember how to accomplish this.  With that being said, I thought I would type it up for everyone to be able to benefit from it.

I know there are a lot of ways to select random records or rows from a database table, but in this quick article I will show you the Microsoft SQL Server and MySQL syntax required to accomplish the end goal.

Microsoft SQL Server:

SELECT TOP 25 ColumnName FROM TableName
ORDER BY NEWID()

MySQL:

SELECT ColumnName FROM TableName
ORDER BY RAND()
LIMIT 25

If you like this post and want to see more, follow me on my website www.chadcompton.com

Or if you prefer...