Mix "Count" with a select statement 2

[dvannoy]

I dont even know if this is possible but here goes..

I need to generate a query that will first do a count and then based off if the count is >3 run a select statement and return records that where >3.

so say a customer was invoiced >3 times but i need to see all three invoices in a query how would I do that without including the count in the select statement and not grouping those records?

any help would be appreciated
thanks

 

[mrdenny]

I'm not clear on what your looking for.

If the user has more that 3 invoices you want to see the most recent 3 invoices?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[dvannoy]

no..no.
I need to first count the records.. if the count is >3 for those grouped records, then I need to show each record. it's like if I wanted to see detail on a TOP 10 report based off the count.

 

[dvannoy]

e.g..


SELECT COUNT(NO) AS TOTAL
FROM TABLE
WHERE TYPE <> 'C'
AND(NOT(DISCDATE IS NULL))


OK...now based off this count I want to see each record line by line on the count >3

is this possible?

thanks

 

[mrdenny]

Oh, OK. I think I see where you are going with this. Try this out.

/*Create a Temp table*/
create #CustList
(CustID int,
cnt int)

/*Load the Temp table with a list of customers that have more than 3 invoices*/
insert into #CustList
select CustID, count(*)
from table
where Type <> 'C'
having count(*) > 3

/*Pull up the invoices for those customers*/
select *
from table
where CustID in (select CustID from #CustList)

/*Drop the temp table*/
drop table #CustList

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[dvannoy]

PERFECT!!!

Dam your good

I didn't even think about a temp table..


Thanks man

 

[mrdenny]

My pleasure. Have a good one.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[ttba]

Assuming each invoice record has a customer field:

SELECT Invoices
FROM table
WHERE (Customer IN (SELECT Customer
FROM table
GROUP BY Customer
HAVING COUNT(*) > 3))

 

[dvannoy]

I have one other problem. I am using VB as my front end and for somereason I cannot tie this procedure to an ADO recordset. I have many other procedures running from my front end so I know it's not my code there. First I thought maybe it was dropping the table before it can become a ADO recordset but thats not the case.

 

[donutman]

Are you using Denny's procedure? If a SP creates more than one recordset, you have to select the right one in the VB app. If you continue to have trouble, post your question in the VB forum.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

My proc should only be returning a single record set. But to be sure try moving to the second recordset. It might be picking up the insert into the temp table as the first recordset.

Failing that, i'd check with the VB group.

Denny

--Anything is possible. All it takes is a little research. (Me)