For starting with: my title sucks, so help me figure out a new one?
I can't post all the SQL here (over 30k characters for the lot of it), so I stuck it on pastebin.com
I get an XML file that I scrape some records from, and I need to extract some data from the records and build another table off it. The records are for an event going off and coming on, and I've included sample data in the pastebin for recreating. Without seeing the data it's kind of hard to explain. I've given all the data I have from my sample import which should be sufficient to build the app off of, but I'm not getting any more information than what is shown in the data.
I'll give you a moment to glance at the data so this makes sense.
So what I need to do is this: For each "off" event, I need to match it to the next "on" event, and I need to have two tables at the end, one table for "historical events" and one table for "current events". However, if I can just get "historical events" built correctly I can figure out how to get "current events" from that.
If two or more "off" events are collected before an "on" event, keep the oldest "off" event. If two or more "on" events are collected before an "off" event, keep the newest "on" event. If there is a complete pair, put them in the historical table. If there is an "off" event and not an "on" event, put it in the current table (so if I wanted to keep inserting/deleting from this table, that's fine too). If there is an "off" event already in the current table I can take it and move it to the historical table for an "on" event being read in (this will need to be implemented later, but if I can get the pairings matched initially I'll be able to go forward for now.
I think that's pretty much it for the logic. My thoughts are to either figure out how to do this in SQL or push it out to an app written in C# and do it with some temporary lists in C# and build what I need using for...next logic. This may be infinitely easier in C#, but I have a feeling SQL can do this job just as easily as C# can, so I needed some help from the dba gurus.
The queries I have already don't work, but that's where I got to with my starting before going home on Friday, and I've been mulling it since then, and building a sample problem that I can post online (and that whole life thing too ya know). The data is live data and exact, except the ID's being anonymized and the text fields changed to something simple to work with.
Here's a spreadsheet showing roughly how I want the data to look at the end and the way it looks now. There is the current data (with a spacer row between each ID for clarity), the data that would be in the historical table (aligned with the ID of the original data for understanding) and the current table (again aligned). I hope this can help clarify the business rules. https://spreadsheets.google.com/ccc?key=0AuvCdeHuVU5ddHRCNkpuWHBUREpRajlmLU5VX2xsWnc&hl=en&authkey=COq7y50H
So the complete SQL including tabledefs and current (very not-right) queries is on pastebin http://pastebin.com/k2f2CLnQ