count records within 3 days

[vtselios]

Hello guys,

I need some help please with a database. I have a table with many records (400.000) and i have a field [date] , a field [cname] and a field [cphone]. I want to count duplicate phone numbers in a range of three days. Is that possible?

Thanks a lot

 

[xenou]

something like:

SELECT MIN(calldate) AS MinCallDate, CPhone, "*" AS FirstCall FROM TABLE1
UNION
SELECT calldate AS MinCallDate, CPhone, "" AS FirstCall FROM TABLE1
ORDER BY CPhone, FirstCall DESC, CallDate

There is a small possibility of error if times are recorded with only minutes, and two calls can come up in the same minute (and you want both of them) - here we would only show one of them because of union discarding duplicates.

 

[xenou]

Ah, there is another error actually...

 

[Micron]

union the result with the min date (and a star) to the results greater than the min date (without a star)

The "star" (x) would be put there as a flag to denote it is the earliest occurrence of a particular call, so there is nothing to query against it or union it with anything until that record is flagged. For me, that gets put there via a code solution. But I think I am done with this one until some answers are provided.

 

[xenou]

For me, that gets put there via a code solution.

Not necessary! But of course possible...

@vtselios, in order to get the best solution can you say what the primary keys are on the table(s) and what the data type of the call date is (and whether it records time with precision down to seconds, basically).

 

[xenou]

Alright, well assuming calldates do not have the same value for a phone number (because it's not allowed or because the dates have enough precision that it's actually not possible for a call to be made twice at the same time) then:

SELECT 
	MIN(T1.calldate) AS MinCallDate, 
	T1.CPhone,
	"*" AS FirstCall 
FROM 
	TABLE1 T1

UNION ALL

SELECT 
	T2.calldate AS MinCallDate, 
	T2.CPhone, 
	"" AS FirstCall 
FROM 
	TABLE1 T2
WHERE 
	T2.calldate <> (
			SELECT 
				MIN(TSub.calldate) 
			FROM 
				TABLE1 TSub 
			WHERE 
				T2.calldate = TSub calldate 
				AND T2.CPhone = TSub CPhone
			)
ORDER BY
	CPhone, FirstCall DESC, calldate

There may be faster solutions though. This will require testing to make sure it's fast enough. If the order by complains, then nest it all in another set of parentheses: SELECT X.calldate, X.cphone, X.firstcall FROM (...) AS X ORDER BY X.calldate, X.firstcall DESC, X.cphone

As a matter of improved performance, it may be faster to save the result in a temp table first (just all the calls), then update the first call with the star.