(Untitled)

[minotaurs]

OH, mighty SQL gurus on my flist..... So, I've got this table that records rental transactions. It has the TransactionDate, ItemID for the item, and the Action (Rented or Returned). For entries that are Rented, it also has CustomerID for the person who rented it. What it does NOT have is the CustomerID for the Return entries. I'm trying to

Comments

suggestion

[anonymous]

You could do

SELECT * FROM [tablename] ORDER BY ItemID, TransactionDate

and that would sort your entries so that for each rental, you'd see the return immediately following it. If you wanted to you could make a (temporary) table of that data, with a primary key, and then use a statement to insert the CustomerIDs into the return records (using a loop or cursor to cycle through the incremental numbers in the primary key field). That would be slightly complicated. But then you'd be able to use a nice simple select statement:

SELECT * FROM [tablename] ORDER BY CustomerID, TransactionDate

Re: suggestion

[minotaurs]

There are different customers - I can pick up the CustomerID from line 1 and copy it into line 2, then pick up a different CustomerID from line 3 and copy into line 4? There would have to be some check to make sure that line 2 was actually a Return.

[aithine]

What're the create statements for the actual tables? (Much easier to see what can be done if you have that. *g*)

[minotaurs]

No idea. These are the tables used by our POS software - I'm trying to do some reporting from them with Crystal Reports.

[babyofthegroup]

Is each item that's referenced in the table a unique item? That is, if you have six copies of DVD X, does each one have a different ItemID?

Assuming that's a yes, you could try (though this is just conceptual; might take ages to run without indexing each sub-select with a primary key):

select a.CustomerID, a.ItemID, a.TransactionDate as RentalDate, min(b.TransactionDate) as ReturnDate
from

(select CustomerID, ItemID, TransactionDate
from [tablename]
where Action=Rented
group by CustomerID
order by TransactionDate) a

left outer join

(select ItemID, TransactionDate
from [tablename]
where Action=Returned
group by ItemID
order by TransactionDate) b

where a.ItemID=b.ItemID
and b.TransactionDate>=a.TransactionDate

I think you may run into issues where there are rental/return combinations on the same day (which can be allowed, right?) so I'm sort of hoping your Date variables are timestamps, not actual dates. But this (or something like it) should work.

[minotaurs]

Ohh, that's clever, that is.

[babyofthegroup]

Did it work? Enquiring minds, etc.

[minotaurs]

Almost. It picked up ALL the returns after each rental, rather than just the next one.