I appreciate the help, actually, as I know no actual SQL. The last time I had to deal with databases, it was abstracted through Rails, which built the queries for me.
So, the GUI we use has two layers of abstraction on top of the possibility of raw SQL. The highest abstraction, called "Normal Search", presents a series of drop-down menus and tabs for refinement. So we'd select that we wanted bibliographic records, we're searching by title, and then we'd out in the title and search. (The defaults are for all locations and all collections, but we can specify which ones we want of both in options tabs (and using the Web-based PAC, as well). So it probably builds something like "select * from BibRecords where title == 'foo'", and that returns a (hash, probably) of bib records that will be iterated over and displayed.
We can also do it to Item Records by title, which returns the items that match directly, and again, the interface iterates over the items and displays selected attributes of them.
I can't set up a query in normal mode and then switch to SQL mode to see what the equivalent query would be, unfortunately.
I can, however, set it up in Normal mode and then remove a layer of abstraction into Power Search mode, which uses a Common...something Language to construct the query (TI="foo", AB=20, CO={45, 46,35}), which isn't all that helpful for divining the underlying SQL, either.
Both Normal mode and Power mode, though, force the choice of either Bib or Item records to be used. To check on circulation status, you have to use item records. So we could, I suppose, construct a comparison that would iterate over our collection and compare it to the item collections in every other branch, but when the system itself carries 1 million plus item records, many of which have the same titles, we're looking for efficiencies if we can get them. And it's not like I can point the gui at the backup server to run it so as not to bog production down.
So I guess it would be something like "select * from ItemRecords where circstatus == "In" AND (branch1 has item matching item.title and branch2 does not)
But I don't actually know the table names, keys, or values that I would have to be going after, and as you can see, the SQL for "is here but not there", I have no clue about.
The other SQL I need is one where I say "select * from ItemRecords where item.firstavailable < "date" AND branch == "mine" AND collection == "any of these collections related to an age group and/or fiction/nonfiction" AND (item.circ.ytd <= value OR item.circ.lastyear <= value OR ((item.circ.ytd + item.circ.lastyear) <= other-value)
...which is basically "find stuff that isn't circulating well enough and is old enough to be potentially discarded."
no subject
So, the GUI we use has two layers of abstraction on top of the possibility of raw SQL. The highest abstraction, called "Normal Search", presents a series of drop-down menus and tabs for refinement. So we'd select that we wanted bibliographic records, we're searching by title, and then we'd out in the title and search. (The defaults are for all locations and all collections, but we can specify which ones we want of both in options tabs (and using the Web-based PAC, as well). So it probably builds something like "select * from BibRecords where title == 'foo'", and that returns a (hash, probably) of bib records that will be iterated over and displayed.
We can also do it to Item Records by title, which returns the items that match directly, and again, the interface iterates over the items and displays selected attributes of them.
I can't set up a query in normal mode and then switch to SQL mode to see what the equivalent query would be, unfortunately.
I can, however, set it up in Normal mode and then remove a layer of abstraction into Power Search mode, which uses a Common...something Language to construct the query (TI="foo", AB=20, CO={45, 46,35}), which isn't all that helpful for divining the underlying SQL, either.
Both Normal mode and Power mode, though, force the choice of either Bib or Item records to be used. To check on circulation status, you have to use item records. So we could, I suppose, construct a comparison that would iterate over our collection and compare it to the item collections in every other branch, but when the system itself carries 1 million plus item records, many of which have the same titles, we're looking for efficiencies if we can get them. And it's not like I can point the gui at the backup server to run it so as not to bog production down.
So I guess it would be something like "select * from ItemRecords where circstatus == "In" AND (branch1 has item matching item.title and branch2 does not)
But I don't actually know the table names, keys, or values that I would have to be going after, and as you can see, the SQL for "is here but not there", I have no clue about.
The other SQL I need is one where I say "select * from ItemRecords where item.firstavailable < "date" AND branch == "mine" AND collection == "any of these collections related to an age group and/or fiction/nonfiction" AND (item.circ.ytd <= value OR item.circ.lastyear <= value OR ((item.circ.ytd + item.circ.lastyear) <= other-value)
...which is basically "find stuff that isn't circulating well enough and is old enough to be potentially discarded."