Related to my other Query Question

I wan't the count only since the last approved one let say 11/1/2008 it was decliend, 12/1/2008 it was approved, 1/1/2009 Declined, 2/1/2009 Declined again. I want the the number 2.
yankel1Asked:
Who is Participating?
 
quincydudeCommented:
select a.MasterID, count(a.ProcessDateTime) from CreditCardsResponse a, (select MasterID,max(ProcessDateTime) as maxtime from CreditCardsResponse where Approved group by MasterID) b  where Not a.Approved and a.ProcessDateTime > b.maxtime and a.MasterID = b.MasterID group by a.MasterID
0
 
quincydudeCommented:
since u didnt give us any detail of your table, i would expect the following

select count(*) from table where status = 'decline' and time > (select max(time)  from table where status = 'approve')

adjust it according to your needs
0
 
yankel1Author Commented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
quincydudeCommented:
this?
select count(*) from CreditCardsResponse where Not Approved and ProcessDateTime > (select max(ProcessDateTime)  from CreditCardsResponse where Approved)

Open in new window

0
 
yankel1Author Commented:
ok thats good!
but I need for each MasterID the count
0
 
quincydudeCommented:
select a.MasterID, count(*) from CreditCardsResponse a, (select MasterID,max(ProcessDateTime) as maxtime from CreditCardsResponse where Approved group by MasterID) b  where Not a.Approved and a.ProcessDateTime > b.maxtime and a.MasterID = b.MasterID
0
 
yankel1Author Commented:
I get this error

You tried to execute a query that does not include the specified expression as part of an aggregate function or grouping.
0
 
quincydudeCommented:
oops

select a.MasterID, count(a.ProcessDateTime) from CreditCardsResponse a, (select MasterID,max(ProcessDateTime) as maxtime from CreditCardsResponse where Approved group by MasterID) b  where Not a.Approved and a.ProcessDateTime > b.maxtime and a.MasterID = b.MasterID
0
 
yankel1Author Commented:
Same error :(
0
 
quincydudeCommented:
this works right?

select MasterID,max(ProcessDateTime) as maxtime from CreditCardsResponse where Approved group by MasterID
0
 
yankel1Author Commented:
yes this works

I'm attaching the table I'm using
Sample.mdb
0
 
yankel1Author Commented:
That work's great!!!
0
 
yankel1Author Commented:
oopes one thing I forgot

I want to count just once per month, if it was declined twice in one month it should count it as one
0
 
quincydudeCommented:
that's not easy for me. but I think you can do it by first do some data massaging to get rid of 'duplicated' decline within the same month and then just use  the query above.
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.

All Courses

From novice to tech pro — start learning today.