Author: way0utwest

My T-SQL Tuesday post for April is based on Aaron Nelson’s subject of reporting. If you’re unsure of what T-SQL Tuesday is, you can read about it’s origins here.

The Reporting Table

Years ago, actually well over a decade in my past, I was working for a small company and building an order entry/CRM/inventory/etc. system for them. We had upgraded their old DOS/Foxpro system to VFP/SQL Server 6.0, and we we in the process of adding some new enhancements. Most of these were actually more sophisticated reports that we designed to help them handle larger volumes of business needs.

One of the reports I was in charge of building was based on a spreadsheet that one of the supply people had built. It used a look back at orders for the past few months and then the inventory we currently had and give the person a guess about what quantity of the various products to order.

So I ported it, encapsulated the same logic that was in the XLS, and deployed it. A day later I was coming back from lunch when the supply guy, and the owner of the company stopped me to say that the report didn’t work and that I needed to fix it ASAP.

I put my tie back on and got to work, digging in with the supply guy. The first thing he showed me was two copies of the report from that morning. They had different data, but the same parameters (I was very glad I’d printed the parameters on each report). I was puzzled at first, but then noticed that the reports had been run a couple hours apart. Since the report was based on sales up to the current day, and the current inventory levels, it’s entirely possible that if the report was run at two times, the data might be different.

“That’s not right.”

That’s what I heard, and I started to show the guy how I could structure queries as of the time of each report in Query Analyzer and get the data he saw. However he didn’t want to hear it and I realized quickly that I needed another solution.

So I inquired how they used the report in more detail. He told me that they’d run the report and then share the data between a few people to discuss what to order. I told him he could “photo copy” the report (we printed things back then), and pass out copies, but his head started shaking before I got much past “copy machine” and so I stopped.

Back to the drawing board, and with a vacation scheduled, I needed solution quickly. My investigations led me to understand that the reason this was never a problem before was that the supply guy manually got data from other reports and assembled it into a spreadsheet by hand. That took him most of a day, and he’d email the report to everyone that afternoon and the next day as they went through their meeting, they would adjust factors on the XLS to decide how much to order.

I’d built in factors as report parameters, but now this was embedded inthe application and so you’d have 2-3 people playing with parameters on reports with their laptops, and comparing numbers. A static printed report wouldn’t work.

What I came up with was a report table. I built a table that duplicated all the columns of the report. So for a report that had productID, Jan Sales, Feb Sales, Mar Sales, Last Order, Current Inventory, etc., I had this table.

CREATE TABLE OrderingReport (

productID int,

JanSales numeric(10, 4),

FebSales numeric(10, 4),

MarSales numeric(10, 4),

LastOrder numeric( 10, 4),

CurrInv numeric( 10, 4)

…

)

I then scheduled a job that would “populate this table” every night around 2am, a time that was unlikely to impact anyone’s work schedule at this company. I reworked the report to query this table, using filters if they limited it by product, and return the same information all day.

I deployed it and the supply group was thrilled. Then could easily run a report, and it would compare to the data that a co-worker had. They could run reports, change factors, re-run them, change back parameters, and still end up with consistent reporting for the day.

Of course, I added one last enhancement to the application before I left. I had to give them a “reload” menu item to reload the entire table is some significant sale occurred, but this satisfied their need for consistent reporting in 1995.

I ordered a few recently from NewEgg. One for a desktop, one for a laptop. We’ll see how they do, but I was excited enough to snag this picture when they arrived.

 

$200 for 80GB.

After my editorial “The Missing Certification” I was amazed at the debate that took place. As of the time I’m writing this, this thread has over 7,000 views and 600 replies!

It’s incredible to see people debating certification, but what’s evolved is kind of a working thread that looks at creating a new certification that’s in between the MCITP and the MCM. I think those certifications are too easy, and too hard, respectively, for the average person.

In my mind, we ought to have a stepping stone for certification that includes some harder tests along with a narrower focus that can better assess if an individual has a skill. So far the debate has been a general focus, but I hope that they work towards a narrow focus that creates tests in specific areas.

As an example, in my mind, I’d like to have a series of T-SQL tests that examine how well someone can solve common problems, and implement well performing solutions. I’d think there would be 2-3 tests here, and that some level of scoring for each one that could be presented as a part of your resume, but also would show that you understand how to use the language. I’d anticipate tests that covered:

• Basic ANSI SQL
• T-SQL Enhancements (from 2005/2008)
• High performing T-SQL – Covering improvements that avoid RBAR type solutions and write code that performs extremely well.

We’ll see what happens, and while I know certification is no guarantee, I think that we can get a better process that has a good chance of actually assessing a person’s skills.

I’ve had the Prius for 3 years as of Mar 31, 2010, and it’s been a great car for us. As the 3 year mark turned over, I was at just over 60,000 miles, which is a pretty heavy load. That level of driving really contributed to my positive experience since it means that I saved a lot of gas over most other cars.

As with most things in my career with SQL Server, I don’t see the idea of going with a hybrid as black or white. It’s not something that I think we should push as heavily as we are in this country. Personally I think the decision to buy a hybrid “depends” on many factors. I’m not pushing the hybrid, but I do think it works in some situations.

As far as my experiences. In 3 years, and 60k miles, I haven’t had any issues. If I don’t have any more in the next year, then I think I’ll have wasted a couple thousand $$ on an extended warranty at this rate.

In 3 years, I have

• changed the oil about every 5k miles with synthetic oil.
• replaced the tires
• rotated tires
• replaced seat belts (4 of 5) that my dog ate

Other than that, no issues. I had to jump start it once, but that’s it. No other repairs. It’s been a solid, reliable, Toyota-like car for me. Just no issues.

The bad things I’ve felt are the handling in the snow. It does not deal with slick surfaces well and the rtaction control is a POS for getting started in snow. I wish that thing would not enable until you hit 5 or 10 mph, but that’s about it.

The interior (cloth) has taken a beating with 3 kids and dogs, but otherwise everything works well and it’s the car that I tend to drive most of the time.

If you drive a lot, it’s worth looking at the Prius. But do so from a financial perspective. I’m not sold this is the best way to go for the environment (I vote for hydrogen and fuel cells w/ electric motors). However if you drive a lot, this can can potentially save some serious $$.