A week or so ago I asked Jacob Sebastian (Blog | Twitter) if there was anything I could help him with in the monumental task he’s undertaken with the now bi-monthly T-SQL Challenges. He asked me to create some ‘Tricky’ Data and do the logic testing setup for some of the challenges, starting with 25. I agreed and started working on it. In my opinion, TSC25 was one of the most involved challenges to date. There were more things to take into account than any of the other recent challenges I can remember. Creating data for this was no different. Because there were so many different things to test for on this one, I’m sure there are still situations that this data doesn’t cover… but that’s OK. The logic testing isn’t really meant to cover every possible situation that could ever occur or cause something to fail, it’s just supposed to throw many different things at the solutions and see how they weather the storm.
I’ll admit, when I started, I was still way too hung up on the previous scheduling challenge and kept thinking that the duration mattered. You can probably still see some of that in the logic testing data with some of the durations being very long. It wasn’t until well into the process of creating the data and generating the expected output that I finally got it through my head that the duration didn’t matter for this one. In any case, I think I covered quite a few of the extraneous situations allowed for by this challenge.
Here are some of the things covered by the logic testing:
- All basic types covered by the rules
- Duplicate Task Names that occur on the same day
- High values for Duration
- Very High values for Val (Adding 255 years to a smalldatetime causes an overflow)
- Unneeded values for Duration
- Fairly Long Task Names(Should have made these longer, but even this length broke one solution)
- Meetings with a Scheduled start date that didn’t match up to their recurring Schedule
- Meetings with > 1 year recurrence.
- Meeting that only occurs one time at the exact end of its schedule time.
- Meeting that starts at 00:00
- Meetings that could never occur
- Meetings that would occur 1 minute past their schedule end date
A couple of the solutions were very close and only missed on one or two of those things. Remember that all of these challenges are ongoing and you can submit solutions at any time. Solutions submitted after the fact will be evaluated periodically, but you should run them through the all of the tests yourself to ensure that they pass before submitting them.