Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen: 13 Productivity Tips for Database Development Using Transact-SQL : Page 4

This installment of "The Baker's Dozen" presents a variety of real-world database situations and how you can use Transact-SQL and SQL-92 to tackle these issues.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Tip 8: Date Functions
Requirement: You need to retrieve daily order information and summarize it by week for a weekly report or weekly graph.

The two brief code snippets below demonstrate how to use SQL Server's DATEPART function to solve this requirement. The first snippet presents a UDF that converts any date during the week to a Saturday date for that week. The second snippet demonstrates the UDF as part of a query against orders, which summarizes the data by the week-ending Saturday date. You can use this result set for a weekly report or graph. This represents another example where you can use Transact SQL to prepare a result set with little or no data munging in the business layer.

In constructing an UPDATE trigger, you need to remember that the trigger fires once, regardless of the number of rows affected by an UPDATE statement.

CREATE FUNCTION dbo.GetEndOfWeek (@dDate DateTime) -- Converts date to the Saturday date for the week RETURNS DateTime AS BEGIN DECLARE @dRetDate DateTime SET @dRetDate = @dDate + ( 7-DATEPART(weekday,@dDate)) RETURN @dRetDate END SELECT dbo.GetEndOfWeek(OrderDate) AS WeekEnding, SUM(Amount) AS WeekAmount FROM OrderHdr GROUP BY dbo.GetEndOfWeek(OrderDate)

DATEPART returns an integer based on the portion of the date that you want to analyze. "SQL Server Books Online" lists all of the date portions (dateparts) that the DATEPART function can analyze. For example, you could use the quarter datepart to summarize data by quarter.

The Weekday datepart is scoped to SQL Server's setting for the first day of the week. The default is Sunday. To set the first day of the week to a different day (e.g., summarize sales from Monday to Sunday instead of Sunday to Saturday), use the SET DATEFIRST command:

-- Change first day of week from default of -- Sunday (7) to Monday (1) SET DATEFIRST 1

One final note on dates: in many instances, you might build queries that compare dates where the date column has a time other than 12 AM, yet the date comparison variable is a date with no time defined.

For example, a row with a date of "05-26-2003 22:10:00" will not be retrieved if the query calls for all rows where Date <= CAST('05-26-2003') AS DATETIME. This is because SQL compares 5-26-2003 at 22:10:00 to 5-26-2003 at 00:00:00 (12 AM), and finds that the former is not <= the latter. If you want all data through 5-26, including any transactions that hit right up to 11:59 PM that day, you could add one day to the comparison date, and change the logic to less than (Date < CAST('05-26-2003') + 1 ).

Tip 9: Data Conversions with CAST and CONVERT
Requirement: You need to produce an English-like message that describes the results of an accounting process. The string should read something like "100 Employee Checks were generated on 11/15/2004 at 5:07:02 PM, for a total dollar value of $134,123.11".

SQL Server provides the functions CAST and CONVERT that allow you to make different data types work together. Listing 8 demonstrates how to use variables of different data types (date, decimal) to build a message string that could be used for a log or display.

Tip 10: Update Triggers
Requirement: You need to make sure that any UPDATES performed against a table automatically update a timestamp column.

An UPDATE trigger is a specific type of stored procedure that fires every time an UPDATE statement executes against the table. You can use triggers to enforce specific rules, and/or to ensure that specific columns are updated.

UPDATE triggers provide access to two critical system tables that contain the state of the row before it was updated (DELETED) and after it was updated (INSERTED). You can use the contents of these tables to implement basic audit trail functionality, which I'll cover in Tip #11. For now, I'll concentrate on setting up an automatic update of a LASTUPDATED column.

In constructing an UPDATE trigger, you need to remember that the trigger fires once, regardless of the number of rows affected by an UPDATE statement. Those new to SQL Server sometimes make the mistake of writing trigger code with the assumption that only one row will be updated.

CREATE TRIGGER Upd_Client ON dbo.Client FOR UPDATE AS UPDATE Client SET LastUpdate = GETDATE() FROM Client C JOIN Inserted I ON I.PrimaryKey = C.PrimaryKey

Thanks for your registration, follow us on our social networks to keep up-to-date