Query writing question

[Matt75]

I have a simple database that has 2 main tables... it is for handling the status of files (physical file folders) and there status...i.e. whether they are checked in or checked out.

the main table holds the info regarding the file and the 2nd table handles the activity... i.e. activity (checked in or out) on (date) and to (who).

Is there a way that I can get a query to tell me what all the files that are checked out to a particular person? in other words.... what files that reference a particular person have a activity of "checked out" logged last?

I was trying to write a query to do this using the "SELECT(MAX)" function

in the most brief statement i can think of, what I am trying to accomplish is:

Select the last activity for each file where activity in/out-from/to = [particular person]

filter by activity [checked out]

I have been trying to get my arms around this for a while now, but just cant seem to nail it down.... does anyone have any good ideas here?... thanks in advance... any help would really be appreciated....

- Matthew Montalto

 

[gmmastros]

Can you post some sample data from the 2 tables involved? This does not need to be "real" data.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

It seems like you may get your answer after getting yourself familiar with these 2 blogs:


Including an Aggregated Column's

Related Values
Including an Aggregated Column's Related

Values - Part 2


PluralSight Learning Library

 

[Matt75]

Since the database is not in use yet, there is no actual data... But I can lay out the structure with some sample data very simple.

main table columns = id (PK), file_number, client_name
sample: 1 0001 John Smith

child table columns = id (PK), filetbl_id (FK), inout_date, inout_act,
inout_person

sample: 1 1 09/28/2010 check out
Joe Blow

two very simple tables related by one foreign key... The trouble here with what I am trying to do is that if I were to merely run a query for check out events, I have no way to know if those events were prior to check in events in which they are no longer of interest since I only want to know if files that are checked out but not yet returned....

so if I could query for the last transaction for any file where the inout_tofrom person is referenced and then filter by those results that are the last activity and a check out, then I think I will have met the goal here...

Really I could even live with not being able to filter it down to only check out events... I could live with just being able to choose the last activity of all the files where the inout_tofrom person is the one in question...

thanks for any help you can offer... tearing my hair out here....

- Matthew Montalto

 

[Matt75]

Thanks for the links Markros...was helpful to a degree... problem I have now is that I can pull the last (most current) date a file was checked out... however, that shows up regardless of whether or not that file has been checked in since...... not sure what I can do about that though...


- Matthew Montalto

 

[gmmastros]

Oh man. I was going to post earlier and forgot about it. Anyway....

There *may* be a trick you can use to help write this query. This trick requires that your "process" behaves exactly the way I would expect it to based on my experiences with a library. That is... a book can only be checked out to one person at a time, and that person is the one that will be checking the book back in. If this is the case, we should be able to tell what "books" are checked out by carefully querying your child table. Specifically, if there is an add number of rows for a file_id, inout_person then we know the file was not returned. If there is an even number, then it must have been checked out and returned.

So....

Select filetbl_id
From   ChildTable
Where  inout_person = 'Joe Blow'
Group By filetbl_id
Having Count(1) % 2 = 1

If you want all of the file information too, then you could use a derived table approach using the previous query as the derived table, like this:

Select file_number, client_name
From   MainTable
       Inner Join (
         Select filetbl_id
         From   ChildTable
         Where  inout_person = 'Joe Blow'
         Group By filetbl_id
         Having Count(1) % 2 = 1
         ) As A
         On MainTable.Id = A.filetbl_id

This trick only works if you are diligent with your data. Specifically, each file that is checked out to an individual, must be checked in for that individual regardless of who actually returns it. For example, if my wife checks out a movie from block buster, and I return it, it MUST be logged as though my wife returned it, otherwise I would have an odd number of rows for returning it, and she would have an odd number or rows for renting it in the first place.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Matt75]

I follow your logic... makes very good sense... I have to run out of the office for the rest of the afternoon, but I am going to give this a run through first thing tomorrow morning.... Thanks so much for you help here George....I will get back to you tomorrow to share my results... thanks again...

Matt


- Matthew Montalto

 

[Matt75]

this seems to be a great idea George.... how can I now make this query tell me all the files that have an odd number of entries in the child table and who that last odd entry references as the inout_person?

in other words so that I can run a query to see any files that are checked out (have odd # of entries in the child table) and who they are checked out to?

any ideas?


- Matthew Montalto

 

[gmmastros]

First, let's talk about odd vs. even. There is a really simple check you can do to accomplish this. Most people are familiar with the common math operators like add, subtract, multiply, and divide. There is another (not so common) operator called modulus. The mod operator only works on integers, and it returns the *remainder* of a division.

For example, 7 / 3 = 2 and 1 / 3 or 2 Remainder 1

The mod operator returns the remainder. Written in SQL:

[tt]Select 7 % 3[/tt]

Now, if we use 2 (instead of 3), the remainder must be either 0 or 1. If the remainder is 0, then it must be an even number. If the remainder is 1, then it must be an odd number.

To get the file id for things that are checked out...

Select filetbl_id, inout_person
From ChildTable
Group By filetbl_id, inout_person
Having Count(1) % 2 = 1

To get all the file details...

Select file_number, client_name, A.inout_person
From   MainTable
       Inner Join (
         Select filetbl_id, inout_person
         From   ChildTable
         Group By filetbl_id, inout_person
         Having Count(1) % 2 = 1
         ) As A
         On MainTable.Id = A.filetbl_id

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Matt75]

holy moly.... George I am not even sure I completely understand how this even works... I can see the logic, but what is fuzzy is how this works no matter what the count grows to....

however... I plugged it in to my sql query window and bingo,.. it pulled the one record that has an odd number if in/out records and it indicated the file number from the main table and the person the file went to which is referenced in the child table...

George... utter genius is the only way to describe this.... I am truly in awe....

you certainly know your stuff... thanks so much for the help,... I cant tell you how much I appreciate it... thanks again...


- Matthew Montalto

 

[Matt75]

ok... I now completely understand how this modules function works... though I still find it interesting that remainders of .5 are returned as 1

I am not exactly sure why that is.... but nonetheless I see how it works now... very clever...

now the real problem is going to be getting the users to enter the data in such a way that it does not affect the integrity of the results of this query...

one mistaken entry can seemingly ruin the integrity of the entire thing...

regardless, I will have a find a way to make them adhere to it...

thanks again for all your hard work....I very much appreciate it....



- Matthew Montalto

 

[gmmastros]

By definition, remainders are whole numbers. Suppose you have 7 strawberries, and 3 kids. You want to share the strawberries equally. Each child would get 2 strawberries with 1 remaining. The mod operator would return the 1.

Does this help?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Matt75]

ok... I now completely understand how this modules function works... though I still find it interesting that remainders of .5 are returned as 1

I am not exactly sure why that is.... but nonetheless I see how it works now... very clever...

now the real problem is going to be getting the users to enter the data in such a way that it does not affect the integrity of the results of this query...

one mistaken entry can seemingly ruin the integrity of the entire thing...

regardless, I will have a find a way to make them adhere to it...

thanks again for all your hard work....I very much appreciate it....



- Matthew Montalto

 

[Matt75]

please excuse that repost... mistake on my part


- Matthew Montalto

 

[Matt75]

yes that does make it clearer... thanks.... by the way... I tried to include the inout_date field in the last query that you sent, but I was unable to get it to work properly... I think what was happening was that it was returning all the check out record, their file numbers and their dates... any idea how I can include the inout_date from the child table in my results?....thanks again....

Matt


- Matthew Montalto