Q
Problem solve Get help with specific problems with your technologies, process and projects.

Customers who made an account deposit every week

I need to return all customers which have made savings every week.

Table1: Customers - AccountNo, CustName, CustAddr

Table2: Savings - DepositNo, WeekNo, AccountNo, DepositAmt


An excellent question. Off the top of my head, the answer would be --

select distinct CustName
  from Customers
 where AccountNo in
       ( select AccountNo
           from Savings
       group by AccountNo
         having count(distinct WeekNo)
              = ( select count(distinct Weekno)
                    from Savings )
       )

This should be fairly efficient, because the sub-subquery which counts the number of distinct week numbers in the Savings table will be executed only once; this gives a static number to be tested in the HAVING clause in the subquery, to find all the account numbers which qualify. This set of account numbers then determines which customers qualify, and the DISTINCT on the outer query ensures that customers who might have more than one such qualifying account are reported only once.

When I tried testing this in Access 97, I discovered that Access 97 does not support COUNT DISTINCT. However, all is not lost. Access allows you to run a query against a saved query -- in effect, a view mechanism. The problem can also be solved with views, but I'll describe the process using Access queries instead. Note that Access queries do not actually save any results; when you query a query in Access, the database engine "combines" the query definitions "under the covers" and only the combined query is actually executed.

The first step is to create a query for distinct week numbers --

select distinct WeekNo
  from Savings

Save this query as DistinctWeeks_Q.

Now find the week numbers for each account --

select distinct AccountNo, WeekNo
  from Savings

Save this query as DistinctAccountWeeks_Q.

Now you can formulate the final query --

select CustName
  from Customers
 where AccountNo in
       ( select AccountNo
           from DistinctAccountWeeks_Q
       group by AccountNo
         having count(*)
              = ( select count(*)
                    from DistinctWeeks_Q )
       )

There's only one other problem that might trip you up -- what if there's a week where nobody made a deposit? The query will still run, and still give results, but it will be all customers that made a deposit in every week that any customer made a deposit, which isn't exactly the same as every week. You may need to generate a different sub-subquery, for example from a calendar week number table, if you want to catch those gaps.

For More Information

  • What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was last published in May 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close