Understanding (some) SQL

Orion Reports often contain advanced SQL statements. Here is a brief overview of some of the statements and what they do.

Time Frames or datetime

SQL OLE Time - As seen previously SQL can use the datetime calculation in SQL OLE format for easy manipulation. This is seen in reports with a variable datetime reference such as Yesterday. An example of this type of datetime statement is:

WHERE ( DateTime BETWEEN 40120.5 AND 40121.5416666667 )

BETWEEN – This format is used in Advanced SQL reports to specify a report period of the “last X”, here the last month. This is accomplished be referring to the time between this month and a month ago (MONTH-1). This would be used as a portion of a WHERE (equijoin) statement.

BETWEEN dbo.Date(YEAR(GETDATE()),MONTH(GETDATE())-1,1) AND dbo.Date(YEAR(GETDATE()),MONTH(GETDATE()),1))

SET @StartDate and SET @EndDate – This can be used to set the date field for certain periods defined in SQL such a dd for two digit day, yyyy for four digit year and qq for quarter. The below example uses date difference (DIFF) to set the time frame for last quarter.

SET @StartDate = DATEADD(qq,DATEDIFF(qq,0,GETDATE())-1,0)SET @EndDate = DATEADD(qq,DATEDIFF(qq,-1,GETDATE())-1,-1)

JOINs

A JOIN allows the return of related data from multiple tables using a single SELECT statement. WHERE is used to create a simple equijoin. This type of join can also be created with an INNER JOIN statement.

Sometime joined tables will not have one-for-one matches in all records from both tables. An OUTER JOIN allows you to specify which table will include all rows and which table will not have related rows. These are always expressed as LEFT or RIGHT OUTER JOIN. Using a LEFT OUTER JOIN states that the table referenced to the left of the JOIN statement will include all records.

Aliases

An alias allows you to shorten a table reference and to use the same table multiple times within a single select statement. An alias in implemented using AS.