MS Access SQL tip: sum, iif and datepart

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:

Units By Month

YR	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
2009	89	75	92	72	97	75	112	122	112	93	82	96	1117
2010	113	102	105	116	116	122	149	126	113	109	118	115	1404
2011	144	107	143	149	152	134	142	158	181	139	134	174	1757

Thanks to a GeekAustin class on writing SQL, I had a way.

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:

sum(iif(test,1,0))

So, I use the datepart function to test the record for every month:

SELECT yr,sum(iif(datepart("m",DATE_OCCURRED) = 1,1,0)) AS Jan, sum(iif(datepart("m",DATE_OCCURRED) = 2,1,0)) AS Feb, sum(iif(datepart("m",DATE_OCCURRED) = 3,1,0)) AS Mar, sum(iif(datepart("m",DATE_OCCURRED) = 4,1,0)) AS Apr, sum(iif(datepart("m",DATE_OCCURRED) = 5,1,0)) AS May, sum(iif(datepart("m",DATE_OCCURRED) = 6,1,0)) AS Jun, sum(iif(datepart("m",DATE_OCCURRED) = 7,1,0)) AS Jul, sum(iif(datepart("m",DATE_OCCURRED) = 8,1,0)) AS Aug, sum(iif(datepart("m",DATE_OCCURRED) = 9,1,0)) AS Sep, sum(iif(datepart("m",DATE_OCCURRED) = 10,1,0)) AS Oct, sum(iif(datepart("m",DATE_OCCURRED) =11,1,0)) AS Nov, sum(iif(datepart("m",DATE_OCCURRED) = 12,1,0)) AS [Dec],
count(*) AS Total
FROM data
GROUP BY yr;

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.

Tracking Private Water Wells in Austin

Just a quick post to note my most recent Google Fusion Tables visualization, this time showing water wells drilled in parts of Austin served by the local water utility. I helped reporter Dave Harmon with the research behind his story about residents drilling their own private wells, many so they can irrigate their landscaping without worry of the watering restrictions we face during drought years in Austin.

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).

My basic steps were:

• Download the Driller's Reports Database (in MS Access).
• Query that database to get wells within ZIP codes in Austin (or in Travis County)
• I exported the query and cleaned up the data. I could tell from my early map visualizations that some of the data had incorrect Lat/Longs.
• I used Esri ArcMap to pull in the wells and plot them. I then layed on the Austin Water district and then searched for all the wells within the district, and then exported that result set.
• More cleanup and custom columns, and then loaded that list of wells into Fusion Tables.
• Using shpescape.com, I uploaded Fusion Tables the shapefiles of the Edwards Aquifer (pulled from CAPCOG and then edited) and the Austin Water Utility shapefiles from the city.
• I used the FusionTablesLayer Wizard with some modifications to layer the three shapefiles on top of each other.