Running sum for last 12 months, SQL style

I’m going to tell you a little secret. Come, sit closer.

You know your running chart for every other dashboard, showing

the SUM / Average / Top / Least / etc.

of Sales / New customers / Quotes  / etc.

for the last…

1 hour / 1 day / 1 week / 4 weeks / 12 months / etc…. ?

In SQL world I’ve been solving the problem with dateadd() getdate().

SELECT
sum(case when month(orderDate) = dateadd( mm, -1, getdate()) then orderAmount end) as [total order amount last month]
FROM
GoatOrders
GROUP BY
month(orderDate)

Of course if you want your chart to contain the last 12 months, you’d have to repeat the select 12 times. No brainer, redundant query.

Here comes this little trick I’ve been using:

SELECT
SUM(orderAmount), month(orderDate)
FROM
GoatOrders
WHERE
orderDate >= dateadd(yy, -1, getdate())
GROUP BY
month(orderDate),
year(orderDate)
ORDER BY –this is where the magic happens
year(orderDate) desc,
month(orderDate) desc

The ORDER BY has it all.

On a completely unrelated note: USA won 2-1 against Ghana!

Conclusion: Now you know how to write even shorter code for your running charts.

When you realize

That more people attended the Argentina – Bosnia game (74, 738) than the opening Brazil – Croatia game (62, 103) at the 2014 World Cup in Brazil

 Conclusion: Argentina is gooooooooooooood.

The Good, the Bad and the Ugly – part 2

A little while ago I did a roundup on two LMS out there – Opigno (‘The Good’) featured the article on their site as well.

Since that post we’ve been continuously testing Opigno, and I still couldn’t manage to install Moodle (‘The Bad’). However, I haven’t said a word on ‘The Ugly.’ Therefore, ladies and gentlemen, this is the time that I finish the article, and speak up about edX.

For those who are not familiar with edX – another platform in the arena, ‘cept for that this one actually puts the platform in good use as well. You can go to edx.org and start participating in free online classes. I do, too, this is what my dash looked like in June, 2014:

edx_dash

 

EdX was founded by MIT and Harvard, and was open sourced in June, 2013. The code can be found on GitHub, forks come from all over the world, including Stanford and Google. You, too, can download the code, but I’m warning you – you’re in for a difficult time.

For the purpose of getting the production stack first I tried with a Virtual Machine (edX feels most comfortable on Ubuntu), but the box was killing my memory and eventually I just snatched up an idle laptop, and put a fresh copy of Ubuntu 12.04 on it. There are decent instructions on GitHub to setup the environment and install the software, and yet it took me two weeks to get this thing to work.

I wish I could explain you what happened in that two weeks. Install ruby, install this, install that, start the edX installation process, run into an error, try again, fail better. I have never seen such a complicated installation before. Constant errors, luckily google was my friend every time, and someone had a solution to any of the errors I encountered.

Also, here is one more thing I didn’t realize, and it’s kind of silly. Hopefully you’ll read this article and not make the same mistake.

Once the installation process ended without errors, with other words: it was done, I somehow didn’t realize that we were done. I thought I have to rehash, or rake something, or run some additional commands. Wrong. Once the production stack’s playbook ran, and you get the green light, you can hit http://localhost:80 immediately and check out your new edX site. No additional steps required. EdX does come out of the box, it just takes a lot of patience to unbox it. A LOT of patience. Don’t give up.

So there you go.

Conclusion: You, too, can install edX, but the process will most likely do magic to your blood pressure.

Can’t save hosts file under Win Server 2008 r2

I’m just saving this for myself for future reference, and sharing the info with you in case you would run into the same issue.

Brian Smith here has the solution for us, let me clean my throat and gesture him to take the podium:

Click on the windows button, then type in “notepad” in the search box, you will see the notepad icon near the top of the window.  Right click it, “run as administrator” then open the file “%windir%\System32\drivers\etc\hosts” then go ahead and edit it.

Thank you for your valuable input, Brian.

Conclusion: Windows’ security is still stupid easy to bypass.

The Good, the Bad and the Ugly

We’re looking into Learning Management Systems (LMS) and having some experience on the matter (see my co-founding venture, GradFly) I happily volunteered for the task.

Based on what I’ve seen so far, here are the best candidates:

  • Opigno. Opigno is a Drupal-based open source solution. Their business model is the usual, you can have the open source platform and go on your merry way, or you can have it hosted and tended to for some charges. So far I could not seen any small print within that business model, truly, if you download the open source version, that is yours. The code is released under GPLv2, which means you can make as much money selling it as you want. Basically, the question is – do you want it to be hosted as a service, or do you want to do the heavy lifting yourself?Opigno UI
    Opigno’s UI looks absolutely sweet. Their website is Foundation for sure, indicating that the people developing Opigno have a strong sense of beauty. Opigno, by deafult, does not ship with Foundation, but fear not: there is a Drupal Foundation module for that. The Opigno distribution contains the Drupal core, so it’s enough to download and setup Opigno, no need to get the separate Drupal core beforehand. The installation will take 2 minutes if you have XAMPP, 30 minutes if you need to install XAMPP as well, and 2 hours if you need to install Opigno, XAMPP, and have to assign new ports for the bundle. Really, aside from my personal port-struggles (Pentaho runs on Tomcat as well, so now there are two tomcat instances on my sandbox) the installation process was a delight. Opigno is a beauty.
  • Moodle. Oh my God, I don’t want to talk about it. Actually the reason I decided to write up this article was Moodle. I’ve been struggling with the installation for 2 hours now, and I needed to take a break.
    Moodle is a well-known player in the arena, my alma mater uses it, too. It is also based on XAMPP, so it shouldn’t be a problem to install, right? I don’t know, my friend. This Moodle is a bad, bad news. I exploded the package in the right directory, created a database, assigned the permissions and modified the config.php file. Two clicks into installation and Moodle throws the first error:

    DDL SQL EXECUTION ERROR
    Debug info: Can’t sync file ‘.\moodle\mdl_user_private_key.frm’ to disk (Errcode: 22 – Invalid argument)
    CREATE TABLE mdl_user_private_key (
    id BIGINT(10) NOT NULL auto_increment,
    script VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ”,
    value VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ”,
    userid BIGINT(10) NOT NULL,
    instance BIGINT(10),
    iprestriction VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
    validuntil BIGINT(10),
    timecreated BIGINT(10),
    CONSTRAINT PRIMARY KEY (id)
    ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 DEFAULT COLLATE = utf8_unicode_ci
    Error code: ddlexecuteerror
    Stack trace:

    • line 449 of \lib\dml\moodle_database.php: ddl_change_structure_exception thrown
    • line 833 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end()
    • line 88 of \lib\ddl\database_manager.php: call to mysqli_native_moodle_database->change_database_structure()
    • line 77 of \lib\ddl\database_manager.php: call to database_manager->execute_sql()
    • line 417 of \lib\ddl\database_manager.php: call to database_manager->execute_sql_arr()
    • line 369 of \lib\ddl\database_manager.php: call to database_manager->install_from_xmldb_structure()
    • line 1486 of \lib\upgradelib.php: call to database_manager->install_from_xmldb_file()
    • line 237 of \admin\index.php: call to install_core()

    Disgusting. I check the database – there are 109 new tables sitting there. So it’s not a permissions issue. I drop and recreate the database, restart the install: another SQL error comes up.  Quick google, turns out the error happens quite a few times on Windows boxes. Congratulations. This is an entirely different experience, conmpared to yesterday’s Opigno.

I gotta go now, and finish this Moodle install. I’ll let you know if I find a solution to the DDL execution error.

UPDATE: I will be posting every attempt’s error message for your entertainment.

Debug info: MySQL server has gone away

I don’t know what this little prick is trying to do, but we’ve now reached the points where MySQL just quits. I do not wish to express my opinion on the matter.

16:34: Debug info: The table ‘mdl_course’ is full

I haven’t even created the course table yet.

16:38: Debug info: MySQL server has gone away

16:39: Debug info: Debug info: Table ‘mdl_config’ already exists

Haaaa, tricked you! Didn’t drop the database before this round.

16:44: Debug info: Can’t sync file ‘.\moodle\#sql-1230_bd.frm’ to disk (Errcode: 22 – Invalid argument)

That’s new…

16:49: Debug info: Can’t sync file ‘.\moodle\#sql-1230_bd.frm’ to disk (Errcode: 22 – Invalid argument)

17:00: Debug info: Debug info: The table ‘mood_course_log’ is full

17:02: Debug info: Debug info: The table ‘mood_course_completion_criteria’ is full

17:11: Debug info: Debug info: The table ‘mood_course_completion_criteria’ is full

See how consistent the error messages are? In the meantime all I do is change table prefixes, drop databases, and change permission parameters (switching back and forth between ‘everyone can execute here’ to ‘only I can execute here’).

17:18: Debug info: The table ‘mdl_course’ is full

17:25: Debug info: MySQL server has gone away

MySQL gave up. Excellent idea, I call it a day myself.

Conclusion: Drupal – sí, sí, Moodle – no, no. Drupal – sí, sí, Moodle – no, no.

UPDATE: Here is the second part of the story, where I describe ‘The Ugly’ of the trio.

SQL Server – move database files

By definition SQL Server uses the install directory to store its data, unless you’ve taken care of that aspect during installation. The default directory is

C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL\DATA

Initially, that solution is absolutely fine, until you realize that SQL Server and its rapidly expanding databases are slowly eating up the disk space dedicated for your OS. That’s when you want to move your databases to another location. Luckily, that’s a rather easy procedure, easier than moving your iTunes database. (Which is a complete pain. Take note, Apple.)

The steps:

1. In SSMS, run the following:

USE MASTER;
GO
ALTER DATABASE MyAwesomeDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
EXEC MASTER.dbo.sp_detach_db @dbname = N’MyAwesomeDatabase’
GO

That will detach your database from the files.

2. Move the database files from their current location to their new home. You’ll want to copy both files associated with the database, in our case the two files are:

MyAwesomeDatabase.mdf
MyAwesomeDatabaselog.ldf

3. To relink the files with your database, run the following:

CREATE DATABASE [CRM] ON
( FILENAME = N’D:\Data\MyAwesomeDatabase.mdf.mdf’ ),
( FILENAME = N’D:\Data\MyAwesomeDatabase.mdf.ldf’ )
FOR ATTACH
GO

4. Ta-dah! That’s it.

ps – the Internetz is a redundant space. There are two tutorials on this topic, one on Technet, the other one on SQLAuthority. As one would expect, the Microsoft tutorial is absolutely confusing and impossible to follow. The latter on the other hand is an excellent tutorial, as matter of fact you should spend some time reading some of the articles you find there. Full of good stuff.

Conclusion: Now you know how to move your database files.

When I FINALLY deploy the package

And it runs, and it’s bug free, and it succeeds, and it schedules, and it runs again, so with other words:

IT’S DONE.