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 |
|
sql_dan
Starting Member
43 Posts |
 Posted - 2010-04-29 : 10:54:41
|
Hi Guys, I have done some googling on this but as my noob sql skills has so far just about got me by I am stuck once more!  I have a table full of booked appointments (apbook) and a table full of appointment that have happened (apoint). If an appointment is moved it has a status of 'M' and moved from apbook to apoint and the new appointment where it has moved to is created in apbook.I would like a query that (along with a couple of other fields) return both the moved appointment and when the new one has been booked.I can create a query to pick up the moved appointments but when I go to join the tables together the multiple responses from the subquery error the code.  This selects all moved appointments from today and 1 week into the future!select a.[date],a.[episode],a.[status] from apoint a where status = 'M' and date > getdate() -1 and date < GETDATE() +8What I need is to join to the table apbook and look up in there the fields that match on a.[episode] which is the unique identifier here.This is what I have:select a.[date],a.[episode],a.[status] from apoint a where status = 'M' and date > getdate() -1 and date < GETDATE() +8 and a.[episode] in (select ab.[date],ab.[key],ab.[episode],ab.[status] from apbook ab)Any help would be gratefully received! Many thanks If you cant sleep at night, its not the coffee its the bunk! |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-04-29 : 11:09:50
|
This?select a.[date],a.[episode],a.[status],ab.[date],ab.[key],ab.[status]from apoint a inner join apbook ab on a.[episode] = ab.[episode]where a.[status] = 'M' and a.date between dateadd(d,-1,dateadd(d, datediff(d, 0, getdate()), 0))and dateadd(d,8,dateadd(d, datediff(d, 0, getdate()), 0)) |
 |
|
|
sql_dan
Starting Member
43 Posts |
Posted - 2010-04-30 : 01:59:41
|
quote:
Originally posted by vijayisonly
This?select a.[date],a.[episode],a.[status],ab.[date],ab.[key],ab.[status]from apoint a inner join apbook ab on a.[episode] = ab.[episode]where a.[status] = 'M' and a.date between dateadd(d,-1,dateadd(d, datediff(d, 0, getdate()), 0))and dateadd(d,8,dateadd(d, datediff(d, 0, getdate()), 0))
Wow!! This is amazing, thank you so much for your help!I knew I needed to Join but couldn't think where!!!Cheers :beer:If you cant sleep at night, its not the coffee its the bunk! |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-04-30 : 09:47:02
|
Np. You're welcome  |
|
|
|
|
|
|
|
|
Subquery with Multiple Results