{"id":1606,"date":"2013-03-18T00:00:00","date_gmt":"2013-03-18T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/solving-complex-t-sql-problems-step-by-step\/"},"modified":"2021-09-29T16:21:45","modified_gmt":"2021-09-29T16:21:45","slug":"solving-complex-t-sql-problems-step-by-step","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/solving-complex-t-sql-problems-step-by-step\/","title":{"rendered":"Solving Complex T-SQL Problems, Step-By-Step"},"content":{"rendered":"
\n

Repeatedly on the SQL Server forums, I see people struggling to solve common, complex business problems in T-SQL. More to the point, I see them struggling to solve them in a way that performs well and scales gracefully to large numbers of rows. My systematic approach to writing ‘difficult’ T-SQL is always the same:<\/p>\n

    \n
  1. Examine the data set carefully, and note down any particular characteristics, especially appearance of NULL<\/code> values, use of dates, and so on.<\/li>\n
  2. Make sure I fully understand the expected results and validate them, manually.<\/li>\n
  3. Break the problem down into a series of simple steps, write the T-SQL, view the results, adjust if necessary, and move on to the next step.<\/li>\n<\/ol>\n

    The key at each step is to understand and accept that the first, most intuitive solution is unlikely to be the fastest or most scalable. As I explained in my previous series, Writing Efficient SQL<\/a>, we need to look for opportunities to use set-based techniques that will help avoid non-scalable row-by-row processing, work with the smallest data set for any expensive calculations, scan the data as few times as possible, and so on. If my solution still fails to meet my expectations for performance, I try to “pair” with a co-worker, or ask advice from a T-SQL guru in the community, to help me find a better solution.<\/p>\n

    I recently helped solve a business problem that illustrates this process, and these ideas, perfectly. Based on data from a time clock, the challenge was to calculate based on a series of entry and exit times for employees, the amount of time between each entry and exit recorded, for each person. I present first a solution that will work on SQL Server 2008 and SQL Server 2008 R2, but has to scan the data a number of times to arrive at the result. Since that solution was not acceptable, I enlisted the help of a T-SQL guru, Peter Larsson<\/a> (known as “SwePeso” or “Peso” on the various community forums) who changed the approach to the problem, and found a better solution.<\/p>\n

    Finally, I’ll demonstrate how new analytic functions in SQL Server 2012, LAG<\/code> and LEAD<\/code>, make it possible to find a solution that performs just as well, and requires a little less lateral thinking.<\/p>\n

    Understanding the Business Requirements and Data<\/h1>\n

    Listing 1 provides the code to create the StaffHours<\/code> table and populate it with data that represents fairly the time clock data set with which I worked. It uses row constructors, introduced with SQL Server 2008, and so the code will not run in any earlier versions (see the code download for the 2005 version).<\/p>\n

    --2008 & 2012 version\r\nIF EXISTS ( SELECT * \r\n      FROM sys.tables \r\n      WHERE name = 'StaffHours' ) \r\n   DROP TABLE StaffHours; \r\n   \r\nCREATE TABLE StaffHours\r\n   ( \r\n      StaffMember CHAR(1) , \r\n      EventDate DATE , \r\n      EventTime TIME , \r\n      EventType VARCHAR(5) \r\n   ); \r\n\r\nINSERT INTO StaffHours\r\n   ( StaffMember, EventDate, EventTime, EventType ) \r\nVALUES \r\n   ( 'A', '2013-01-07', '08:00', 'Enter' ), \r\n   ( 'B', '2013-01-07', '08:10', 'Enter' ), \r\n   ( 'A', '2013-01-07', '11:30', 'Exit' ), \r\n   ( 'A', '2013-01-07', '11:35', 'Exit' ), \r\n   ( 'A', '2013-01-07', '12:45', 'Enter' ), \r\n   ( 'B', '2013-01-07', '16:45', 'Exit' ) \r\n   ( 'A', '2013-01-07', '17:30', 'Exit' )\r\n   ( 'A', '2013-01-07', '1:00', 'Exit' )\r\n   ( 'C', '2013-01-07', '08:33', 'Enter' ), \r\n   ( 'C', '2013-01-07', '17:33', 'Exit' ), \r\n   ( 'C', '2013-01-07', '17:35', 'Exit' );<\/pre>\n
    Listing 1: Creating and Populating the StaffHours<\/code> table<\/p>\n
    Figure 1 shows the “time clock” data set, essentially a series of entry and exit times from each member of staff.<\/p>\n
    \"1767-1-86d61e9f-53bd-461d-8a98-18e674fca\"<\/p>\n
    Figure 1: The original time clock data set<\/p>\n
    The requirement is to calculate the amount of time in hours, minutes and seconds between each Enter<\/strong> and E<\/strong>xit<\/strong>, for each staff member. The first point to note is that while there is usually a matching Exit<\/strong> row for each Enter<\/strong>, sometimes there is an Enter<\/strong> with no Exit<\/strong> or an Exit<\/strong> with no Enter<\/strong>. Here are the business rules:<\/p>\n