SQL Question

[buellwinkle]

I have a SQL problem I can't figure out. It's a video store and people check out and check in videos. We want to know, what's the maximum of a certain video that was checked out. So for example, I check out a video, "The Terminator" at 9AM and watch it and check it back in at noon. Then another person comes in and checks it out at 1PM and checks it in at 5PM. So the maximum checked out for "The Terminator" is 1. But if another person checks out another copy at 11 AM and returned it at 5 PM, then the maximum checked out is 2.

The table layout is very simple, resource, customer, checkin_timestamp, checkout_timestamp.

Seems like an easy question to ask, just can't figure out how to do it.

 

[bborissov]

Can you pot some simple data and desired result from it? Also please describe what you mean with MAX check out? If what is the time tolerance which give you more then one check-out?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[buellwinkle]

The customer wants to know how many movies to keep in inventory. Hence he wants to see the most amount of movies for each title that are outstanding. So for example, if the store has 10 copies of The Terminator, but the most that are "checked out" or "rented out" is 2 then he has too many copies of that movie and he should reduce inventory. On the other hand if it's the most out is 10, time to buy more copies of that movie.

For example, data -

"Terminator", "buellwinkle", 03/11/07 9:00, 03/11/07 12:00
"Terminator", "bborissov", 03/11/07 11:00, 03/11/07 17:00
"Terminator", "johnsmith", 03/11/07 13:00, 03/11/07 17:00
"The Piano", "johndoe", 03/11/07 10:00, 03/12/07 10:00
"The Piano", "janedow", 03/12/07 11:00, 03/12/07 17:00


The desired result would be -

Title Maximum Qty Checked out
Terminator 2
The Piano 1

 

[bborissov]

Bu I still don't get the logic. How you determinate that "Terminator" count must be 2 but "Piano" must be 1?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[nice95gle]

If the times overlap for a given day that can be counted as 2 or 3 etc...

Well Done is better than well said
- Ben Franklin

 

[gmmastros]

In my opinion, the best solution for this problem would be to create a numbers table in your database. With this numbers table, the query is greatly simplified (meaning that it will also be faster to execute).

In the example I show, I create a table variable for the numbers table. I strongly encourage you to create a numbers table that will span all the hours that you will ever care about.

Also, I create an @Movies table variable to represent your test data. You can copy/paste this query to query analyzer and run it. Make sure it is returning the right information for you. If it appears to work, just create a 'real' Hours table and then change the query to use your table and the real hours table.

[COLOR=blue]Declare[/color] @Movies [COLOR=blue]Table[/color]([COLOR=blue]Name[/color] [COLOR=blue]VarChar[/color](100), RentedBy [COLOR=blue]VarChar[/color](100), RentedOn [COLOR=#FF00FF]DateTime[/color], ReturnedOn [COLOR=#FF00FF]DateTime[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Movies [COLOR=blue]Values[/color]([COLOR=red]'Terminator'[/color], [COLOR=red]'buellwinkle'[/color], [COLOR=red]'03/11/07 9:00'[/color], [COLOR=red]'03/11/07 12:00'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Movies [COLOR=blue]Values[/color]([COLOR=red]'Terminator'[/color], [COLOR=red]'bborissov'[/color], [COLOR=red]'03/11/07 11:00'[/color], [COLOR=red]'03/11/07 17:00'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Movies [COLOR=blue]Values[/color]([COLOR=red]'Terminator'[/color], [COLOR=red]'johnsmith'[/color], [COLOR=red]'03/11/07 13:00'[/color], [COLOR=red]'03/11/07 17:00'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Movies [COLOR=blue]Values[/color]([COLOR=red]'The Piano'[/color], [COLOR=red]'johndoe'[/color], [COLOR=red]'03/11/07 10:00'[/color], [COLOR=red]'03/12/07 10:00'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Movies [COLOR=blue]Values[/color]([COLOR=red]'The Piano'[/color], [COLOR=red]'janedow'[/color], [COLOR=red]'03/12/07 11:00'[/color], [COLOR=red]'03/12/07 17:00'[/color])

[COLOR=blue]Declare[/color] @i [COLOR=#FF00FF]DateTime[/color]
[COLOR=blue]Declare[/color] @Hours [COLOR=blue]Table[/color](AllHours [COLOR=#FF00FF]DateTime[/color])
[COLOR=blue]Set[/color] @i = [COLOR=red]'20070301'[/color]

[COLOR=blue]While[/color] @i < [COLOR=red]'20070401'[/color]
  [COLOR=blue]Begin[/color]
    [COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Hours [COLOR=blue]Values[/color](@i)
    [COLOR=blue]Set[/color] @i = [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Hour[/color], 1, @i)
  [COLOR=blue]End[/color]

[green]-- Query starts here.[/green]
[COLOR=blue]Select[/color]  A.Name,
        [COLOR=#FF00FF]Max[/color](TheCount) [COLOR=blue]As[/color] ConcurrentRentalCount
[COLOR=blue]From[/color]    (
        [COLOR=blue]Select[/color]  M.Name,
                [COLOR=#FF00FF]Count[/color](H.AllHours) [COLOR=blue]As[/color] TheCount
        [COLOR=blue]From[/color]    @Movies M
                [COLOR=blue]Inner[/color] [COLOR=blue]join[/color] @Hours H
                  [COLOR=blue]On[/color] H.AllHours Between M.RentedOn And M.ReturnedOn
        [COLOR=blue]Group[/color] [COLOR=blue]By[/color] H.AllHours, M.Name
        ) [COLOR=blue]As[/color] A
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] A.Name

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

Because of the overlap with terminator, there must be 2 copies checked out. But since the times for Piano don't overlap, there must be only 1 checked out. At least that's my guess.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

I think the poster really means:

Maximum Qty Checked out at one time

-SQLBill

Posting advice: FAQ481-4875

 

[monksnake]

This will be a total stab in the dark, but if it's not perfect I think it can lead you to an answer:

select resource, max(count(resource)) from table1 a inner join table1 b 
on 
a.resource = b.resource and
(((a.checkin_timestamp <= b.checkin_timestamp) and (a.checkout_timestamp >= b.checkin_timestamp)) 
or 
((a.checkin_timestamp <= b.checkout_timestamp) and 
(a.checkout_timestamp >= b.checkout_timestamp)))
group by resource

<.

 

[buellwinkle]

Monksnake, tried it and doesn't work.

GMMastros, worked like a charm, thanks for the help. I made one modification. The only times that are valid are the start times, and not fixed times in the hours table so I did a select distinct of all RentedOn times to generate the list of times to do the between join on. This would give more accurate results (not just hourly) and run quicker as the nested loop join would be looping less times.

Select A.Name,
Max(TheCount) As ConcurrentRentalCount
From (
Select M.Name,
Count(H.AllHours) As TheCount
From Movies M
Inner join (select distinct Rentedon AllHours from Movies) H
On H.AllHours Between M.RentedOn And M.ReturnedOn
Group By H.AllHours, M.Name
) As A
Group By A.Name