Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
callista
Starting Member
1 Post |
 Posted - 2005-11-19 : 11:08:58
|
LOAN+---------+---------+-----------+---------------+| loan_id | cust_id | librarian | checkout_date |+---------+---------+-----------+---------------+| 1 | 1 | Jim | 7-11-2005 |+---------+---------+-----------+---------------+| 2 | 2 | Jim | 7-11-2005 |+---------+---------+-----------+---------------+| 3 | 2 | Kelly | 9-11-2005 |+---------+---------+-----------+---------------+| 4 | 2 | Jim | 9-11-2005 |+---------+---------+-----------+---------------+RENEWAL+---------+-----------+------------+| loan_id | librarian | renew_date |+---------+-----------+------------+| 1 | Kelly | 8-11-2005 |+---------+-----------+------------+| 1 | Jim | 9-11-2005 |+---------+-----------+------------+| 3 | Kelly | 9-11-2005 |+---------+-----------+------------+| 1 | Kelly | 10-11-2005 |+---------+-----------+------------+RESULT+------------+---------+-----+-------+| date | cust_id | Jim | Kelly |+------------+---------+-----+-------+| 7-11-2005 | 1 | 1 | 0 |+------------+---------+-----+-------+| 7-11-2005 | 2 | 1 | 0 |+------------+---------+-----+-------+| 8-11-2005 | 2 | 0 | 1 |+------------+---------+-----+-------+| 9-11-2005 | 1 | 1 | 0 |+------------+---------+-----+-------+| 9-11-2005 | 2 | 1 | 2 |+------------+---------+-----+-------+| 10-11-2005 | 1 | 0 | 1 |+------------+---------+-----+-------+I'm trying to get the resulting table but don't know how to achieve it.I've tried doing several joins but they were all rubbish! Is the resulting table possible? Would be even better if there's a group total for Jim and Kelly for each day. Of course, there's be more librarians and customers.Anyone has any idea? I've only ever used SQL for simple statements. No complicated joins (er ... not sure if this one is even complicated at all  ).Cheers! |
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
Not sure how to join tables for required result