Need Help conceptualizing a checkin checkout system

[user125]

Hey guys,

Here is what I'm looking for.
for example, I have 5 cameras (labeled c1 - c5) and I have 5 users (u1 - u5), I'm trying to build a system where, I can record a checkout date when U1 takes C1 and then record when he checks it in. Thats all fine and simple to do, the part I'm having a hard time visualizing is lets say I have the following.
cam user checkout checkin
C1 U1 1/1/01 1/5/02
C2 U2 2/2/02 2/5/02
C3 U3 3/8/03 1/5/04
C1 U3 3/8/03
C2 U1 4/5/04

Now on my main page, I want to be able to display what is the 'latest' status of all cams, so in theory it should look like:

cam user checkout checkin
C1 U3 3/8/03
C2 U1 4/5/04
C3 U3 3/8/03 1/5/04

I figured, I cannot use a datagrid because it'll just pull out all the data from the table, as where here, I only want the information of the latest status of each cam.

Further more, if I click on any cam under the colum cam, i'd like to have a history of all information on each cam.

What would I need to display only the latest information?
Right now I have 3 tables. I have a user table, a camera table, and a checkout table that has user and camera as foreign keys. I thought, i might have to have a seperate query for each camera, so select * from cam where cam = c1 sort date asc, and figure it would just take the top data.
However, that seems wrong to me. Any help would be appreciated.

 

[jmeckley]

for your summary page use this sudo query

Select o.cameraId, o.userId, max(o.checkedOutDate) as lastCheckedOut, (Select checkedInDate from checkout t where o.cameraId = t.cameraId and o.userId = t.userId and lastCheckedOut = t.checkedOutDate) as InOrOut
From   checkout o
group by o.cameraId, o.userId

writing this from scratch, so there may be a syntax error here or there. you may need to group by [tt]InOrOut[/tt].
another issue might be [tt]lastCheckedOut = t.checkedOutDate[/tt]. this may need to be [tt]max(o.checkedOutDate) = t.checkedOutDate[/tt] instead.

so use some variation of the query above to load your summary grid. from there link to a viewhistory.aspx page and pass the carmera id, user id to the page. then query the checkout table on camera id, and user id. I would assume you would sort the list checkedOutDate descending to see the most recent history first.

depending on how the history table information is displayed, you may want to concider paging the data to make it managable for the user.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[blar9]

if its always 5 cams then

Select Top(5) from Table Order By checkout DESC

 

[jmeckley]

top 5 wouldn't work. your assuming you have to check out cameras 1-5 in a specific order. if any given camera is checked out twice before any other camera, then one of them cameras will be left off the list.

what happens when they purchase a new camera, replace an existing camera, or decommission a camera, then a developer would need to modify the code because the inventory changed.

to simplify my previous query the following may work better

select c.cameraId, c.userId, c.checkOutDate, c.checkInDate
from   checkout c inner join (
           select cameraId, max(checkOutDate) as currentDate
           from   checkout
           group by cameraId
       ) t on c.cameraId = t.cameraId and c.checkOutDate = t.currentDate
order by c.cameraId

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[user125]

SOrry to bring back a old post, I just saw this now after being afk for a while. Jmeckly, thank you for your answer. I'd like to break it down so I can understand it better..

I'm assuming here by your code we have three tables
c
checkout
t

What fields does each table have? I'm not to versed in database codes when it comes to joins. Thanks again.

 

[jbenson001]

There are 2 tables
camera which jason assigned an alis "c" to.

The other "t" is a derived table from the sub select.

 

[user125]

checkout c inner join (
select cameraId, max(checkOutDate) as currentDate
from checkout
group by cameraId
) t on c.cameraId = t.cameraId and c.checkOutDate =

Isn't there three here? checkout, c and t? because its From Checkout, c and t?

 

[jbenson001]

Nope.. the alias for Checkout is c
the derived table is t

From Checkout c
means that in the query you can refernce Checkout by c, instead of typing out the entire table name.