A couple months back, we had the opportunity to peek into into an application (ironically named Payback) that Colin A. Bradley inherited. Although the jury is still out on what exactly Adam did to deserve maintaining this, I'm guessing it had to do something with lots and lots of ALTER statements in a previous COBOL life. Any way, it's time to check in with Adam ...

Hello there, again.

I found this while messing with a report generated by my favorite application to maintain, Payback.

The picture shows a fairly standard looking report in Access showing enrollment by different groups of people in different seminars, by quarter of a specified fiscal year.

The first thing I'd like to point out are those totals on the right side of the report. You might think that the report itself groups the incoming data appropriately and sums certain fields to come up with those totals. That seems reasonable, but it's wrong. The stored procedure that feeds this report figures all the totals via 80 different select queries and temp tables. It seems to work ok, but from what I know of this database's (admittedly crappy) design, I'm fairly certain the same result could have been achieved much more directly and simply.

The main thing I'd like to point out though, the most obvious WTF about this report, is in the stored procedure that feeds this report. First, note in the picture of the report how there is a series of periods after each location. Now look at the stored procedure :

 

/* ED: SNIP */
/* SELECT ALL RECORDS FROM CURRENT ENROLLMENT THAT ARE GOING TO BE ON THE REPORT */
SELECT DISTINCT S_C_S_L.SEC,
CONVERT(INT,(C_EN.S_ID/10000)) AS Seminar,
C_EN.S_ID AS Section_ID,
Count(C_EN.ID) AS Enrollment,
S_C_S_L.B_D AS Begin_Date,
S_C_S_L.C+', '+S_C_S_L.S+'. . . . . . . . . . . . . . . . . . . ' AS Location,
C_S.T AS Title,
Count(C_S.O_S) AS Optimum_Size,
C_S.R AS Reference,
CASE WHEN DATEPART(mm,S_C_S_L.B_D) > 9
AND DATEPART(mm,S_C_S_L.B_D) < 13 THEN 1
    WHEN DATEPART(mm,S_C_S_L.B_D) > 0
AND DATEPART(mm,S_C_S_L.B_D) < 4 THEN 2
    WHEN DATEPART(mm,S_C_S_L.B_D) > 3
AND DATEPART(mm,S_C_S_L.B_D) < 7 THEN 3
    WHEN DATEPART(mm,S_C_S_L.B_D) > 6
AND DATEPART(mm,S_C_S_L.B_D) < 10 THEN 4
    ELSE 0
END AS QUARTER
INTO #ENROLLMENT
FROM ((S_C_S_L ... --ED: Snip

 

Notice this line in particular: "S_C_S_L.C+', '+S_C_S_L.S+'. . . . . . . . . . . . . . . . . . . ' AS Location,"

That's right, this sproc is kind enough to provide formatting of the data as well as the data itself! Which is good, because now that report won't have to work so hard formatting the data on its own. This is also good because now I can't use this sproc, which returns data which is needed fairly routinely, for anything else other than this one report! That'll teach me to be lazy, trying to reuse someone else's code.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!