Tuesday, June 26, 2012

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:

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

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

Tuesday, June 05, 2012

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.

  © Blogger template 'A Click Apart' by Ourblogtemplates.com 2008

Back to TOP