Groovy MDM

From the Wikipedia…

A bar bet is a bet made between two patrons at a bar. Bar bets can range from wagers about little-known trivia, such as obscure historical facts, to feats of skill and strength. Some bar bets are intended to trick the other party into losing.

Well, I’ve worked out a clever little SQL gimmick that lets you do some unexpectedly sophisticated things with SELECT statements. Unfortunately, I’ve concluded this little trick of mine has no real application…except maybe as a bar bet. This is the software equivalent of H.O.R.S.E. Think of it as a trick pool shot to impress your geek friends.

The challenge entails building a summarized report from a table of individual events, e.g. a sales history report from a table containing each customer order. Of course, we’ll need to introduce some constraints to make it more interesting. Tell your friends you can generate this particular report from only the table defined below it using a single SELECT statement. (Naturally, your audience needs to know enough SQL for this to seem impossible or at least pretty hard.)

Required Report Columns:

• Customer
• Product
• YTD_QTY
• Previous YTD_QTY
• Previous Year QTY
• JAN_QTY …
• DEC_QTY

“Invoice” Table definition:

Fine Print:

• The table contains one row for every order the company ships. The same customer may have purchased the same product any number of times.
• The report is summarized to the Customer, Product level, i.e. each Customer, Product combination appears as a single line in the report.
• Ytd_Qty equals total of all invoiced quantities in current year (including month to date orders for current month)
• Prev_Ytd_Qty equals total of all invoiced quantities in previous year through today’s date
• Prev_Year_Qty equals total of all invoiced quantities in entire previous year
• Jan_Qty - Dec_Qty, each equals the total of all invoiced quantities for the most recent January, most recent February, etc. Hence, some of these columns will contain current year order information and other columns may contain previous year order information.
• The report can be run at any time, on demand with the totals accurately reflected.

So there you have it. I do have a solution which I’ll publish if anyone’s interested. Perhaps it will get you a drink if nothing else.

I’ve always loved the southwestern art that incorporates intricate, repeating geometric patterns like the kind you often see on rugs or pottery. I started to think about how I might create those patterns myself with software. But instead of jumping into some feature rich graphics package, I began experimenting with standard html — that is, drawing shapes with <TABLE>’s and <DIV>’s.

Though the first patterns were pretty basic, I achieved greater variety and sophistication over time. If I had the talent, I would rather be an artist than a software geek. Now maybe I can be both.

Here’s one of my more interesting works. (This is a screen cap, of course.)

If any one finds this interesting and wants to see the solution, I’ll post a link to the source html.