November 8, 2006 at 11:19 am
I want to display the top 5 count of areacodes for each name
I want to combine all the results for the areacodes into one column for each user. like a csv. I tried my code below but the results just return all of the areacodes for all names on each area code row with each callername. like
joe blow 123,456,755,312,465,567,555
I just want the top 5 for each particular name.
I tried readin a few articles and i could not figure out what i am missing!
Thanks
DECLARE@Stage TABLE (RowID INT IDENTITY(1, 1), Name VARCHAR(256), AreaCode VARCHAR(3), Calls INT, theDate DATETIME,strValues VarChar(8000))
DECLARE @strValues varchar(8000)
INSERT@Stage
( Name,
AreaCode,
Calls,
theDate,
strvalues
)
SELECTName,
SUBSTRING(TargetNum, 2, 3) AS AreaCode,
COUNT(*) AS AreaCodeCount,
GtCalcDate,[Result]=@strValues
FROMCDRMAIN
WHERELEN(TargetNum) >= 11
AND TargetNum NOT LIKE '_800%'
AND GtCalcDate >= '2006-11-06'
AND GtCalcDate < '2006-11-07'
GROUP BYCallerName,
SUBSTRING(TargetNum, 2, 3),GtCalcDate
ORDER BYName,
COUNT(*) DESC
-- Do the work
SELECTs.Name,
s.AreaCode,
s.Calls,
s.theDate
FROM@Stage s
INNER JOIN(
SELECT[Name],
MIN(RowID) mirw,
4 + MIN(RowID) marw
FROM@Stage
GROUP BYName
HAVING (Name = Name1) OR (Name = 'Name2')
) q ON q.CallerName = s.CallerName AND s.RowID BETWEEN q.mirw AND q.marw
ORDER BYs.RowID
SELECT @strValues = COALESCE(@strValues+',', '') + AreaCode
From @Stage
select distinct Name,thedate, @strValues As Acodes from @Stage
Group by Name,AreaCode,TheDate
HAVING (Name = Name1) OR (Name = 'Name2') [/code]
November 8, 2006 at 11:41 am
the normal Caveats for this question: you should do this at the presentation layer, whether it is an app or a web page, because it's easier.
here's one way to do it in SQL:
declare @MyAreaCodes varchar(50)
set @MyAreaCodes =''
SELECT top 5 @MyAreaCodes = @MyAreaCodes + ISNULL(AreaCodes,'') + ',' from sometable
print @MyAreaCodes
Lowell
November 8, 2006 at 12:21 pm
i tried this and a few other things, but just can't get the correct syntax
DECLARE@Stage TABLE (RowID INT IDENTITY(1, 1), CallerName VARCHAR(256), AreaCode VARCHAR(3), Calls INT, theDate DATETIME,myareacode int)
declare @MyAreaCode varchar(50)
INSERT@Stage
(
CallerName,
AreaCode,
Calls,
theDate
--myAreacode
)
SELECTCallerName,
SUBSTRING(TargetNum, 2, 3) AS AreaCode,
COUNT(*) AS AreaCodeCount,
DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0)
FROMCDRMAIN
WHERELEN(TargetNum) >= 11
AND TargetNum NOT LIKE '_800%'
AND GtCalcDate >= '2006-11-06'
AND GtCalcDate < '2006-11-07'
GROUP BYCallerName,
SUBSTRING(TargetNum, 2, 3),
DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0)
ORDER BYCallerName,
COUNT(*) DESC
-- Do the work
SELECTs.CallerName,
s.AreaCode,
s.Calls,
s.theDate
FROM@Stage s
INNER JOIN(
SELECTCallerName,
MIN(RowID) mirw,
4 + MIN(RowID) marw
FROM@Stage
GROUP BYCallerName
HAVING (CallerName = 'Beth Scharstein') OR
(CallerName = 'name1') OR
(CallerName = 'name2')
) q ON q.CallerName = s.CallerName AND s.RowID BETWEEN q.mirw AND q.marw
ORDER BYs.RowID
set @myAreaCode ='SELECT top 5 @myAreaCode = @myAreaCode + ISNULL(AreaCode,'') + ',' from @stage'
print @myAreaCode
November 8, 2006 at 12:28 pm
does this work?
change the last statement at the end to the following:
set @myAreaCode ='SELECT top 5 @myAreaCode = @myAreaCode + ISNULL(AreaCode,'') + ',' from @stage'
set @myAreaCode ='''
SELECT top 5 ISNULL(AreaCode,'') from @Stage --does this return a recordset?
set @MyAreaCodes =''
SELECT top 5 @MyAreaCodes = @MyAreaCodes + ISNULL(AreaCodes,'') + ',' from @Stage
SELECT @MyAreaCodes AS MyAreaCodes
Lowell
November 8, 2006 at 1:01 pm
this almost does it, it returns the same myareacode list for every caller, and it is not the top 5 by count for each name.
November 8, 2006 at 8:56 pm
Really? Easier in the App? Could I see an example, please? And don't forget to report what the performance is... thanks.
--Jeff Moden
November 8, 2006 at 11:29 pm
Gail,
Sorry I don't have time to modify this code snippet I borrowed from some of the production code I wrote for work... lemme give you a couple of hints...
Here's the snippet for you to modify to suit your needs...
--===== If the working table exists, drop it
IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL
DROP TABLE #MyHead
--===== Recreate the working table
CREATE TABLE #MyHead
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CustID INT,
NPA VARCHAR(20),
CallCount INT,
GroupRank INT,
Top5NPA VARCHAR(20)
)
--===== Declare and preset local variables
DECLARE @PrevCust INT
SET @PrevCust = -2000000000 --No customers way down here
DECLARE @MyCount INT
--===== Populate the working table with data grouped by CustID to count calls per NPA
-- with the call counts in descending order. This takes 17 seconds to resolve over
-- 4.34 MILLION Call Detail Records (CDR's) down to 176,668 records
INSERT INTO #MyHead
(CustID,NPA,CallCount,Top5NPA)
SELECT CustID,
NPA = SUBSTRING(TermNumber,1,3),
CallCount = COUNT(*),
Top5NPA = SUBSTRING(TermNumber,1,3)
FROM Prod_Usage_US_20060225.dbo.Usage --select count(*) from Prod_Usage_US_20060225.dbo.Usage
GROUP BY CustID, SUBSTRING(TermNumber,1,3)
ORDER BY CustID ASC, CallCount DESC, NPA ASC
--===== Do a cascaded variable update to rank each NPA for each CustID
UPDATE #MyHead
SET @MyCount = GroupRank = CASE
WHEN CustID <> @PrevCust
THEN 1
ELSE @MyCount+1
END,
@PrevCust = CustID
FROM #MyHead
--===== We already have the first NPA in the Top5NPA column... go get the next 4 by rank
-- for each custid in a columnar loop (not RBAR, updates whole sets in column 4 times)
SET @MyCount = 2
WHILE @MyCount <= 5
BEGIN
UPDATE #MyHead
SET Top5NPA = t.Top5NPA+','+d.NPA
FROM #MyHead t,
(--Derived table "d" finds area codes by increasing rank
SELECT CustID,NPA
FROM #MyHead
WHERE GroupRank = @MyCount
) d
WHERE t.CustID = d.CustID
AND t.GroupRank = 1
SET @MyCount = @MyCount + 1
END
--===== Return the result set
SELECT *
FROM #MyHead
WHERE GroupRank = 1
ORDER BY RowNum
Here's what the first 20 lines of output look like... note that "CallCount" is still the number of calls for the single NPA which also happens to be the most used NPA for the given customer... GroupRank and RowNum mean nothing at this point...
RowNum CustID NPA CallCount GroupRank Top5NPA
----------- ----------- -------------------- ----------- ----------- --------------------
1 1000 248 243 1 248,869,517,352,941
17 1001 248 219 1 248,810,313,734,586
26 1005 248 93 1 248,586,425,813,989
31 1008 586 10 1 586
32 1010 734 9 1 734,904
34 1011 906 72 1 906
35 1016 906 80 1 906,317
37 1023 248 615 1 248,586,313,216,206
48 1066 330 15 1 330
49 1068 330 32 1 330,937,520,614,847
54 1073 330 196 1 330,734
56 1076 586 36 1 586,707,760,510,530
62 1089 330 306 1 330,304,775,216,949
73 1099 330 121 1 330,704
75 1102 330 43 1 330,234,216,615,646
80 1117 989 434 1 989,225,507,520,734
110 1124 586 87 1 586,248,212,734
114 1134 586 88 1 586,248,313,925
118 1141 734 237 1 734,517,956,615,770
131 1143 313 29 1 313,773
(20 row(s) affected)
--Jeff Moden
November 9, 2006 at 5:54 am
Jeff Thanks that worked great, Except How can i get that to work within an existing stored procedure
here is my Sproc that i am trying to implement this with.
Thanks again
Alter Procedure [dbo].[WeeklyMasterCallReport]
--
As
Declare @DateNow DateTime,
@Date5 DateTime
--set date to be yesterday
--SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE() - 1), 0)
--set date to be yesterday
Set @DateNow = (SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE() - 1), 0))
--set date -5
Set @Date5 =(SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE() - 5), 0))
Exec TempAreaCodes --(This is the procedure Jeff M gave me)
--select distinct gtcalcdate from cdrmain where gtcalcdate between @Date7 and @DateNow
SELECT
Daily.GtCalcDate,
Callers.ClCallerName AS CallerNameID,
Callers.ClCallerNum,
Daily.TotLDCallsPerDay,
--More Columns.....
FROM
--Get Callers
(SELECT DISTINCT CallerName AS ClCallerName,CallerNum as ClCallerNum
FROM CDRMAIN
WHERE (CDRMAIN_4.CallerName = 'Caller3') OR
(CDRMAIN_4.CallerName = 'Caller2' OR
(CDRMAIN_4.CallerName = 'Caller3'))
) AS Callers INNER JOIN
(SELECT *
FROM #MyHead WHERE GroupRank = 1 )As DailyArea on DailyArea.CallerName = Callers.ClCallerName
Inner Join
Inner Join
--get daily local calls
(SELECT CDRMAIN_4.CallerName, CDRMAIN_4.GtCalcDate,SUM(TalkDuration) / 60 AS dylocTalkTimeTot,
SUM(CASE WHEN Direction = '2' AND LEN(Targetnum) >=7 AND LEN(Targetnum) =7 AND LEN(Targetnum) = 7 AND LEN(Targetnum) ='2006-07-01' and (LEN(TargetNum) > 6) AND (LEN(TargetNum) < 11) AND (DATEPART(Year, GtCalcDate) = DatePart(Year,@DateNow))
GROUP BY DATEPART(Year, GtCalcDate), CallerName
HAVING (CDRMAIN_4.CallerName = 'Caller3') OR
(CDRMAIN_4.CallerName = 'Caller2' OR
(CDRMAIN_4.CallerName = 'Caller3')) AS YearLoc ON YearLoc.yrlocCallerName = Callers.ClCallerName
GROUP BY Callers.ClCallerName, Daily.GtCalcDate,callers.ClCallerNum, Monthly.mnTotLDCallsPerMonth, Monthly.mnYear, Monthly.mnMonth, Yearly.yrTotLDCallsPerYear, Monthly.mnBusDayCount,
Yearly.yeardaycount, Daily.TotLDCallsPerDay, YearLoc.yrTotLocalCallsPerYear, YearLoc.yrlocTalkTimeTot,monthly.mnLDTalkTimeTot,yearly.yrLDTalkTimeTot
,Daily.dyLDTalkTimeTot,dailyloc.dyTotLocCallsPerDay
ORDER BY Callers.ClCallerName,daily.gtcalcdate,Monthly.mnYear, Monthly.mnMonth
November 9, 2006 at 6:16 am
I'm assuming that the proc you made from my code example creates a temp table within it... that won't work because of the scope of temp tables... you have to (at least) create the temp table in your existing procedure clear code. You might also want to add a couple of parameters to the new proc for date filters...
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply