Two Nine Media :: The Personal Studio of Joe Gautreau - SQL

SQL - Update Query Using Sub Queries Update Value

Mon, 23 Mar 2009 16:00:00 -0400

I like to post anything SQL that is "new" to me that perhaps I didn't know before or don't remember doing. Today was one such occasion. I was challenged with the task of updating a table full of reading data for for our district's 3rd-5th graders. The data was comprised of a record per student per testing cycle, and was used for the purpose of tracking reading fluency. I have a second table which holds a score matrix letting me know per grade, per testing cycle what the low and high grades should be to determine if a student is above average, below average or at average. This was something that I had to do as a "one time event" to about 7000 plus records. I decided that I could run a bunch of queries per reading cycle per grade to do this, but that seemed cheesy to me. I wanted to do it all in one fell swoop. Here is what worked for me.


UPDATE dataTable
SET dataTable.atAboveBelow =
CASE
WHEN dataTable.readingScore < (SELECT belowAvg FROM scoreMatrix where gradeLevel = dataTable.gradeLevel AND testWindow = dataTable.testWindow) THEN -1
WHEN dataTable.readingScore > (SELECT aboveAvg FROM scoreMatrix where gradeLevel = dataTable.gradeLevel AND testWindow = dataTable.testWindow) THEN 1
ELSE 0
END

SQL 101 - Counts By Location and Grade

Wed, 09 Jul 2008 12:35:00 -0400

The Desired Result

The other day at work there was a need for a report that our reporting system was having trouble putting out. So the fix was to write it ourselves. It's not too complex and for some it may be quite elementary, but here it is none-the-less. Being that I work for a school district there was a need for a report by campus and grade level of the number of students that fell into a certain category. So for the sake of this report we want a count of all active students based on their campus and their grade level. Below is the stripped down version of how this was accomplished.


SELECT Campus, Grade, COUNT(studentID) AS StudentCount
FROM ourStudentTable
WHERE studentStatus = 'A'
GROUP BY Campus, Grade
ORDER BY Campus

Hopefully this helps!

SQL - Using Sub Queries As Joining Tables

Tue, 01 Jul 2008 16:45:00 -0400

Today at work I faced a dilemma. I was tasked with developing a function in which a parent could be notified when their child has an absence or a tardy in one of their classes (I work for a school district). It involved touching 3 different tables including one twice. The three types of tables are as follows, an attendance details table which we'll call AttendanceDetail, a parent/student table which ties parents with students which we'll call ParentStudent, and finally a notify parent when an absence occurs table which we'll call AttendanceNotification. So here is the short of how the process works. Daily a routine will run checking to see if a student whose parent requested a notification upon an attendance event had missed a class and/or was tardy to class. Sounds simple enough huh? Well it wasn't as easy as first thought. My first query was as follows...


SELECT an.studentID, ps.studentFirstName, ps.studentLastName, ad.absence_date
FROM AttendanceNotification an
INNER JOIN ParentStudent ps ON ps.studentID = an.studentID
INNER JOIN AttendanceDetail ad ON ad.studentID = an.studentID
WHERE an.notify = 1 AND ps.status = 'A'

Basically this query grabs all prior absence events for a student where they are active and the parent needs to be notified of the last absence event. So we have a bunch of unwanted events here, we just need the most recent event. Simple enough we'll just add a couple things to our query and we'll be good to go right? Not so fast. So I added a MAX() function to the absence date which would give me the most recent absence and I grouped by the studentID which would only give me one record back per student and I ended up with the following.


SELECT an.studentID, ps.studentFirstName, ps.studentLastName, MAX(ad.absence_date) AS absenceDate
FROM AttendanceNotification an
INNER JOIN ParentStudent ps ON ps.studentID = an.studentID
INNER JOIN AttendanceDetail ad ON ad.studentID = an.studentID
WHERE an.notify = 1 AND ps.status = 'A'
GROUP BY an.studentID, ps.studentFirstname, ps.studentLastname

Well, the problems began to start, I needed to be able to show the parent what the absence event detail was. Did their student have a tardy? An unexcused absence? A doctors appointment? You get the point. My first thought was to just add it into the SELECT statement for the above query, but then I had to include them in my GROUP BY clause which caused more than one record per student to be returned. I then thought about trying to wrap them in a MAX function, that didn't work, they needed an aggregate function to be included in my SELECT criteria and not in my GROUP BY, I was at a loss. Then I got an idea, I wondered if I could JOIN on a sub query. I had never tried it before, but it worked perfectly! I thought I'd share it with you just in case you ever ran across a similar problem. The final query does an INNER JOIN from the AttemdanceDetail table to the previous result set or sub query. Now we're able to get detailed absence info yet only the most recent record. Here is the final query.


SELECT
	a.studentID,
	a.studentFirstName,
	a.studentLastName,
	a.absenceDate,
	CASE WHEN ltrim(rtrim(adII.am_office_abs)) IS NULL THEN ltrim(rtrim(adII.am_teacher_abs)) ELSE ltrim(rtrim(adII.am_office_abs)) END AS am,
	CASE WHEN ltrim(rtrim(adII.pm_office_abs)) IS NULL THEN ltrim(rtrim(adII.pm_teacher_abs)) ELSE ltrim(rtrim(adII.pm_office_abs)) END AS pm
FROM AttendanceDetail adII
INNER JOIN
(SELECT an.studentID, ps.studentFirstName, ps.studentLastName, MAX(ad.absence_date) AS absenceDate
FROM AttendanceNotification an
INNER JOIN ParentStudent ps ON ps.studentID = an.studentID
INNER JOIN AttendanceDetail ad ON ad.studentID = an.studentID
WHERE an.notify = 1 AND ps.status = 'A'
GROUP BY an.studentID, ps.studentFirstname, ps.studentLastname) a
ON a.studentID = adII.studentID AND a.absenceDate = adII.absence_date

Perhaps there is a better way to do this, I just don't know how, but this worked so I ran with it.

CFUnited: My Recap

Tue, 24 Jun 2008 11:35:00 -0400

CFUnited 2008 was a great experience for me personally, I presented as a speaker for the first time which was a great experience and challenge, as well as learned a great deal of things that I would like to put into practice in my own development environment. I wanted to put a quick post out there to go over some of the highlights that I was able to take away learning wise for others to see as well as a constant reminder to myself. So, what did I personally learn?

In his own words, Ray Camden will be using tables for layout until someone stops him... Should've come to my session Ray!
I want to start using CFToolTip more for my end-users benefit.
There is a cool Event Validator at RiaForge that Mark Drew referenced, I would like to check into that when I get the time.
From Hal Helms session, leave functions open for extention, closed for changes. Keeping things simple
Just because MVC is used, doesn't mean I'm doing OO. I can still be doing procedural code in an MVC format.
Get book called Design Patterns by Gang of Four
Find the things that are constantly changing in my applications and encapsulate them.
Good OO is all about abstraction.
Pass objects to be persisted.
From Chris Scott's session, keep ColdSpring simple - getBeans/setBeans
Increase the JVM default on the CF Server.
Increase the minimum memory on the CF Server.
Turn on trusted cache in production.
Cache queries used often in applications.
Read the CF8 performance brief.
Use SQL Server performance monitoring tools to profile, trace, look at execution plans and their costs, run database reports to see which queries are putting loads on the server.
If session is not available, reject the form submission, because it means they didn't come from your form.
From Joe's advanced Model-Glue session... you can use the include tags to include entire applications into your Model-Glue app.
Prefix event handlers.
Controllers should be as thin as possible.
Use views for common joins, filtering, etc... when dealing with larger queries.
Index all fields searched on in my SQL tables for fast searching and less load on the database. A good example of this is looking for pizza in the phone book, but searching every page of the book from A-Z. With indexing you don't need to search from A-Z simply jump to 'Pizza'.
SQL views can be about 75% faster than CF for complex queries.
Stored procedures can be about 90% faster than CF.
Use cfQueryParams for security as well as not making the SQL Server re-write execution plans everytime a query is run.
Be careful about how you index your tables, you could do more damage than good if it's not done right.

That was my quick recap of what I learned. Overall the venue was great, I loved the fact that the conference was in downtown DC instead of Bethesda, the hotel was awesome and close to the conference. Another great job by the CFUnited team. I hope to return in 2009.

SQL Dynamic Date Range

Fri, 01 Feb 2008 18:31:00 -0400

I work for a school district here in Texas and today an issue arose. We needed to run a nightly SQL job which pulls students from our Student Information System and dumps them into some tables we use for a local application. Problem is while we have about 22,000 active students our total student table has over 55,000 records in it, some from prior years. The initial reaction was to hard-code the school year start and end dates into the system, but then if our DBA leaves, or we forget what kind of lame hack we put in place we have problems next school year. I had done this previously in ColdFusion, but today I decided to do a SQL version of it. It will take the current day and decides what the current school year start and end dates are based on the current day, so we're always pulling current data only no matter what year it may be.


DECLARE @startDate datetime
DECLARE @endDate datetime

SET @startDate = cast('7/1/' + cast(datePart("yyyy",getDate()) AS varchar) AS datetime)
SET @endDate = cast('6/30/' + cast(datePart("yyyy",getDate()) AS varchar) AS datetime)

SET @startDate = CASE WHEN cast(@startDate AS datetime) > getDate() THEN cast(dateAdd("yyyy",-1,@startDate) AS datetime) ELSE cast(@startDate AS datetime) END
SET @endDate = CASE WHEN cast(@endDate AS datetime) < getDate() THEN cast(dateAdd("yyyy",1,@endDate) AS datetime) ELSE cast(@endDate AS datetime) END

SELECT @startDate AS startDate, @endDate AS endDate

The end result is 7/1/2007 as the startDate and 6/30/2008 for the endDate. It works perfect for us, and I hope it works for you.

Web Development - How do you approach it?

Thu, 15 Nov 2007 17:49:00 -0400

I work for a school district, and I am the first developer they have every had. So I've been developing web apps for them for about six months now using ColdFusion with Model-Glue and SQL, all authentication is done through Active Directory.

Lately I started thinking about the future and how to best do development. When I was hired they had a list of projects they needed done, and they needed to be done yesterday. So I began coding, coding, coding. No design docs, not project scope docs, no database diagrams, nothing of the sort.

My first thought was to slow down and start a process for each project thrown my way. It may be more tedious, time-consuming and boring, but I really think long-term, a well laid out play with an approval process up front lends itself to successful projects.

I am a big fan of the Interface-Driven approach to web development, but I wanted to get some feedback from the community...

What methods/steps do you employ when taking on a project?
Do you build out pieces and release them or wait till the project is complete?
What type of documentation is used and why?
What have you found most beneficial to you in your experience with this?

Thanks in advance for your input!

Calculating Age In SQL

Tue, 16 Oct 2007 11:53:00 -0400

So, I was building an application at work that required me to know the age of an individual before further processing could be done on the record. I thought this would be a piece of cake with the "dateDiff(yy,date1,date2)" function. That only gives the year difference. For instance if today's date is 10/16/2007 and you we're born on 10/17/1981, the dateDiff function would tell you that you're 26 years old when in fact you don't turn 26 till tomorrow. This became problematic for me because I needed to know to the day how old this individual was at any given moment. After a bit of browsing and small modifications on my end here is what I ended up using.


SELECT DATEDIFF(YY, '10/15/1981', getDate()) - CASE WHEN( (MONTH('10/15/1981')*100 + DAY('10/15/1981')) > (MONTH(getDate())*100 + DAY(getDate())) ) THEN 1 ELSE 0 END as Age

What the above SQL statement does is first grab the year difference. Then the CASE statement takes the month multiplied by 100 and adds the day of the date to that total. It also does the same thing for the second date passed in. It then evaluates the two and either leaves the year alone or subtracts one from it. For this example I hard-coded the date of "10/15/1981" into the statement, but in my real-life example that became a sub-query based on an ID. Hope you find this as useful as I did.

ColdFusion: Stored Procedures For Beginners

Fri, 18 May 2007 18:24:00 -0400

So, you are a beginner in ColdFusion and you want to know the quick and dirty way to build a stored procedure and call it in ColdFusion. Step One: Open SQL Server Enterprise Manager and browse to your database, you will find a section called "Stored Procedures", right click on it and select "New Stored Procedure". Step Two: A window "Stored Procedure Properties" window will open where you will drop in your t-SQL code for running a query. Save it and you are good to go. Below you'll find the window and example code.


CREATE PROCEDURE dbo.getStuff
@firstname varchar(50)
AS

SELECT dbo.[User].firstName AS Firstname, dbo.[User].lastName AS Lastname, dbo.[User].email AS EmailAddress, dbo.Role.name AS Role,dbo.Organization.Name AS OrgName, dbo.Permission.name AS PermissionName
FROM dbo.RolePermission INNER LOOP JOIN
	dbo.UserOrganization INNER LOOP JOIN
        dbo.[User] ON dbo.UserOrganization.UserID = dbo.[User].userID INNER LOOP JOIN
        dbo.UserOrganizationRole ON dbo.UserOrganization.UserOrganizationID = dbo.UserOrganizationRole.userorganizationid INNER LOOP JOIN
        dbo.Role ON dbo.UserOrganizationRole.roleid = dbo.Role.roleId ON dbo.RolePermission.roleid = dbo.Role.roleId INNER LOOP JOIN
        dbo.Organization ON dbo.UserOrganization.OrganizationID = dbo.Organization.OrganizationID INNER  JOIN
        dbo.Permission ON dbo.RolePermission.permissionid = dbo.Permission.permissionID
WHERE dbo.[User].firstName = @firstname
GO

Step Three: Open up a ColdFusion document and drop the following code in to call the stored procedure. You'll need the datasource to the database where the strored procedure lives, the stored procedure name, and a name you will call your result, also pass in any parameters that the stored procedure needs.

Dump your stored procedure result.

And just like that, you've got results. A stored procedure is really quite simple and very useful. Why use stored procedures? Many times, depending on what you are doing a stored procedure is much quicker. Everytime you write a query in ColdFusion, the SQL server takes it and says... "What would be the most efficient way to run this query?" It then runs it. Whereas with a stored procedure, the "Execution Plan" of how to run the query most efficiently is stored in the system so the next time you call the stored procedure, it doesn't have to evaluate and come up with an "Execution Plan." The main reason for using them is speed. You'll have to evaluate for yourself based on your situation at hand, what is quicker for the task at hand. And no, I don't recommend using stored procedures for every little query, but for some of the bigger more complex ones... WHY NOT??? NOTE: Microsoft SQL and Sybase among others use Transact SQL as the language to write a stored procedure. It is extremely robust and evolved and it may behoove you to learn some of the functionality available to you to perform more advanced operations (branching logic, looping over results, etc...). An excellent reference book on this subject can be found here.