Help with SQL statement please?

Posted by: Viking

Information Systems

Help with SQL statement please?
has no ratings.

Published: 16 May 2016
Revised: 19 May 2016 - 2899 days ago
Last viewed on: 26 Apr 2024 (2811 views)

Main URL:

Help with SQL statement please? Published by: Viking on 16 May 2016 view comments(4)

Hi, hoping for some quick guidance on what should be a very easy SQL statement...

I would like to find all accounts in a table where there is more than one record in the table for that account where the end date is 0. Each account should only have one record in the table without an end date, so I'm trying to find those accounts that have more than one record with no end date.

Something like:

select account
from myfile
where enddate = 0
group by account
having count(*) > 1
order by account

But this will also find accounts with more than one record as long as one of them has an enddate = 0, and I only want to see those where multiple records for that account have enddate = 0.

Any help would be appreciated... thanks!

Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)

Posted by: bobcozzi

Site Admin ****

Chicagoland

www.cozTools.com/qryfile

Comment on: Help with SQL statement please?
Posted: 7 years 11 months 10 days 9 hours 45 minutes ago

select account                          
from myfile                             
                                        
group by account, enddate               
having count(*) > 1 and enddate = 0     
order by account

Posted by: Viking

Premium member *

Comment on: Help with SQL statement please?
Posted: 7 years 11 months 10 days 5 hours 39 minutes ago

Yes, that was it... thanks Bob!

Posted by: DaleB

Premium member *

Reading, PA

Comment on: Help with SQL statement please?
Posted: 7 years 11 months 8 days 7 hours 31 minutes ago

WHERE condidtions are applied before GROUPing. The query as you originally presented it should work as is. Is there more that you're not showing us? Some JOINs maybe?

Posted by: Viking

Premium member *

Comment on: Help with SQL statement please?
Posted: 7 years 11 months 8 days 4 hours 10 minutes ago

Dale, no this is the whole statement. Mine also showed accounts with just one open record (no end date) as long as there were multiple records for the account, but Bob's only shows accounts with multiple OPEN records. Bob's produces the result I needed.