[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query Help

Posted on 2012-09-10
15
Medium Priority
?
292 Views
Last Modified: 2012-09-10
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
Comment
Question by:error_prone
  • 8
  • 4
  • 3
15 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 38383415
What does your sample result mean?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38383453
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
 

Author Comment

by:error_prone
ID: 38383468
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 29

Expert Comment

by:IrogSinta
ID: 38383519
How about
Select PROC_DT, Count(Distinct ACOUNT_NUMBER) As New_Accounts From TableName Group By PROC_DT

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 38383533
Once an account has been created , does it appear for every month afterwards or can there be months where it does not appear?
0
 

Author Comment

by:error_prone
ID: 38383556
"Undefined function unique..."
0
 

Author Comment

by:error_prone
ID: 38383561
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38383571
Ignore my last response.  I wasn't thinking straight.
0
 

Author Comment

by:error_prone
ID: 38383575
I'm sorry, it was supposed to be "Undefined function Distinct..."
0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 38383614
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
 

Author Comment

by:error_prone
ID: 38383720
Working through errors in that query right now...
0
 

Author Closing Comment

by:error_prone
ID: 38383735
Nice!
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38383785
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
 

Author Comment

by:error_prone
ID: 38384083
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
 

Author Comment

by:error_prone
ID: 38384140
Yes, that worked.  Thanks for double-checking.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question