Tuesday, June 26, 2012

I've been working for a while on a large data set for a future story, and I created a query that I thought different enough to share.

sum, iif and datepart

I have a series of records that have a date in mm/dd/yyyy format. I wanted to know how many records there were for each month of a 3-year span, so I wanted to query and group by year, but I wanted a count of the records by month. I wanted it to look like this:

I used the MS Access iif function to count and sum a record if it met a certain condition, but to not count it if it didn't. In this case I was checking if it was the proper month for that column. This is the iif function:

iif(test,value_if_true,value_if_false)

Wrap that in a sum, and you count the record only if the value is true:

The ""iif" is the cool part, but it's the "datepart" function that makes it powerful in this instance, as I can test if the month is true. I test if "m" (or month) equals "1" (or January.) If so, make the value 1 so it will be summed in that column. Otherwise, the value is zero and it is not added to the sum.

A big thanks to Boyd Hemphill of that GeekAustin class for that bit about summing the dates. It's gratifying to actually use code out of a class like that.

When we started with the analysis, we originally looked at ZIP codes that were mostly in the city boundary, using data from the Texas Water Development Board's Submitted Driller's Reports Database. But as Dave turned the story more about how folks were using these wells to bypass water restrictions, the more it made sense to focus instead on the areas that the Austin Water utility serves. I put in a public information request to the city to get the GIS shapefiles of the water service area, and I was able to us that to search by location for all the wells in just the service area (using Lat/Long from the Driller's Reports).