Group By Problem in StoredProcedure

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 2005 Forums

Transact-SQL (2005)

Group By Problem in StoredProcedure

Author

Topic

ABHISEK SUNDHARAM
Starting Member

9 Posts

Posted - 2010-11-13 : 03:10:04

In Source Contain Two Column name Menuname, NextMenuName.In Stored-procedure contain Three Temporary tables(#IntoMEnuCount,#outTomenuCount,#menupairCount) in that Temp table one of the column name as intocount , that intocount Column name contains count(*) of the column name(NextMenuname),like wise #outtomenucount of the temp table count * is there.The #MenuPair temp table Contain some columns in the Soucre table. up-to this coming correctly.at last of SP we Select the data from MenuPair temp tables,and left outer join the intomenucount,outtomenucount temp tables.

javascript:insertsmilie('

') in the SP we group By the Columns finally.Column names: PEriod ,Phone number etc... here values are differntly incremented when compare to source tables.. How to Solve this problem..any Queries is there. Temptables values and final select statement of stored procedures values are vsrying.. How i Compare this,,,????????????????????????????// HELP ME....

ABHI

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-13 : 14:19:41

You'll need to post the code, sample data, and expected output.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ABHISEK SUNDHARAM
Starting Member

9 Posts

Posted - 2010-11-16 : 03:45:15

SOURCE DATA:
COLUMN NAMES:ID ,STREAMID ,OFFEREDTIME ,TERMINATEDTIME ,Menuname,Prevmenuname,NextMenuName,ivrdisconnected,CallerANI,CallerDNI

200 , 160#A31 ,2010-06-01 01:29:59.000,2010-06-01 01:29:59.000,Account,Agent,Agent Transfer,13,9865,9678
221,160#B31,2010-06-01 02:29:59.000,2010-06-01 01:29:59.000,MenuName,Repeat Offer,Account,15,998,997
222,160#c#1,2010-06-01 03:29:59.000,2010-06-01 02:29:59.000,Previousmenu,Account,Agenttransfer,16,997,09

STORED PROCEDURE:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[SSIS_IVRAnalysis_IVR_InsertMenusByTimesMobileNumberMenu2]
(
--DECLARE
@LastID int,
@MaxID int
)
AS
--set @LastID=0
--set @MaxID =20000
BEGIN
SET FMTONLY OFF
SET NOCOUNT ON
DECLARE @ProcessedRows INT --number of rows processed
DECLARE @Offered INT --total call offered
DECLARE @PrimaryMenu varchar(100) -- The first Menu Name in the IVR
-- How many calls came into TO menu
CREATE TABLE #InToMenuCount(
Period Datetime,
CallerANI Varchar(100),
CallerDNIS varchar(100),
MenuName VARCHAR(200),
inToCount INT

)
-- How many calls came out of TO menu
CREATE TABLE #OutToMenuCount(
Period Datetime,
CallerANI Varchar(100),
CallerDNIS varchar(100),
MenuName VARCHAR(200),
OutToCount INT
)
-- Count of calls for each available Menu Pair in IVR
CREATE TABLE #MenuPairCount(
period Datetime,
CallerANI Varchar(100),
CallerDNIS varchar(100),
MenuName VARCHAR(200),
NextMenuName Varchar(200),
PairCount INT
)

SELECT top 1 @PrimaryMenu = MenuNameLevel0 FROM IVRMenu GROUP BY MenuNameLevel0 -- Fetch the first Menu Name in the IVR

select @Offered = count(distinct (Stream_ID) )
from IVRMenu_SPlit
where ID > @LastID and ID <= @MaxID

insert into #InToMenuCount
select
CONVERT(DATETIME,CONVERT(VARCHAR(10),i.TerminatedTime,101)+' '+CASE WHEN DATEPART(mi,i.TerminatedTime) between 0 AND 29 THEN CONVERT(VARCHAR(2),DATEPART(hh,i.TerminatedTime))+':29:59.000' ELSE CONVERT(VARCHAR(2),DATEPART(hh,i.TerminatedTime))+':59:59.000' END) as Period,
i.CallerANI,i.CallerDNIS,i.MenuName, count(*) from IVRMenu_SPlit i
where i.ID > @LastID and i.ID <= @MaxID
and i.PrevMenuName <> ''
group by
CONVERT(DATETIME,CONVERT(VARCHAR(10),i.TerminatedTime,101)+' '+CASE WHEN DATEPART(mi,i.TerminatedTime) between 0 AND 29 THEN CONVERT(VARCHAR(2),DATEPART(hh,i.TerminatedTime))+':29:59.000' ELSE CONVERT(VARCHAR(2),DATEPART(hh,i.TerminatedTime))+':59:59.000' END),
i.MenuName,i.CallerANI,i.CallerDNIS

insert into #OutToMenuCount
select
CONVERT(DATETIME,CONVERT(VARCHAR(10),i.TerminatedTime,101)+' '+CASE WHEN DATEPART(mi,i.TerminatedTime) between 0 AND 29 THEN CONVERT(VARCHAR(2),DATEPART(hh,i.TerminatedTime))+':29:59.000' ELSE CONVERT(VARCHAR(2),DATEPART(hh,i.TerminatedTime))+':59:59.000' END) as Period,
i.CallerANI,i.CallerDNIS,i.MenuName, count(*) from IVRMenu_SPlit i
where i.ID > @LastID and i.ID <= @MaxID
and i.NextMenuName <> ''
group by
CONVERT(DATETIME,CONVERT(VARCHAR(10),i.TerminatedTime,101)+' '+CASE WHEN DATEPART(mi,i.TerminatedTime) between 0 AND 29 THEN CONVERT(VARCHAR(2),DATEPART(hh,i.TerminatedTime))+':29:59.000' ELSE CONVERT(VARCHAR(2),DATEPART(hh,i.TerminatedTime))+':59:59.000' END),
i.MenuName,i.CallerANI,i.CallerDNIS

INSERT into #MenuPairCount
select
CONVERT(DATETIME,CONVERT(VARCHAR(10),TerminatedTime,101)+' '+CASE WHEN DATEPART(mi,TerminatedTime) between 0 AND 29 THEN CONVERT(VARCHAR(2),DATEPART(hh,TerminatedTime))+':29:59.000' ELSE CONVERT(VARCHAR(2),DATEPART(hh,TerminatedTime))+':59:59.000' END) as Period,
i.CallerANI,i.CallerDNIS,i.MenuName, i.NextMenuName, count(*)
from IVRMenu_SPlit i
where i.ID > @LastID and i.ID <= @MaxID
group by
CONVERT(DATETIME,CONVERT(VARCHAR(10),TerminatedTime,101)+' '+CASE WHEN DATEPART(mi,TerminatedTime) between 0 AND 29 THEN CONVERT(VARCHAR(2),DATEPART(hh,TerminatedTime))+':29:59.000' ELSE CONVERT(VARCHAR(2),DATEPART(hh,TerminatedTime))+':59:59.000' END),
i.MenuName, i.NextMenuName,i.CallerANI,i.CallerDNIS

Create NonClustered Index IX_InToMenuCount_Index1 On #InToMenuCount
(Period ASC, CallerANI ASC, CallerDNIS ASC,MenuName ASC)

Create NonClustered Index IX_OutToMenuCount_Index1 On #OutToMenuCount
(Period ASC, CallerANI ASC, CallerDNIS ASC,MenuName ASC)

Create NonClustered Index IX_MenuPairCount_Index1 On #MenuPairCount
(Period ASC, CallerANI ASC, CallerDNIS ASC,MenuName ASC, NextMenuName ASC)

select @ProcessedRows = count(*) from IVRMenu_SPlit
where ID > @LastID and ID <= @MaxID

select A.Period, A.CallerANI,A.CallerDNIS,A.MenuName, A.NextMenuName,
(CASE WHEN A.NextMenuName = @PrimaryMenu THEN (ISNULL(B.inToCount,0)+@Offered) ELSE ISNULL(B.inToCount,0) END) as inToCount,
ISNULL(C.OutToCount,0) as OutToCount,
(CASE WHEN A.MenuName = @PrimaryMenu THEN (ISNULL(D.inToCount,0)+@Offered) ELSE ISNULL(D.inToCount,0) END) as inFromCount,
ISNULL(E.OutToCount,0) as OutFromCount,
ISNULL(A.PairCount,0) as PairCount,
@ProcessedRows as processedrows
from #MenuPairCount A
left outer join #InToMenuCount B on A.NextMenuName = B.MenuName and A.Period = B.Period and A.CallerANI = B.CallerANI and A.CallerDNIS = B.CallerDNIS
left outer join #OutToMenuCount C on A.NextMenuName = C.MenuName and A.Period = C.Period and A.CallerANI = C.CallerANI and A.CallerDNIS = C.CallerDNIS
left outer join #InToMenuCount D on A.MenuName = D.MenuName and A.Period = D.Period and A.CallerANI = D.CallerANI and A.CallerDNIS = D.CallerDNIS
left outer join #OutToMenuCount E on A.MenuName = E.MenuName and A.Period = E.Period and A.CallerANI = E.CallerANI and A.CallerDNIS = E.CallerDNIS
where A.NextMenuName <> ''
order by A.Period,A.MenuName,A.NextMenuName

END

OUTPUT DATAMODEL:

Period ,CALLERANI,CallerDNIS,MenuNAm,Agentname,IntoCount,OutTocount,InfromCount,OutFromCount,PairCount
>>>..

Here the Intocount ,OutTocount ,OutFromCount,InfromCount columns count values increasing , because of groupby. Source offerdtime and terminated time is every minutes. But StoredProcedure We Group by the period for Every Half Hour. In that group Count is increasing.. We cant avoid group by..Because we need every half hour count ,, Help ME

ABHI

ABHISEK SUNDHARAM
Starting Member

9 Posts

Posted - 2010-11-16 : 03:52:11

Stored Procedure:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

--
--ALTER PROCEDURE [dbo].[SSIS_IVRAnalysis_IVR_InsertMenusByTimesMobileNumberMenu]
--(
DECLARE
@LastID int,
@MaxID int
--)
--AS

set @LastID=0
----set @MaxID =20000
select @MaxID= Max(ID) from ivrmenu_split where year(terminatedtime)=2010 and month(terminatedtime)=6 and

day(terminatedtime)=1

BEGIN
SET FMTONLY OFF
SET NOCOUNT ON
DECLARE @ProcessedRows INT --number of rows processed
DECLARE @Offered INT --total call offered
DECLARE @PrimaryMenu varchar(100) -- The first Menu Name in the IVR
-- How many calls came into TO menu

CREATE TABLE #InToMenuCount
(
Period Datetime,
CallerANI Varchar(100),
CallerDNIS varchar(100),
MenuName VARCHAR(200),
nextMenuName varchar(200),
inToCount INT

)
-- How many calls came out of TO menu
CREATE TABLE #OutToMenuCount
(
Period Datetime,
CallerANI Varchar(100),
CallerDNIS varchar(100),
MenuName VARCHAR(200),
NextMenuName Varchar(200),
OutToCount INT
)

CREATE TABLE #OFFERED
(
Period Datetime,
CallerANI VARCHAR(100),
CallerDNIS VARCHAR(200),
offered int

)

-- Count of calls for each available Menu Pair in IVR
CREATE TABLE #MenuPairCount
(
period Datetime,
CallerANI Varchar(100),
CallerDNIS varchar(100),
MenuName VARCHAR(200),
NextMenuName Varchar(200),
PairCount INT
)

SELECT top 1 @PrimaryMenu = MenuNameLevel0 FROM IVRMenu GROUP BY MenuNameLevel0 -- Fetch the first Menu Name in the IVR

select @Offered = count(distinct (Stream_ID) )
from IVRMenu_SPlit
where ID > @LastID and ID <= @MaxID

insert into #InToMenuCount
select
CONVERT(DATETIME,CONVERT(VARCHAR(10),i.TerminatedTime,101)+' '+CASE WHEN DATEPART(mi,i.TerminatedTime) between 0 AND 29 THEN CONVERT(VARCHAR(2),DATEPART(hh,i.TerminatedTime))+':29:59.000' ELSE CONVERT(VARCHAR(2),DATEPART(hh,i.TerminatedTime))+':59:59.000' END) as Period,
i.CallerANI,i.CallerDNIS,i.MenuName,i.NextMenuname ,count(*) from IVRMenu_SPlit i
where i.ID > @LastID and i.ID <= @MaxID
and i.PrevMenuName <> ''
group by
CONVERT(DATETIME,CONVERT(VARCHAR(10),i.TerminatedTime,101)+' '+CASE WHEN DATEPART(mi,i.TerminatedTime) between 0 AND 29 THEN CONVERT(VARCHAR(2),DATEPART(hh,i.TerminatedTime))+':29:59.000' ELSE CONVERT(VARCHAR(2),DATEPART(hh,i.TerminatedTime))+':59:59.000' END),
i.MenuName,i.nextMenuName,i.CallerANI,i.CallerDNIS

insert into #OutToMenuCount
select
CONVERT(DATETIME,CONVERT(VARCHAR(10),i.TerminatedTime,101)+' '+CASE WHEN DATEPART(mi,i.TerminatedTime) between 0 AND 29 THEN CONVERT(VARCHAR(2),DATEPART(hh,i.TerminatedTime))+':29:59.000' ELSE CONVERT(VARCHAR(2),DATEPART(hh,i.TerminatedTime))+':59:59.000' END) as Period,
i.CallerANI,i.CallerDNIS,i.MenuName,I.nextmenuname ,count(*) from IVRMenu_SPlit i
where i.ID > @LastID and i.ID <= @MaxID
and i.NextMenuName <> ''
group by
CONVERT(DATETIME,CONVERT(VARCHAR(10),i.TerminatedTime,101)+' '+CASE WHEN DATEPART(mi,i.TerminatedTime) between 0 AND 29 THEN CONVERT(VARCHAR(2),DATEPART(hh,i.TerminatedTime))+':29:59.000' ELSE CONVERT(VARCHAR(2),DATEPART(hh,i.TerminatedTime))+':59:59.000' END),
i.MenuName,i.NextmenuName,i.CallerANI,i.CallerDNIS

INSERT INTO #OFFERED
select
CONVERT(DATETIME,CONVERT(VARCHAR(10),TerminatedTime,101)+' '+CASE WHEN DATEPART(mi,TerminatedTime) between 0 AND 29 THEN CONVERT(VARCHAR(2),DATEPART(hh,TerminatedTime))+':29:59.000' ELSE CONVERT(VARCHAR(2),DATEPART(hh,TerminatedTime))+':59:59.000' END) as Period,
i.CAllerANI,i.CallerDNIS,count(distinct(STREAM_ID))
from IVRMenu_Split i
where i.ID > @LastID and i.ID<=@MaxID
Group By
CONVERT(DATETIME,CONVERT(VARCHAR(10),TerminatedTime,101)+' '+CASE WHEN DATEPART(mi,TerminatedTime) between 0 AND 29 THEN CONVERT(VARCHAR(2),DATEPART(hh,TerminatedTime))+':29:59.000' ELSE CONVERT(VARCHAR(2),DATEPART(hh,TerminatedTime))+':59:59.000' END),
i.CallerANI,i.CallerDNIS

INSERT into #MenuPairCount
select
CONVERT(DATETIME,CONVERT(VARCHAR(10),TerminatedTime,101)+' '+CASE WHEN DATEPART(mi,TerminatedTime) between 0 AND 29 THEN CONVERT(VARCHAR(2),DATEPART(hh,TerminatedTime))+':29:59.000' ELSE CONVERT(VARCHAR(2),DATEPART(hh,TerminatedTime))+':59:59.000' END) as Period,
i.CallerANI,i.CallerDNIS,i.MenuName, i.NextMenuName, count(*)
from IVRMenu_SPlit i
where i.ID > @LastID and i.ID <= @MaxID
group by
CONVERT(DATETIME,CONVERT(VARCHAR(10),TerminatedTime,101)+' '+CASE WHEN DATEPART(mi,TerminatedTime) between 0 AND 29 THEN CONVERT(VARCHAR(2),DATEPART(hh,TerminatedTime))+':29:59.000' ELSE CONVERT(VARCHAR(2),DATEPART(hh,TerminatedTime))+':59:59.000' END),
i.MenuName, i.NextMenuName,i.CallerANI,i.CallerDNIS

Create NonClustered Index IX_InToMenuCount_Index1 On #InToMenuCount
(Period ASC, CallerANI ASC, CallerDNIS ASC,MenuName ASC)

Create NonClustered Index IX_OutToMenuCount_Index1 On #OutToMenuCount
(Period ASC, CallerANI ASC, CallerDNIS ASC,MenuName ASC)

Create NonClustered Index IX_MenuPairCount_Index1 On #MenuPairCount
(Period ASC, CallerANI ASC, CallerDNIS ASC,MenuName ASC, NextMenuName ASC)

Create NonClustered Index IX_Offered_Index1 On #OFFERED
(Period ASC, CallerANI ASC, CallerDNIS ASC)

select @ProcessedRows = count(*) from IVRMenu_SPlit
where ID > @LastID and ID <= @MaxID

select * from (
select A.Period, A.CallerANI,A.CallerDNIS,A.MenuName ,A.NextMenuName,
(CASE WHEN A.NextMenuName = @PrimaryMenu THEN (ISNULL(B.inToCount,0)+F.Offered) ELSE ISNULL(B.inToCount,0) END) as inToCount,
ISNULL(C.OutToCount,0) as OutToCount,
(CASE WHEN A.MenuName = @PrimaryMenu THEN (ISNULL(D.inToCount,0)+F.Offered) ELSE ISNULL(D.inToCount,0) END) as inFromCount,
ISNULL(E.OutToCount,0) as OutFromCount,
ISNULL(A.PairCount,0) as PairCount,
@ProcessedRows as processedrows
from #MenuPairCount A
left outer join #InToMenuCount B on A.MenuName = B.MenuName and A.NextMenuName=B.NextMenuname and A.Period = B.Period and A.CallerANI = B.CallerANI and A.CallerDNIS = B.CallerDNIS
left outer join #OutToMenuCount C on A.MenuName = C.MenuName and A.Period = C.Period and A.CallerANI = C.CallerANI and A.CallerDNIS = C.CallerDNIS
left outer join #InToMenuCount D on A.MenuName = D.MenuName and A.Period = D.Period and A.CallerANI = D.CallerANI and A.CallerDNIS = D.CallerDNIS
left outer join #OutToMenuCount E on A.MenuName = E.MenuName and A.Period = E.Period and A.CallerANI = E.CallerANI and A.CallerDNIS = E.CallerDNIS
left outer join #Offered F on A.period = F.period and A.CallerANI = F.CallerANI and A.CallerDNIS = F.CallerDNIS
where A.MenuName <> ''
-- group by A.Period,A.CallerANI,A.CallerDNIS,A.MenuName,A.NextMenuName,B.InToCount,C.OutToCount,F.Offered
-- group by A.Period,A.CallerANI,A.CallerDNIS,A.MenuName,A.NextMenuName,B.InToCount,C.OutToCount,F.Offered
--order by A.period,A.MenuName,A.NextMenuName
)
testtable where MenuName = 'Agent Transfer' and Period='2010-06-01 01:29:59.000'
--and Period='2010-06-01 01:29:59.000'
select * from #InToMenuCount where menuname='Agent Transfer' and Period ='2010-06-01 01:29:59.000'
--and Period ='2010-06-01 01:29:59.000'
--
--select * from (EXECUTE SSIS_IVRAnalysis_IVR_InsertMenusByTimesMobileNumberMenu '06/01/2010','06/01/2010')
--EXCEPT
--Select * from InToMenuCount where menuname='Agent transfer'

DROP TABLE #InToMenuCount
DROP TABLE #OutToMenuCount
DROP TABLE #Offered
DROP TABLE #MenupairCount
--select * from vwA
--EXCEPT
--select * from vwB

END

ABHI

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources