It is TSQL Tuesda… err Wedn… err Tuesday for August 2011.  This month the party is a day later and bringing us oodles of failure.

Adam Machanic is hosting this month.  He thought it would be a good idea to poke and prod us while getting us to torture ourselves.  It seems to be a month long theme (as he alluded to in his announcement of the topic).

See, the topic this month requires us to share our failures.  More specifically we are to share our ugly code that we know better than to do.  Crap Code is a failure.  At least we have come to recognize that it was crap code and that it was indeed a failure.  The question now is this:  What have we done about what we learned?

I put a fair amount of thought into this topic.  I could come up with several possible experiences for this topic.  I kept running into a wall though.  That wall was recalling detailed facts and examples of those experiences.  Many of them were documented and left behind with former employers.  I no longer have the “dirt” on those dirty little secrets.

Then it hit me!  I had posted some blog entries about some of my experiences and was certain that something would be applicable.  You know what?  It’s a good thing I have this blog going.  Not only does it help me to learn many things and write better, it serves as a repository for introspection and recollection.  So, I will rely on a couple of posts from the early days of this blog to help write my entry for TSQL Tuesday this month.  And, I hope that what I share and what I learned from my experience will prove helpful to somebody else.

The FAILURE / Crap Code

If you want, you can read the entire first part here then skip to the next heading.  Or you can continue reading as I recount that article.

I had been asked to provide a report that would provide data according to pay periods.  I had no calendar table yet I needed to be able to compare dates passed to the report and match them to this pay period calendar.  Despite requests to the business (the pay periods for this report did not line up with the normal business pay periods) to get a list of those pay periods in order to create a Calendar table, I got nothing.  I did however receive generic instruction as to the general time-lines for these pay periods.  Based on this, I decided to get tricky and try to meet some basic guidelines that I created for myself.

1. The payperiods need to be accessible to the query
2. No Physical Lookup Table
3. Do not hard-code the data in the code.

Based on that, I came up with a pretty neat little solution.

Code block

WITH periodstarts (StartID, StartDate,EndDate)AS (
SELECT 1 AS StartID,@StartDate,DATEADD(wk, DATEDIFF(wk, 0, @StartDate) + 2, 0) -1
UNION All
SELECT StartID + 1,DATEADD(wk, DATEDIFF(wk, 0, StartDate) + 2, 0) AS StartDate,DATEADD(wk, DATEDIFF(wk, 0, EndDate) + 2, 0)-1 AS EndDate
FROM PeriodStarts
WHERE DATEADD(wk, DATEDIFF(wk, 0, StartDate) + 2, 0) > StartDate
And Startid < 105

This worked well – for awhile.  Performance degraded over time, then it didn’t seem so cool.

Egg On Face

The update to that first article can be read in full here.  In short, that method really did not work out as well as I had hoped.  In the end, I had to create a table that would need to be maintained – at some point.  I populated the data in the table with 5 years worth of pay periods.  I hope somebody reads the documentation and takes care of that!

With the implementation of the table, I updated the CTE and the code being run by the report.  Performance of this updated version outperformed the first version – even when it was running fast.  I probably could have gotten away with doing an Itzik style numbers CTE in lieu of that recursive CTE – as I look back on it now.  Again, that is another level of learning and could potentially be an improvement on that first round of crap code I offered up for that report.

Conclusion

I learned from that experience.  First, it was a pretty neat little trick.  Secondly, I really should have tested it more.  Thirdly, I can likely still learn from it and improve on it because I am getting better at writing faster code and testing my proposed solutions (and I keep learning new tips/tricks).  Obviously something like this has not deterred me.  Quite the opposite really.  Because of a little failure like this, I work harder to get better at what I do.