SQL system for Full Merit History
Made by Piggy
You can learn more about how to build your SQL queries in
here
and about Javascript in
here
.
SQL
Script
Enter some SQL
Execute SQL
Enter some script code
// Variable holding the sum of unspent merits for each user. var total_unspent_merits=0; // List of username and total merit received for every user. var merit_received = RunSQLSynch('SELECT username, coalesce(tot,0) From userdata Left join (select toid, sum(merit) as tot from meritdata group by toid) on userid = toid;'); // List of username and total merit given for every user. var merit_given = RunSQLSynch('SELECT username, coalesce(tot,0) From userdata Left join (select fromid, sum(merit) as tot from meritdata group by fromid) on userid = fromid;'); // We go through the lists (they have the same lenght and already ordered in the same way). for (var i=0;i<Length(merit_received);i++){ // Just to be safe we check if the Username match, by getting the first coulmn of the current row. if (GetDataField(merit_received,i,0) == GetDataField(merit_given,i,0)){ // Calculate the unspent merits (using the forumula (Total Merit Receive/2) - Total Merit Given) var user_unspent_merits = (GetDataField(merit_received,i,1)/2) - GetDataField(merit_given,i,1); // If it’s positive add it to the total. if (user_unspent_merits > 0) total_unspent_merits += user_unspent_merits; } } // Clear the output information and write the result. Clear(); WriteLine("\nTOTAL MERIT UNSPENT:" + total_unspent_merits + "\n");
Execute Script
Results will be displayed here