Before we get to the SQL, I need to deviate to Churchy stuff for a moment.
In my church, the members of the congregation are allowed to participate in the inner functions of the local church. We get the opportunity of accepting a 'calling', and performing some tasks. This opportunity lets you interact with other members of the congregation, volunteer your time and talents, and assist in the church. Depending on the 'calling' you may have, you may spend a little time or more time. In the past, I have worked with the Cub Scouts, have taught Sunday School lessons, have been on the party planning committee, and so on. My current calling is different from all these, and has me more closely working with the week to week duties of keeping the church working. We meet each Sunday for worship services. At one of the meetings, there is an opening prayer and a closing prayer. I am in charge of seeking out the members of the congregation and getting them to pray in these two prayers each Sunday. Not a huge task. But over time, you find some interesting things. People remember the last time they prayed, and if they have any qualms about doing it again (nervous, shy, etc) they will typically recall the last time they prayed as having been more recent than reality, regardless of when this prayer actually occurred. Other people will never volunteer to say a prayer, and I need to remember to not ask them. Other people will always say yes, and would end up praying each and every week asked. I need to refrain from asking them too often.
So, now you understand a little about what I am doing. To keep track of who has prayed, and try to not repeat too frequently, as well as avoid asking some that can't/won't, I started keeping a simple list. We'll call this DB 1.0. It was on my palm pilot, and each week I would write down the name of the person that performed each prayer, next to the date. I kept doing this for sometime, and as time marched on, I created quite a list. I could refer to this list fairly easily in the first few months, and see who had prayed recently or not. I was even able to tell some folks that recalled praying recently, that in fact it was a bit longer than they had remembered, and could show them the actual date on my list. My little list grew in fame with some folks.
This all started in July of 2007. Fast forward to today. I have a record of every prayer (both opening and closing) since that time. All in a note on my phone now (having retired the palmpilot from daily use).
A couple weeks ago, I finally imported this data into a database. I have a table of prayers. I also have a table of members. After some scrubbing, I was able to link the names in my note to the IDs in the members table. I added another table to indicate if they could pray or not. Also added a field to the members table indicating if they are enabled or not. Since people have moved in and out of the congregation, I need to keep them as a member, but make them disabled if no longer present. From here, I was able to create some simple views, and procs. I got tired of looking up the ID to assign a prayer, so made a proc to AddPrayer. This will look for the name in the members table, and use the ID it finds. If it cannot find it, it'll add it to the members table. Now, I can quickly add new prayers each week. (keep in mind that I still keep the list on my phone, and add to it.)
Some fascinating data was able to be pulled form this database. I could generate a list of the people that had prayed most recently. By getting the max date, and sorting by that, along with the count of prayers they had performed, I could have a list of those I had picked on most recently. I could reverse this list, and show those folks that have not prayed recently, or at all. This allows me to more easily spread out the load to those folks I do not normally contact. Since so much of the church and membership is a social animal, I find myself in a certain few circles, spinning around, and not including the entire membership. This way, the DB can suggest some names of folks I would not normally think of.
Stepping away from SQL for a momment... So, those of you that are religious may be asking yourself if applying stored procedures and data mining to choosing a prayer is the proper way. To get religious on you, I do rely on being guided by the Holy Ghost as to whom I select, hoping that my selection will fit into some great plan that I am not privy too. This has happened on more than one occasion, and the individual I selected was 'supposed' to pray that day, for some reason, and I was merely the instrument in that action here on Earth. This has been an incredible experience, when it happens. But it doesn't happen each week. But at the same time, how can I say that by my making a database to keep track of all this, and using the tools it provides is not also some aid in the 'Grand Plan'? Besides, its fun to have found a chunk of workable data, with an end goal in mind, and be able to assist my weekly task with this data. Its been great fun in thinking of different ways to view the data, and make a story out of it. It turns out that my wife is not the most asked person to pray. I tend to lean on her if someone else was unable to pray, doesn't show up, and so on. I would have thought she would have had the most prayers. But no, she doesn't. Who does? Well, I know, and can prove it with data.
Back to SQL. This has been an interesting journey to gather this data, 2 records at a time each week. The story it tells will not fascinate most people, but those in my church are interested. Not all, mind you, but some think its a great thing to have captured all this, and be able to report on it.
I challenge you to think of a place in your life where gathering data and storing it would tell an interesting tale to you and yours. Maybe not right away, but as some point in the future, you can look back, apply some of your SQL knowledge to your personal life, and enjoy the results.