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.
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 dayfor 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? ThanksSELECT 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. |
|
|
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 doingThanks! |
|
|
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 SomeResultsetORDER BY SomeAggregation DESCPeter LarssonHelsingborg, Sweden |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-08 : 00:21:06
|
Well, do this and you'll get 5 rowsSELECT 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 thisSELECT 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') |
|
|
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, GtCalcDateFROM CDRMAIN AS AWHERE 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 LarssonHelsingborg, Sweden |
|
|
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. |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 codeso i want the top 5 of the area code for each indivdual |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-08 : 03:01:00
|
Each individual is "CallerName"? Or "Name"?Peter LarssonHelsingborg, Sweden |
|
|
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, GtCalcDateFROM CDRMAINWHERE 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 LarssonHelsingborg, Sweden |
|
|
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!!! |
|
|
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 dataDECLARE @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, GtCalcDateFROM CDRMAINWHERE 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 workSELECT s.[Name],--s.CallerName AreaCode, CallsFROM @Stage sINNER 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.marwORDER BY s.RowID Peter LarssonHelsingborg, Sweden |
|
|
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 boundDECLARE @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, GtCalcDateFROM CDRMAINWHERE 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),GtCalcDateORDER BY [CallerName],-- CallerName COUNT(*) DESC-- Do the workSELECT s.[Name],--s.CallerName AreaCode, CallsFROM @Stage sINNER 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.marwORDER BY s.RowID |
|
|
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 LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-08 : 09:02:14
|
[code]CREATE PROCEDURE dbo.uspGetMyRecords( @FromDate DATETIME, @ToDate DATETIME)ASSET NOCOUNT ONSELECT @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 CDRMAINWHERE 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 workSELECT s.CallerName, s.AreaCode, s.Calls, @FromDate FromDate, @ToDate ToDateFROM @Stage sINNER 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.marwORDER BY s.RowID[/code]Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-08 : 09:26:49
|
Call withEXEC dbo.uspGetMyRecords '20061106', '20061106'orEXEC dbo.uspGetMyRecords '20061101', '20061130'Peter LarssonHelsingborg, Sweden |
|
|
gtjr92
Starting Member
15 Posts |
Posted - 2006-11-08 : 09:48:39
|
Thanks a bunch If i could bother yo for one last thingHow can I extract the rows indivdually?Not sure how to word it any betterWhat 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 likeCaller Acodes Calls DateName 757,856,212,345,542 6,3,2,2,1 2006-11-06I know in sql reports I can do something like =rowID1.value & "," & rowid2.value & "," & Rowid3.ValueI hope i explained it well enough |
|
|
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 LarssonHelsingborg, Sweden |
|
|
|
|
|
|
|