PayPeriod Matching

Recently I was asked to alter a report to pull different more meaningful data. The particular report was pulling data from the datawarehouse. It had been recently modified to use a stored procedure (and subsequently improved performance 10 fold). Due to this change, the report started showing differently. The report was now paginating on the different matrices. Due to this minor display change, the creative wheels started turning and new requirements started developing from the real needs of the users of the report.

Through the quick review process, the requirements evolved from first displaying the current week and previous week data to being able to display data grouped by pay period. If possible, then the goal would be to correlate the data to specific pay periods. If the process was going to be too complex, or take too long – then sorting by weeks would be adequate. Correlating the data to current and previous weeks would be rather simple. The data was already present. The code was also conducive to making this correlation. So as a milestone, this task was completed first – as quickly as possible. Doing this also made it possible to more quickly jump onto the more puzzling requirement – which was more of a desirable, yet unnecessary requirement requested by the business.

Based on this, I came up with some enhanced requirements that would better define this request.

The payperiods need to be accessible to the query

No Physical Lookup Table

Do not hard-code the data in the code.

When reviewing these requirements, I was puzzled as to how to accomplish the task. I did not want physical structures that required continued maintenance and pay period updates. I would do that if it was absolutely necessary. I was hoping to achieve something that required little-to-no maintenance, was fast, accurate, and provided the end-user the desired results.

Thinking about it for a bit, I came across a few ideas but each evaporated when I found a flaw with it. It soon dawned on me a quick way to do it. I could use a recursive CTE, one known good pay period start date, and then some date logic. Date logic by itself did not seem useful enough for me since the pay periods were for specific ranges.

This CTE gives me the ability to create 4 years worth of pay periods on the fly. There are likely other ways of accomplishing the same task, this one suits me very well. To build the table, I start with a known valid pay period start date. From there, I can create the start and end dates of each of the pay periods over the next four years. To calculate the dates for the start and end for each period beyond the initial seed date, I used a method shown by Lynn Pettis in one of his Blog posts. In my scenario, each pay period is two weeks. To calculate the ending day of the pay period, I just subtract 1 day from the Result of adding two weeks to the start date. Verifying the data, I can see that I have start and end dates that correlate correctly to the pay periods.

The next step was to integrate the above into the query, and thus be able to correctly assign data to either the previous pay period or the current pay period. I was able to accomplish this through two left joins. This was the tricky part. I initially only created the CTE to have Start Dates and no end dates. This proved to be more difficult than I desired. The Table Joins started getting a little too complex and convoluted for what I had envisioned. I decided it would be much simpler to also include the EndDate in the CTE, thus drastically improving readability and ease of design for the query. That tricky part was now overcome to a degree, and I was able to associate some of the records. However, I was getting stumped on the Previous PayPeriod records. After trying a few things, I realized how easy the fix was to retrieve those records. A simple change to use Isnull in the Second Left join resolved this issue.

So now, my Join code is something like this:

[codesyntax lang=”sql”]

1

2

3

4

5

6

7

8

9

10

11

Inner Join clc.dbo.employeese

One.ntloginname=MIS.NTLoginName

Ande.lc_flag=1

Ande.activeflag=1

Ande.AdminRights=0

Left Outer Join PeriodStarts PS

On MIS.RecordDate between PS.StartDate andPS.EndDate

And@CurrentDate between PS.StartDate andPS.EndDate

Left Outer Join PeriodStarts Prev

On MIS.RecordDate Between Prev.StartDate andPrev.EndDate

AndIsNull(PS.StartDate,0)=0

[/codesyntax]

Since the only tying factor between my data is a recorddate and the payperiod range, I needed to be able to compare the recorddate to the startdate and enddate range. This works better than I had expected. As was expected, I would incur some cost to create the “PayDay” table on the fly as in the CTE. I also take a hit for the date comparisons, since I can only compare on a Range and not do an actual equality. The query is executing across two databases (1 is SQL 2005 and the other is SQL 2ooo) and returns in about 200ms, without any index tuning.

If I tune indexes in one of the tables (93% of total cost to the query comes from this table), I expect to see some improvement. Since the table only has a clustered Index, I started by creating an Index on NTLoginName, RecordDate, Product, LVCRequested and ProspectID. I know, I didn’t divulge the entire query, so some of this is coming out of the blue. However, those fields were in the SQL 2000 database and were required outputs for this query. By adding a new NC Index, I was able to reduce the Clustered Index Scan to an Index Seek. For this part of the query, it reduced overall cost from 93% to 33%. Logical reads on the table reduced from about 5000 to 76 – another substantial savings. Total execution time is down to about 140ms.

All in all, this is a good solution for the requirements at hand. Revisiting the self-defined requirements:

The payperiods need to be accessible to the query

No Physical Lookup Table

Do not hard-code the data in the code.

The only requirement that may be questionable is #3. I do need to pass a date into the proc to make this whole thing work. That date must be a known good payperiod start date. However, I have also set a default so that the date will populate to one that I know is good. #2 is a achieved since I did not create a permanent physical lookup table. The nice takeaway from this exercise has been the improvement in the query once again. Though the query is doing something a bit more difficult than previously, performance is better. It was also a nice exercise in thinking outside the box.

I considered doing that. I had no calendar table. A calendar table would have been useful and might be implemented in the next DB design (this one is being deprecated – I guess I could have included that).

In a recent article on SSG, I discussed how to use Extended Events to function in a Profiler like fashion. You can read about that here. I recommend reading that article first because it helps to lay some of the groundwork for this article. Within Management Studio, from a righ-click context menu, there is an[…]

SQL Server is full of good stuff. There are plenty of features to be used. Plenty of applications to help it. And there is even plenty of metadata within SQL Server to help you better understand your data and the queries that are being run. It just so happens that a couple of clients requested[…]

Today we have another installment in what is known as TSQL Tuesday. This month we have an invitation and topic given to us by the infamous Kenneth Fisher ( blog | twitter). Today, the invitation is for us to share our stories on how we like to manage security. Or at least that is the[…]

Recently I wrote an article about Capturing Online Index Operations. In that article, I discussed a problem that I had encountered. Well, there were multiple problems. One was an issue with a vendor app that had some hidden module that was performing online index defrags that was causing corruption in a couple of indexes every[…]

One of the things that DBAs love to do is keep their servers running and healthy. A healthy server, after all, is your ticket to a stress free day and a full night’s sleep. Granted this not a guarantee but it sure helps make life easier. We are always looking for the big ticket items[…]

What a fun week we have tuned up for the folks in Las Vegas. It is the first full week of January and there is this huge convention going on near the strip. And as timing would have it, this week is also the perfect time to have our User Group meeting. What major conference[…]

Tis the season for TSQL Tuesday. Not only is it that season again, but it is also the Holiday season. During this season, many people start to think about all of the things for which they are thankful. Many may start to think about their families and friends. And many others will focus more of[…]

This past weekend I had the opportunity to go visit Washington DC. It was just the second time I got to stay in the Nation’s capitol for more than just a few hours. The previous opportunity came with last years event which I talked about here. Sadly, my time was far too limited this trip and[…]