Order by Calculation Result?

[christrinder]

Hello,

I developed a system at work which allowed our various businesses to input particular statistics. The site also allowed the user to view a league table based on these inputs. The league tables are currently by month, however, we now need to produce a league table for a rolling 12 month period. Each input is obviously recorded against a statmonth number and statyear, but how could I go about producing a total for these 12 months, and then ordering the businesses in a league table by this new total value?

Any ideas?
Chris

[raf]

Lots of idea.
The easiest method (what i would do) is have a seperate table for each leage/team where you store the totals for the last year. After inserting a record (in another table) you update this field. I'm not sure, but i think you need the totals from the last 12 months? In that case the updatestatement is somewhat more complex.
Something like

sql = "update table set field=field+newmonth-13monthsagovalue where team=theteam"
sql = replace(sql,"newmonth",request.form("valuenew"))
sql = replace(sql,"theteam",request.form("team"))

sqlbis=select score from table where team=theteam and month=themonth and year=theyear")
sqlbis = replace(sqlbis,"theteam",request.form("team"))
sqlbis = replace(sqlbis,"themonth",request.form("month")
sqlbis = replace(sqlbis,"theyear",(CInt(request.form("year")) - 1))
execute the sqlbis and retrieve recordset rssubtract
sql = replace(sql,"13monthsagovalue",rssubstract.Fields("score"))


I don't know how you store the date's and so. If you record a real date, it's even easier, since you can then use the dateadd() function.
I suppose there aren't that much teams so including an extra field wount make your db explode. And updating them when inserting the new record wount cause much delay.
You'll probably also need to include a "last updated" field in the team table, so you can inducate which teams were already updated for the last month. Selecting and ordering is then real straightforward:
select * from teamtable where league=theleague order by totscore desc.
I can immagen that this query should be performed more often and should have a shorter response time then the insertion/updating of a new score, so i shouldn't wory about the 0.01 seconds that the updating would take.


Or you can write a stored procedure to compute the totals at runtime, store them in a temporarely table and then retrieve them as a recordset that is ordered on the computed variable. This approach saves you some db-space but takes more runtime-resources. Also, in this approach, each request means that the data has to be recomputed from scratch

These are just some idea. It depends on your db-design and app (what you want to show in the browser) what the actual code will look like

[christrinder]

Hi Raf,

Cheers for that, some interesting points. This is really taxing my brain, especially because I am so new to ASP. I probably use a very long-winded, resource demanding way of doing things, but my thoughts were along the lines of this...

strSQLleague = "SELECT int(sum(labour)) AS LT FROM stats WHERE dealership = 24"

This gives me the total labour sales of one particular dealership. But how could I make the calculation include only the top 12 records. I tried adding the usual TOP 12 in the select statement, but it didn't seem to do anything. This approach also dosn't address the issue of ordering the league table and would also require a separate select statement for each dealership. I can cope with an unordered league table for now, so if I could work out how to select the top 12 records, I could then add a simple count variable for the select statement.

Any ideas with this TOP 12 problem?

Thanks,
Chris

[raf]

Christrinder,

well. best sollutions and finding them here are something of a paradox. Not that this forum isn't perfect :D but the best way to add a functionality to your site (and that's what is is, not a 'problem to solve') will probably take some modification in more then one page, and requires some planning and analysing. Thats where the fun stuff is !! Knowing your tools and being able to sit back and think about the most effective way to solve something (often required db-planning or modification) etc.

Are you up to it? Think so.

I'm not gonna nag about functional analysis and db-design (well, not more then thusfar) but this is what i would do:
- define your response and process time requirements
- define which users need to be able to perform which action
- define functionalitys for your site, and the importance of each one
- define pageflows and info needed in each page
- list the querys (--> the info that needst to be selected/updated/inserted/deleted) that need to be ran + the order + frequency + importance + required performance for each action

Depending on these (and probably other) choices, you then decide on a db-design and screenflow. I alway work like that, so i know the best db-design to answer all my needs. I'm not a normalisation freak,"overall performance" isn't my primary goal. I select the processes that need to be optimized and build my db an app around that. These processes are primarely the "show or record" info action for the user.

So, to me, showing this ordered table is critical. It's probably one of the most important funcionalitys in your app. So your db needs to be organised to perform this action as fast as possible. To me, an optimized db is a db that can be queried for critical processes with a minimal of joins or subquerys. (Other, non critical operation, like modules for yourself to insert new values and stuff like that, well, there i also look for the most efficient way but i don't mind a delay of 0.05 seconds. The most important thing is that these processes don't compromise the critical once by locking tables to long or by taking up to much processing time on traffic-peak moments)

What i would do is move as much db-processing to the imputside (see previous post). I wouldn't make any compromise on displaying the table as i want.
Also, when you insert the new value, you'll probably gonna run one or more selectquerys on the db (or maybe even the scores table) before the new value is inserted. I'd try to use one of these already required selects to grab the thismonth-12 value that you need to substract. If you then drag it along in your form(s), (in a hiddenfield) you'll save a db-call to get the info you need for the updatestatement. The update is then also a lot simpler and faster. something like

updatevalue=CInt(request.form("newvalue")) - CInt(request.form("valuetosubstract"))
if updatevalue >= 0 then
updatesyntax= "+" + CStr(updatevalue)
else
updatesyntax= updatevalue
sql = update table set totscore=totscore " & updatesyntax & " where team=theteam"
etc

You get the idea? (Also, you'll probably haved queried the teams-table, where the total score is recorded, by the time you get to the update script, so you could even compute the value in your asp and have a normal 'SET totscore=newvalue' statement.)

It's just the way i work. Feel free to take another approach. (I started as a business expert, moved on to making business anaysis, functional analysis and finaly coding because most analyst i worked with just didn't do a good job and were just codeproducers that just wanted the job done. Just buy a bigger machien if it needs to go faster, you know)

I think, that in your approach, you need something like

SELECT SUM (score) AS [totalscore] FROM table where ID IN (SELECT TOP 12 ID FROM table WHERE team='theteam' ORDER BY date desc)

to get the value for one of the teams.
And you'll need to have a distinct somewhere to get the scores from all the teams + a join to get additional info (like teamname etc from other tables. I mean, it can probably done, but at what expense ? As far as i know, you cant combine DISTINCT with SUM so you'll need at least a temporarely table. Or run 12 sql statement with a join combined with a subquery. + what happens if you get 200 teams ? Or even just 13. Fix all your code ? Build in a loop?
To do this sort a thing, you're moving dangerously closely to an OLAP tool (there's a reason why they are so expensive).

So, i woudn't even think about taking this approach. In my approach 1 simple select, without a join, just does the trick.

Basically, this is why i think my approach is beter (how presumptious of me myself and i :))


About TOP
You didn't include your statement, but maybe you didn't realize that TOP doesn't run on a table. Heh ?! Come again ?! It runs on a view, that is, a temporarely sort of systemcreated and managed table that contains the resulting records of a db-select or filter or join process. If you run a join query, for instance, the RDBM will create a table with records that contain fields from these two tables. This intermediate table, can then be further processed (filtered,joined, serve as input for another query ...) Now, TOP runs on such a view. If you run top, then your sql statement needs to have an ORDER BY clause. It doesn't select the top twelve dates from your table, it selects the top 12 records from the view
SELECT TOP 12 ID from table ORDER BY date desc -- > will return 12 last inserted record-ID's
SELECT TOP 12 ID from table ORDER BY date desc -- > will return 12 first inserted record-ID's


Hope this helps.


EDIT
An additional reason why your approach will fail (hard word, but true, it will faill soner or later) is that TOP doesn't choose between equel values. So if you ever have the same dates (and it will happen, when you need to convert the db for whatever reason or so (i came across several of these 'can not happen' situations) you can and up with 50 or more returned records on a TOP 12 select.

More info from access helpfile:
---------------------------------------------
TOP n [PERCENT] Returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose you want the names of the top 25 students from the class of 1994:
SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC;

If you do not include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause.

The TOP predicate does not choose between equal values. In the preceding example, if the twenty-fifth and twenty-sixth highest grade point averages are the same, the query will return 26 records.

You can also use the PERCENT reserved word to return a certain percentage of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose that, instead of the top 25 students, you want the bottom 10 percent of the class:

SELECT TOP 10 PERCENT
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage ASC;

The ASC predicate specifies a return of bottom values. The value that follows TOP must be an unsigned Integer.

TOP does not affect whether or not the query is updatable.

[christrinder]

Hey Thanks Raf.

That, I think, is the most comprehensive answer I have ever seen on any forum! Some really interesting points there.

I too have come from a business (actually marketing) background, so my perspective on the development of such web apps is of an operations view.

Anyway, I used a temporary fix for now. Every time a business inputs a new input, providing the month isn't 1, then their value for labour sales is added to the field in the user table. This is obviously their cumulative figure for the year, and the value by which I sort the league table. Works for now, but I want to revisit it and do something a little more accurate... I will be refering back to this post!

Thanks again for your help.
Chris

[raf]

Your welcome.

I'm gonna steel your problem/functionality. :D
It's perfect to give as a homework assignment to upcomming analists/coders/db-designers. Curious how they will tackle it.
+
I just outlined some principles. I'm planning on writing an article on the way i put them into practice. --> i made a sort of addition to ishikawa's (type of diagrams). I start with the main goal (fill in a shoping cart order or so) an then go to all functionalitys. Basically I just drill them down until i get at a 1 data level. Once you've got that picture, the db-design kinda emerges from it.
This functionality is perfect as an illustration.