Category: Uncategorized

There are a million blog posts on this sort of thing but it just bit us so I thought I’d add mine to the mix.

Date calculations can be notoriously difficult depending on the language you are using.  However, DO NOT succumb to the desire to "fake" it as our vendor did.  Here’s what happened:

In California every school has to submit data for their special ed students/programs to receive proper funding.  This is called a CASEMIS report, and reporting day is every June 30th and December 1st.

For CASEMIS if a child is under 6 they are in one category and if 6 and over in another.

In our data there is one student in question who’s date of birth is 12/3/2002.  So on the reporting date of 12/1/2008 this student will be just two days shy of 6 years old.

We were getting an error in our validation process because apparently our vendor was calculating ages using 1 year = 365 days.  12/1/2008 – 12/3/2002 yields 2190 days, which at 365 days a year results in 6 years exactly.

Ugh!  Sorry, but the "close enough for government work" doesn’t apply here.

I don’t know exactly what language they are using but I believe they use perl on the back end and Business Objects InfoView XI for the reporting.  The actual date calculation could be in either of these two places, or somewhere entirely different.

While some languages make calculating proper dates difficult, there is almost always a correct way to do it.

For instance, in Microsoft T-SQL, asking the difference in years between 12/1/2008 and 12/3/2002 using:
SELECT DateDiff(yy, ’12/3/2002′, ’12/1/2008′)
incorrectly returns 6 years, since 2008 – 2002 = 6.

However, if you change the question slightly and ask if the birthdate + 6 years is less than the reporting date using:
SELECT CASE WHEN DateAdd(yy, 6, ’12/3/2002′) < ’12/1/2008′ THEN ‘6 or Over’ ELSE ‘Under 6’ END
this results in the correct response “Under 6”.

It’s a matter of finding the proper question and syntax for the language.

In an ideal world you would design your applications so that your users could never enter bad data.  Well, that’s not always possible for some technical reasons and for 3rd party apps you’re at the mercy of the vendor.

 

One case in point is our Student Information System (SIS) at the Val Verde Unified School District.  It does a great job and we love it, but as a developer it does have its cons.  The database isn’t as normalized as I would prefer and sometimes their data model just seems weird.  Granted it is a Microsoft Access UI that links to a SQL back end, however, it still doesn’t change the way things are.

 

In this case we need to perform audits on our data.  Anyone who is in data management knows that if it’s possible to enter an invalid value (such as a 5 digit phone number) some user will do it someday.  In some cases bad data is rare and other cases just plain prevalent.  The other issue is how that impacts your organization.  Sometimes the invalid data will just be informational and not a big impact, however, in other cases this can impact reporting, automated processes, data exports/imports and many other systems.

 

So, I came up with our current data auditing system.  We started it up back in June, 2006 and it’s been a great help.  We currently have 60 checks on our SIS that run various times of the day.  Each check is really just a simple SQL statement that looks for errors and stores these in a violations table.  After each job a report with the results is sent to the relevant users.  Typically we have only a few jobs, most of which run at night and by the morning staff have the latest error report in their email. 

 

This has been a real labor saver as data entry errors are caught within 24 hours.  Those who are responsible for the data usually have the information fresh in their minds or the reference paperwork readily at hand.  We have to send various reports to the state several times in the year.  In the past, before we had audits, many of the reported fields were not actively maintained and we would get thousands of errors that needed to be corrected before we submitted our reports.  Now, the errors are corrected by the next day.  When it comes time to submit the reports it’s a much faster and less stressful process.

 

Currently our audit system is in a very 1.0 stage.  It only runs on our SIS data since that is what it was originally created for.  Also, it’s entire maintained in SQL by our database managers (3 of us).  There is no UI at all.  It works, and is great for a 1.0 venture if I am allowed to say so.  But it’s definitely time for a 2.0 version.

 

Here are just a few features I’m looking to put into the 2.0 version:

• Easy to use web UI
• Make use of ASP.Net security and roles
• Able to audit ANY data through various data providers:
• SQL
• Active Directory
• Exchange
• File System
• IIS logs
• Etc.
• Better reporting system
• Easily setup generic audits on fields such as:
• Non-empty
• Alpha
• Numeric
• Real-time audits (such as using triggers in SQL)

 

I’m thinking about putting this on CodePlex once I have the basic framework up and running.  I have no ETA on this project since it’s rare that we get a lot of “free” time to work on things like this.

 

Back in the good old days, when MS Office simply was Word, Excel, PowerPoint and Access, we all knew what Office was and why we needed it.  It was similar with other market leading programs such as WordPerfect and Lotus 123, Photoshop, QuickBooks or PeachTree, etc.

By my list of software above you may be trying to figure out what I mean by "way back when".  Let’s pinpoint this time period as cerca 1995.

The world was great.  Well, not great, but it was less confusing. :)  For the general consumer there was only a few operating systems to choose from, one or two office packages, etc.

Back then we knew we needed Microsoft Word or Word Perfect.  We knew we needed Excel or Lotus 123.  If we wanted to know how to use it we bought a book, took a class, or (gasp!) read the manual.  Yeah, manuals were actual paper books bundled with the software back then too.  This meant that companies could charge for in-depth books and training programs for their software.  It was the "if you build it people will pay to learn how to use it" era.

Now come back to the present.  It’s a lot more complicated.  Do I use Office Home and Student, Standard, Small Business, Professional or Ultimate?  What about this Open Office thing I keep hearing about?  What in the world is Groove, InfoPath, OneNote and why do I care?  People keep telling me that OneNote changed their entire work process.  How?

Notice I’m just sticking to the MS family (except for the Open Office reference I threw in there).  It seems like with every new version Microsoft throws a new product or two into the Office family.  If I were to list all the non-MS alternatives this post would be a mile long.

So, what’s Microsoft’s answer?  As you probably have noticed much of the Office website (and really any major Microsoft product site these days) is mostly devoted to helping you understand what each product does and how to use it.  It’s still pretty confusing, but there is a wealth of how-to articles, videos, webinars, trial demos and sometimes even online interactive demos.  All free.

It’s not like the world woke up and suddenly Microsoft realized they needed to convince us why to buy their product.  That has always been the case.  It’s just now they are freely telling us how to actually use it.  That’s great. 

This is definitely more of an industry trend.  Most software companies offer free product demos, videos and training on their website.  In fact, if a new product can’t show me in a 5 minute video why I would want to use their software I tend to move on.

It’s nice to be able to spend a half an hour in the morning beefing up on a new product.  I’m trying to set this as a weekly (perhaps daily) habit.  Currently I’m a OneNote training junkie.  🙂