?
Solved

Related to my other Query Question

Posted on 2009-02-09
14
Medium Priority
?
213 Views
Last Modified: 2013-11-27
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.
0
Comment
Question by:yankel1
  • 7
  • 7
14 Comments
 
LVL 15

Expert Comment

by:quincydude
ID: 23596573
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
 
LVL 15

Expert Comment

by:quincydude
ID: 23596602
this?
select count(*) from CreditCardsResponse where Not Approved and ProcessDateTime > (select max(ProcessDateTime)  from CreditCardsResponse where Approved)

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:yankel1
ID: 23596619
ok thats good!
but I need for each MasterID the count
0
 
LVL 15

Expert Comment

by:quincydude
ID: 23596643
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
 

Author Comment

by:yankel1
ID: 23596685
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
 
LVL 15

Expert Comment

by:quincydude
ID: 23596705
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
 

Author Comment

by:yankel1
ID: 23596721
Same error :(
0
 
LVL 15

Expert Comment

by:quincydude
ID: 23596763
this works right?

select MasterID,max(ProcessDateTime) as maxtime from CreditCardsResponse where Approved group by MasterID
0
 

Author Comment

by:yankel1
ID: 23596775
yes this works

I'm attaching the table I'm using
Sample.mdb
0
 
LVL 15

Accepted Solution

by:
quincydude earned 2000 total points
ID: 23596808
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
 

Author Closing Comment

by:yankel1
ID: 31544829
That work's great!!!
0
 

Author Comment

by:yankel1
ID: 23596822
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
 
LVL 15

Expert Comment

by:quincydude
ID: 23596841
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

850 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