error_prone
asked on
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
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
peter57r
What does your sample result mean?
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?
ASKER
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.
Irog - it would only be considered new in Feb. Yes, Proc_Dt is always the last day of the month.
How about
Select PROC_DT, Count(Distinct ACOUNT_NUMBER) As New_Accounts From TableName Group By PROC_DT
Once an account has been created , does it appear for every month afterwards or can there be months where it does not appear?
ASKER
"Undefined function unique..."
ASKER
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.
Ignore my last response. I wasn't thinking straight.
ASKER
I'm sorry, it was supposed to be "Undefined function Distinct..."
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Working through errors in that query right now...
ASKER
Nice!
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.acc
where qry1.Accountnumber is null
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.acc
where qry1.Accountnumber is null
ASKER
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...
ASKER
Yes, that worked. Thanks for double-checking.