Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Select Top not selecting top...

Author  Topic 

gtjr92
Starting Member

15 Posts

Posted - 2006-11-07 : 19:00:33
I have a query and I want the top 5 areacodes that were dialed the most for that day
for each name. For some reason no matter if i put top 5 or top 1 I still get the same results.
If gives me the proper data just more than i want.
What am i missing here?
Thanks

SELECT Name, COUNT(SUBSTRING(TargetNum, 2, 3)) AS Acodecount, SUBSTRING(TargetNum, 2, 3) AS ACode, GtCalcDate
FROM CDRMAIN AS A
WHERE (LEN(TargetNum) >= 11) AND (SUBSTRING(TargetNum, 2, 3) NOT LIKE '800%') AND (GtCalcDate IN (SELECT TOP 5 GtCalcDate
FROM CDRMAIN AS B
WHERE (A.GtCalcDate = GtCalcDate) AND (A.CallerName = CallerName) AND (A.GtCalcDate = '2006-11-06 00:00:00.000')
ORDER BY GtCalcDate DESC))
GROUP BY GtCalcDate, Name, SUBSTRING(TargetNum, 2, 3)
HAVING (Name = name1') OR
(Name = 'name2') OR
(Name = 'name3') OR
(Name = 'name4')

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-07 : 19:06:00
Your query selects rows that match 5 dates but then groups them by the 5 dates, and by name and a substring of target number. So each of the 5 dates is going to have multiple names and or target numbers and you'll get each of them separately.

You need to remove Name and SUBSTRING(TargetNum, 2, 3) from the group by and use them in aggregates (like count, min or max) otherwise you can't make those multiple values into the 5 rows you want.
Go to Top of Page

gtjr92
Starting Member

15 Posts

Posted - 2006-11-07 : 21:06:26
can you give me a better idea on how to do that exactly?
I just tried a few things, but there must be something i am not doing
Thanks!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 00:17:12
If you use TOP operator, you also need to include a ORDER BY, so the query make any sense.

SELECT TOP 5 *
FROM SomeResultset
ORDER BY SomeAggregation DESC


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-08 : 00:21:06
Well, do this and you'll get 5 rows
SELECT COUNT(SUBSTRING(TargetNum, 2, 3)) AS Acodecount, GtCalcDate
FROM CDRMAIN AS A
WHERE (LEN(TargetNum) >= 11) AND (SUBSTRING(TargetNum, 2, 3) NOT LIKE '800%')
AND (GtCalcDate IN (SELECT TOP 5 GtCalcDate
FROM CDRMAIN AS B
WHERE (A.GtCalcDate = GtCalcDate) AND (A.CallerName = CallerName)
AND (A.GtCalcDate = '2006-11-06 00:00:00.000')
ORDER BY GtCalcDate DESC))
GROUP BY GtCalcDate

Now, you'll say, but then I don't get the names. Right, you have multiple names per date which is why you get multiple rows per date and then more than 5 rows.
I can't tell you what to do next because I don't know what you want to do with the multiple names, you could just pick the maximum one if that's meaningful? Like this
SELECT max(Name), COUNT(SUBSTRING(TargetNum, 2, 3)) AS Acodecount, GtCalcDate
FROM CDRMAIN AS A
WHERE (LEN(TargetNum) >= 11) AND (SUBSTRING(TargetNum, 2, 3) NOT LIKE '800%')
AND (GtCalcDate IN (SELECT TOP 5 GtCalcDate
FROM CDRMAIN AS B
WHERE (A.GtCalcDate = GtCalcDate) AND (A.CallerName = CallerName)
AND (A.GtCalcDate = '2006-11-06 00:00:00.000')
ORDER BY GtCalcDate DESC))
GROUP BY GtCalcDate, Name
HAVING (Name = name1') OR
(Name = 'name2') OR
(Name = 'name3') OR
(Name = 'name4')
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 00:53:09
You only select 1 date, 2006-11-06, since the IN does a WHERE for that particular date.
Something like this?
SELECT TOP 5	GtCalcDate,
CallerName,
SUBSTRING(TargetNum, 2, 3) AS ACode,
COUNT(*) AS Acodecount,
GtCalcDate
FROM CDRMAIN AS A
WHERE LEN(TargetNum) >= 11
AND TargetNum NOT LIKE '_800%'
AND CallerName IN (Name1, Name2, Name3, Name4)
GROUP BY GtCalcDate,
CallerName,
SUBSTRING(TargetNum, 2, 3)
ORDER BY GtCalcDate DESC

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gtjr92
Starting Member

15 Posts

Posted - 2006-11-08 : 00:56:46
"I can't tell you what to do next because I don't know what you want to do with the multiple names"
I just want the top 5 areacodes dialed for each name for that particular gtcalcdate.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 01:52:33
Ok, I understand you right, you want top 5 areacodes for every day? For a daterange? Or a particular day only?
What is TOP 5 based on?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gtjr92
Starting Member

15 Posts

Posted - 2006-11-08 : 02:53:56
top 5 is based of the count of the times each particular name dialed an area code
so i want the top 5 of the area code for each indivdual
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 03:01:00
Each individual is "CallerName"? Or "Name"?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 03:05:37
Begin with this query. Does this produce the INFORMATION you want?
If so, we can restrict the maximum number of records for each name, later.
SELECT		Name,-- CallerName
SUBSTRING(TargetNum, 2, 3) AS AreaCode,
COUNT(*) AS AreaCodeCount,
GtCalcDate
FROM CDRMAIN
WHERE LEN(TargetNum) >= 11
AND TargetNum NOT LIKE '_800%'
AND GtCalcDate >= '2006-11-06'
AND GtCalcDate < '2006-11-07'
GROUP BY Name,-- CallerName
SUBSTRING(TargetNum, 2, 3)


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gtjr92
Starting Member

15 Posts

Posted - 2006-11-08 : 08:12:08
Ok This is giving me the same information i had in my original query.
Which is fine,
How can We Restrict how many rows are returned for each user based on the areacode count Descending?
Thanks!!!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 08:29:31
quote:
Originally posted by gtjr92

Ok This is giving me the same information i had in my original query.
Yes, I know. But this one is far more efficient.
-- Stage the data
DECLARE @Stage TABLE (RowID INT IDENTITY(1, 1), [Name] VARCHAR(x), AreaCode VARCHAR(3), Calls INT)

INSERT @Stage
(
[Name],
AreaCode,
Calls
)
SELECT Name,-- CallerName
SUBSTRING(TargetNum, 2, 3) AS AreaCode,
COUNT(*) AS AreaCodeCount,
GtCalcDate
FROM CDRMAIN
WHERE LEN(TargetNum) >= 11
AND TargetNum NOT LIKE '_800%'
AND GtCalcDate >= '2006-11-06'
AND GtCalcDate < '2006-11-07'
GROUP BY Name,-- CallerName
SUBSTRING(TargetNum, 2, 3)
ORDER BY Name,-- CallerName
COUNT(*) DESC

-- Do the work
SELECT s.[Name],--s.CallerName
AreaCode,
Calls
FROM @Stage s
INNER JOIN (
SELECT Name,-- CallerName
MIN(RowID) mirw,
4 + MIN(RowID) marw
FROM @Stage
GROUP BY Name-- CallerName
) q ON q.[Name] = s.[Name] AND s.RowID BETWEEN q.mirw AND w.marw--q.CallerName = s.CallerName AND s.RowID BETWEEN q.mirw AND w.marw
ORDER BY s.RowID

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gtjr92
Starting Member

15 Posts

Posted - 2006-11-08 : 08:57:15
I tried to adapt this for my db but i keep getting the multipart identifer w.marw could not be bound


DECLARE @Stage TABLE (RowID INT IDENTITY(1, 1), [Name] VARCHAR(256), AreaCode VARCHAR(3), Calls INT,Date DateTime)

INSERT @Stage
(
[Name],
AreaCode,
Calls,
Date
)
SELECT [CallerName],-- CallerName
SUBSTRING(TargetNum, 2, 3) AS AreaCode,
COUNT(*) AS AreaCodeCount,
GtCalcDate
FROM CDRMAIN
WHERE LEN(TargetNum) >= 11
AND TargetNum NOT LIKE '_800%'
AND GtCalcDate >= '2006-11-06'
AND GtCalcDate < '2006-11-07'
GROUP BY [CallerName],-- CallerName
SUBSTRING(TargetNum, 2, 3),GtCalcDate
ORDER BY [CallerName],-- CallerName
COUNT(*) DESC

-- Do the work
SELECT s.[Name],--s.CallerName
AreaCode,
Calls
FROM @Stage s
INNER JOIN (
SELECT [Name],-- CallerName
MIN(RowID) mirw,
4 + MIN(RowID) marw
FROM @Stage
GROUP BY [Name]-- CallerName
) q ON q.[Name] = s.[Name] AND s.RowID BETWEEN q.mirw AND w.marw And q.[Name] = s.[Name] AND s.RowID BETWEEN q.mirw AND w.marw
ORDER BY s.RowID

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 08:58:30
Sorry. Should be "q.marw"
Why are you altering the complete line with ON?
I put -- there to comment out some code.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 09:02:14
[code]CREATE PROCEDURE dbo.uspGetMyRecords
(
@FromDate DATETIME,
@ToDate DATETIME
)
AS

SET NOCOUNT ON

SELECT @FromDate = DATEADD(day, DATEDIFF(day, 0, @FromDate), 0),
@ToDate = DATEADD(day, DATEDIFF(day, 0, @FromDate), 0)

DECLARE @Stage TABLE (RowID INT IDENTITY(1, 1), CallerName VARCHAR(256), AreaCode VARCHAR(3), Calls INT, theDate DATETIME)

INSERT @Stage
(
CallerName,
AreaCode,
Calls
)
SELECT CallerName
SUBSTRING(TargetNum, 2, 3),
COUNT(*)
FROM CDRMAIN
WHERE LEN(TargetNum) >= 11
AND TargetNum NOT LIKE '_800%'
AND GtCalcDate >= @FromDate
AND GtCalcDate < DATEADD(day, 1, @ToDate)
GROUP BY CallerName
SUBSTRING(TargetNum, 2, 3)
ORDER BY CallerName
COUNT(*) DESC

-- Do the work
SELECT s.CallerName,
s.AreaCode,
s.Calls,
@FromDate FromDate,
@ToDate ToDate
FROM @Stage s
INNER JOIN (
SELECT CallerName,
MIN(RowID) mirw,
4 + MIN(RowID) marw
FROM @Stage
GROUP BY CallerName
) q ON q.CallerName = s.CallerName AND s.RowID BETWEEN q.mirw AND q.marw
ORDER BY s.RowID[/code]
Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 09:26:49
Call with

EXEC dbo.uspGetMyRecords '20061106', '20061106'

or

EXEC dbo.uspGetMyRecords '20061101', '20061130'


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gtjr92
Starting Member

15 Posts

Posted - 2006-11-08 : 09:48:39
Thanks a bunch If i could bother yo for one last thing
How can I extract the rows indivdually?
Not sure how to word it any better
What i want to do is put this info in a report that lists the top 5 area codes in one column per user
so the data would look something like
Caller Acodes Calls Date
Name 757,856,212,345,542 6,3,2,2,1 2006-11-06

I know in sql reports I can do something like =rowID1.value & "," & rowid2.value & "," & Rowid3.Value

I hope i explained it well enough
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 09:51:10
There are a lot of CSV functions here. Both extracting data and building data.
Do a search.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -