• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

Query Help

I have a history table that has account statuses month over month.  Two of the fields look like this:
PROC_DT - Date Field - Example: 1/31/2012, 2/29/2012, etc...
ACCOUNT_NUMBER

The same ACCOUNT_NUMBER can show up in each month.  How can I write an efficient query that shows me a list of "new" ACCOUNT_NUMBERS per month.  New would be defined by an ACCOUNT_NUMBER showing up for the first time in February's month for example.  So I'm looking for a query that will give me the output below.  I'm looking for a single query (or the shortest query possible) where I can type a date range for 6 months for example.  I'm trying to avoid running a query six times by typing in a single month six different times.

PROC_DT
1/31/2012    4
2/29/2012    10
3/31/2012    15
4/30/2012    7
0
error_prone
Asked:
error_prone
  • 8
  • 4
  • 3
1 Solution
 
peter57rCommented:
What does your sample result mean?
0
 
IrogSintaCommented:
What if the same account number showed up in both February and March. Would it be considered new in March as well?  Is Proc_Dt always the last day of the month for each record in your history table? Or are they varied and you only want the query to show the last day of each month?
0
 
error_proneAuthor Commented:
It means that there were 4 new account numbers in January, 10 in Feb, etc..

Irog - it would only be considered new in Feb.  Yes, Proc_Dt is always the last day of the month.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
IrogSintaCommented:
How about
Select PROC_DT, Count(Distinct ACOUNT_NUMBER) As New_Accounts From TableName Group By PROC_DT

Open in new window

0
 
peter57rCommented:
Once an account has been created , does it appear for every month afterwards or can there be months where it does not appear?
0
 
error_proneAuthor Commented:
"Undefined function unique..."
0
 
error_proneAuthor Commented:
There can be months that it cannot appear once it does initially appear.  It's existence in the history table indicates whether the account is active or not.  So I am looking for an efficient query that tells me how many new accounts there were per month.  The definition of new is that the account did not exist in the month before it appeared.
0
 
IrogSintaCommented:
Ignore my last response.  I wasn't thinking straight.
0
 
error_proneAuthor Commented:
I'm sorry, it was supposed to be "Undefined function Distinct..."
0
 
peter57rCommented:
So an account can be 'new' many times?

This will give a list of the new accounts.  To get a summary, save this query and then build a simple totals query from it as shown in the second query.

Select Tbl.*  from
tbl left join
(Select AccountNumber, Dateadd("m",1,Proc_dt) as Prev from tbl) as qry1
On tbl.accountnumber=qry1.accountnumber and tbl.proc_dt=qry1.proc_dt
where qry1.Accountnumber is null

If that is saved as qrylist then you can do..

Selet Proc_dt, count(accountnumber) as NewAccts from querylist group by Proc_dt
0
 
error_proneAuthor Commented:
Working through errors in that query right now...
0
 
error_proneAuthor Commented:
Nice!
0
 
peter57rCommented:
I'm afraid the answer I gave you doesn't quite work.  It doesn't deal correctly with months that have 30 days.

The main query needs to be your version of...

Select Tbl.*  from
tbl left join
(Select AccountNumber, dateserial(year(proc_dt), month(proc_dt)+2,0) as Prev from tbl) as qry1
On tbl.accountnumber=qry1.accountnumber and tbl.proc_dt=qry1.proc_dt
where qry1.Accountnumber is null
0
 
error_proneAuthor Commented:
Yes, I just saw that the query wasn't working.  Several account numbers are showing as "new" in multiple months.  I just got excited I guess.  Trying new query...
0
 
error_proneAuthor Commented:
Yes, that worked.  Thanks for double-checking.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 8
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now