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 |
|
dzabor
Posting Yak Master
138 Posts |
 Posted - 2008-08-07 : 21:05:19
|
| I am trying to find all records in a database that do not have a records in the activity table for teh past 5 years. I will use max (transaction_date) for comparison.select id, max (transaction_date) as MaxID from activitygroup by idWhen i put together the script below I get the error below.select id, last_first, paid_thrufrom namewhere paid_thru = '12/31/2008'and status <> 'D'and id not in (select id, max (transaction_date) as MaxID from activitygroup by id) error:Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.What am I doing wrong?dz |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-07 : 23:07:55
|
| >>What am I doing wrong?the "ID NOT IN(...)" is expecting a list of one or more ID values. You are providing both IDs and Dates.perhaps your subquery could be something like:(select [ID] from [activity] group by [ID] having max(transaction_date) < dateadd(year, -5, getdate()) )Be One with the OptimizerTG |
 |
|
|
dzabor
Posting Yak Master
138 Posts |
Posted - 2008-08-08 : 09:00:24
|
| That worked - thanks!Just to clarify. select [ID] from [activity] group by [ID] statement needs to be by itself and then add any other criteria after? dz |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-08 : 09:25:55
|
quote:
I am trying to find all records in a database that do not have a records in the activity table for teh past 5 years.
The subquery statement (as I wrote it - including the HAVING clause) will only give you IDs with no Activity rows in the last 5 years. I am assuming that the rest of your statement (outside the subquery) is as you need it.Be One with the OptimizerTG |
|
|
|
dzabor
Posting Yak Master
138 Posts |
Posted - 2008-08-08 : 09:37:46
|
| Great - thanks! |
|
|
|
|
|
|
sub query