January 28, 2009 at 2:01 pm
I'm trying to compile the stored proc below but keep getting this error msg: Must declare the scalar variable "@RunParm".
Hopefully my code below makes sense to all. Any ideas?
--execute dbo.rs_OTM_DynamicHeadersTable
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Procedure [dbo].[rs_OTM_Daily_Campaign_Log]
(
@DateBegin smalldatetime = NULL
,@DateEnd smalldatetime = NULL
,@RunParm varchar(1) = NULL
,@CampaignId varchar(5000) = NULL
)
AS
--Declare @DateBeginJ int,
--@DateEndJ int
SET NOCOUNT ON
-- Clean CampaignId Input Line
-- Declare @CampaignId varchar(5000)
Set @CampaignId = Replace(@CampaignId,' ','-')
Set @CampaignId = Replace(@CampaignId,',','-')
Set @CampaignId = Replace(@CampaignId,'--','-')
Set @CampaignId = '-' + @CampaignId + '-'
-- Drop Table #CampaignList
Select *
Into#CampaignList
FromLifeExtension_PROD.dbo.otmCampaigns
Where(Charindex('-'+ltrim(rtrim(CampaignId))+'-',isnull(@CampaignId,'')) > 0 or
@CampaignId is null)
-- select * from #CampaignList
BEGIN
IF @RunParm = 'R'-- Month to Date
BEGIN
SET @DateBegin = convert(smalldatetime,convert(varchar,GetDate() - DatePart(dd,Getdate()) + 1,101))
SET @DateEnd = convert(smalldatetime,convert(varchar,GetDate(),101))
END
IF @RunParm = 'M'-- Prior Month
BEGIN
Declare @CurrentMonthStart smalldatetime
set @CurrentMonthStart = convert(smalldatetime,convert(varchar, GetDate()- (DATEPART(dd,GetDate()))+ 1, 101))
Set @DateBegin = dateadd(mm,-1,@CurrentMonthStart)
set @DateEnd = dateadd(mm,0,@CurrentMonthStart)-1
END
IF @RunParm = 'L'--Lifetime
BEGIN
SET @DateBegin = (Select Min(Convert(smalldatetime, Convert(varchar,(CallDate),101))) From LifeExtension_PROD.dbo.otmCampaignCallLog)
SET @DateEnd = (Select Max(Convert(smalldatetime, Convert(varchar, (CallDate),101))) From LifeExtension_PROD.dbo.otmCampaignCallLog)
END
ELSE
BEGIN-- Select Dates
SET @DateBegin = Convert(smalldatetime, Convert(varchar, @DateBegin, 101))
SET @DateEnd = Convert(smalldatetime, Convert(varchar, @DateEnd, 101))
END
END
--Set @DateBeginJ = dbo.converttojulian(@DateBegin)
--Set @DateEndJ = dbo.converttojulian(@DateEnd)
--DROP TABLE #temp1
--DROP TABLE #temp1a
--DROP TABLE #temp2
--DROP TABLE #temp3
--DROP TABLE #temp4
--select * from #temp1
--select * from #temp1a
--select * from #temp2
--select * from #temp3
--select * from #temp4
----------------------------------------------------------------
---------TEMP TABLE # 1 WITH TARGET COUNTS------------
----------------------------------------------------------------
Select CampaignID,
Target = Count(distinct CustomerNumber),
ActiveCustomers = Count(distinct Case When Active = 'Y' then CustomerNumber End),
NotValidType = Count(distinct Case When NotType = 'Y' Then CustomerNumber End),
PreferenceChange = Count(distinct Case When NotPref = 'Y' Then CustomerNumber End)
Into #temp1
From (
Select A.*,
--B.abac01,
--C.CustomerType,
Active= (Case When A.Status in ('A','S') Then 'Y' Else 'N' End),
NotType= (Case When abac01 not like CustomerType and A.Status = 'A' Then 'Y' Else 'N' End),
NotPref= (Case When (D.RemoveAllContact = 1 or D.PhonePromo = 0) and A.Status = 'A' Then 'Y' Else 'N' End)
FromLifeExtension_PROD.dbo.otmCampaignTarget A
LeftOuter
JoinJDE_PRODUCTION.dbo.f0101 B
onCustomerNumber = aban8
LeftOuter
JoinLifeExtension_PROD.dbo.otmCampaigns C
onA.CampaignID = C.CampaignID
LeftOuter
JoinLifeExtension_PROD.dbo.cmnCustomerPreferences D
onD.CustomerNumber = a.CustomerNumber
) X
WhereCampaignId in (Select CampaignId from #CampaignList)
Group By CampaignID
-- 3:34
----------------------------------------------------------------
---------TEMP TABLE # 2 WITH DAILY COUNTS------------
----------------------------------------------------------------
Select CampaignID,
Campaign = Description,
CallDate,
--CallBy,
TotalCalls = Count(CustomerNumber),
Agreed_Accepted = Count(Case When Accepted = 'Y' Then CustomerNumber End),
Refused_Rejected = Count(Case When Refused = 'Y' Then CustomerNumber End),
DoNotContact = Count(Case When DontContact = 'Y' Then CustomerNumber End),
Busy_Retry = Count(Case When Busy = 'Y' Then CustomerNumber End),
No_Answer = Count(Case When NoAnswer = 'Y' Then CustomerNumber End),
Bad_Number = Count(Case When BadNumber = 'Y' Then CustomerNumber End),
Call_Back = Count(Case When CallBack = 'Y' Then CustomerNumber End),
LeftMessage = Count(Case When LeftMsg = 'Y' Then CustomerNumber End)
Into #temp2
From (
Select A.CampaignID,
c.Description,
--B.abac01,
--C.CustomerType,
CallDate= convert(smalldatetime,convert(varchar,d.CallDate,101)),
--d.CallBy,
d.CustomerNumber,
Accepted= (Case When d.DispositionId = 'ACC' Then 'Y' Else 'N' End),
Refused= (Case When d.DispositionId = 'NTS' Then 'Y' Else 'N' End),
DontContact = (Case When d.DispositionId = 'DNC' Then 'Y' Else 'N' End),
Busy= (Case When d.DispositionId = 'BRT' Then 'Y' Else 'N' End),
NoAnswer= (Case When d.DispositionId = 'NAN' Then 'Y' Else 'N' End),
BadNumber= (Case When d.DispositionId = 'BNO' Then 'Y' Else 'N' End),
CallBack= (Case When d.CampaignId = 'DRLIST' And d.DispositionId = 'BRT' Then 'Y'
When d.DispositionId = 'CLB' Then 'Y' Else 'N' End),
LeftMsg= (Case When d.DispositionId = 'MSG' Then 'Y' Else 'N' End)
FromLifeExtension_PROD.dbo.otmCampaignTarget A
LeftOuter
JoinLifeExtension_PROD.dbo.otmCampaigns C
onA.CampaignID = C.CampaignID
left outer
JoinLifeExtension_PROD.dbo.otmCampaignCallLog d
ond.CustomerNumber = a.CustomerNumber
Whered.DispositionId <> 'SKP'
--Inner
--JoinotmCampaignCallLog e
--one.CampaignId = a.CampaignId
) X
Where CallDate is not null
AndCampaignId in (Select CampaignId from #CampaignList)
Group By CampaignID, Description, CallDate --, CallBy
-- 00:02
----------------------------------------------------------------
---------TEMP TABLE # 3 WITH DAILY REMOVALS------------
----------------------------------------------------------------
select a.campaignid, convert(smalldatetime,convert(varchar,LastCall,101)) as DateRemoved, count(a.CustomerNumber) as Removed
Into#temp3
from
LifeExtension_PROD.dbo.otmcampaigntarget a
inner join
LifeExtension_PROD.dbo.otmdispositions b
on
a.LastDispositionId = b.DispositionId
and
a.CampaignId = b.CampaignId
wherea.Status = 'i' and b.CallAction = 'r'
Anda.CampaignId in (Select CampaignId from #CampaignList)
group by a.campaignid, convert(smalldatetime,convert(varchar,LastCall,101))
-- 00:01
----------------------------------------------------------------
---------TEMP TABLE # 4 WITH JDE SALES------------
----------------------------------------------------------------
Select
Rep,
CampaignId,
TtlOrders= count(TtlOrders),
TtlUnits= sum(TtlUnits),
TtlSales= sum(TtlSales),
MktCode,
SaleDate
Into #temp4
From
(select distinct
Rep= sdtorg,
CampaignId= b.CampaignId,
TtlOrders= sddoco,
TtlUnits= sdsoqs,
TtlSales= sdaexp/100,
MktCode= a.MarketingCode,
SaleDate= dbo.convjulian2(sdtrdj)
from
JDE_PRODUCTION.dbo.f42119(nolock)
inner join
LifeExtension_PROD.dbo.ordOrderItemInformation a
onsddoco = a.OrderNumber
and sddcto = a.DocumentType
andsdkcoo = a.CompanyNumber
inner join
LifeExtension_PROD.dbo.otmCampaignTarget b
onsdan8 = CustomerNumber
inner join
LifeExtension_PROD.dbo.OtmCampaigns c
onltrim(rtrim(a.MarketingCode)) = ltrim(rtrim(c.MarketingCode))
and ReportRevenue = 1
and (ItemNo is null or sdlitm like ItemNo)
andb.CampaignId = c.CampaignId
where
sdtrdj > 108152
andsdlnty in ('S','M','G','LA')
andsdlttr <> '980'
and sddcto = 'SO'
and b.CampaignId in (Select CampaignId from #CampaignList)) sls
Group by Rep, CampaignId, MktCode, SaleDate
order by Rep, MktCode, SaleDate, CampaignId
-- 2:25
--select * from LifeExtension_PROD.dbo.OtmCampaigns
----------------------------------------------------------------
-------CREATE TABLE # 5 TEMP CAMPAIGNS TABLE----
----------------------------------------------------------------
select
CampaignId,
Description,
convert(smalldatetime,convert(varchar,StartDate,101)) as StartDate,
convert(smalldatetime,convert(varchar,isnull(EndDate,getdate()+1),101)) as EndDate,
MarketingCode,
ItemNo,
ReportRevenue
Into#temp1a
FromLifeExtension_PROD.dbo.otmCampaigns
-- select * from #temp1a
-- 00:00
----------------------------------------------------------------
-------CREATE HEADERS TABLE FROM DISPOSITIONS----
----------------------------------------------------------------
Truncate table rs_OTMHeadersTable
InsertInto rs_OTMHeadersTable (CampaignID)
Selectdistinct
CampaignId
Fromlifeextension_prod.dbo.otmdispositions
WhereCampaignId is not null
Go
-- 8
Updaters_OTMHeadersTable
SetACC = b.Description
Fromrs_OTMHeadersTable a
LeftJoin
lifeextension_prod.dbo.otmdispositions b
Ona.CampaignId = b.CampaignId
Whereb.DispositionId = 'ACC'
-- 8
Updaters_OTMHeadersTable
SetBNO = b.Description
Fromrs_OTMHeadersTable a
LeftJoin
lifeextension_prod.dbo.otmdispositions b
Ona.CampaignId = b.CampaignId
Whereb.DispositionId = 'BNO'
-- 8
Updaters_OTMHeadersTable
SetBRT = b.Description
Fromrs_OTMHeadersTable a
LeftJoin
lifeextension_prod.dbo.otmdispositions b
Ona.CampaignId = b.CampaignId
Whereb.DispositionId = 'BRT'
-- 8
Updaters_OTMHeadersTable
SetCLB = b.Description
Fromrs_OTMHeadersTable a
LeftJoin
lifeextension_prod.dbo.otmdispositions b
Ona.CampaignId = b.CampaignId
Whereb.DispositionId = 'CLB'
-- 3
Updaters_OTMHeadersTable
SetDNC = b.Description
Fromrs_OTMHeadersTable a
LeftJoin
lifeextension_prod.dbo.otmdispositions b
Ona.CampaignId = b.CampaignId
Whereb.DispositionId = 'DNC'
-- 8
Updaters_OTMHeadersTable
SetLTR = b.Description
Fromrs_OTMHeadersTable a
LeftJoin
lifeextension_prod.dbo.otmdispositions b
Ona.CampaignId = b.CampaignId
Whereb.DispositionId = 'LTR'
-- 1
Updaters_OTMHeadersTable
SetMSG = b.Description
Fromrs_OTMHeadersTable a
LeftJoin
lifeextension_prod.dbo.otmdispositions b
Ona.CampaignId = b.CampaignId
Whereb.DispositionId = 'MSG'
-- 2
Updaters_OTMHeadersTable
SetNAN = b.Description
Fromrs_OTMHeadersTable a
LeftJoin
lifeextension_prod.dbo.otmdispositions b
Ona.CampaignId = b.CampaignId
Whereb.DispositionId = 'NAN'
-- 8
Updaters_OTMHeadersTable
SetNTS = b.Description
Fromrs_OTMHeadersTable a
LeftJoin
lifeextension_prod.dbo.otmdispositions b
Ona.CampaignId = b.CampaignId
Whereb.DispositionId = 'NTS'
-- 7
Updaters_OTMHeadersTable
SetSKP = b.Description
Fromrs_OTMHeadersTable a
LeftJoin
lifeextension_prod.dbo.otmdispositions b
Ona.CampaignId = b.CampaignId
Whereb.DispositionId = 'SKP'
-- 7
-- select * from rs_OTMHeadersTable
----------------------------------------------------------------
-------JOIN ALL TABLES----
----------------------------------------------------------------
Select distinct
a.CampaignID,
ACC= isnull(f.ACC,''),
BNO= isnull(f.BNO,''),
BRT= isnull(f.BRT,''),
CLB= isnull(f.CLB,''),
DNC= isnull(f.DNC,''),
LTR= isnull(f.ACC,''),
MSG= isnull(f.MSG,''),
NAN= isnull(f.NAN,''),
NTS= isnull(f.NTS,''),
SKP = isnull(f.SKP,''),
a.Target,
a.ActiveCustomers,
a.NotValidType,
a.PreferenceChange,
b.CampaignID,
b.Campaign,
b.CallDate,
b.TotalCalls,
b.Agreed_Accepted,
b.Refused_Rejected,
b.DoNotContact,
b.Busy_Retry,
b.No_Answer,
b.Bad_Number,
b.Call_Back,
b.LeftMessage,
isnull(c.campaignid,'') as CampaignId,
c.DateRemovedas DateRemoved,
isnull(c.Removed,0) as Removed,
sum(e.TtlSales) as TtlSales,
ReportRevenue= Case When d.ReportRevenue = 1 Then 'Yes' Else 'No' End,
RunParm= @RunParm,
BeginningDate= @DateBegin,
EndingDate= @DateEnd
from
#temp1 a
inner join
#temp2 b
ona.campaignid = b.campaignid
inner join
#temp1a d
onb.campaignid = d.campaignid
andconvert(smalldatetime,convert(varchar,b.CallDate,101)) between d.StartDate and d.EndDate
left join
#Temp3 c
onb.campaignid = c.campaignid
and b.CallDate = c.DateRemoved
left join
#temp4 e
on--b.CallBy = e.Rep
b.CallDate = e.SaleDate
andd.MarketingCode = e.MktCode
andb.campaignid = e.campaignid
Left Join
rs_OTMHeadersTable f
on b.campaignid = f.campaignid
where
CallDate between @DateBegin and @DateEnd
--Anda.CampaignId in (Select CampaignId from #CampaignList)
Group by a.CampaignID,
f.ACC, f.BNO, f.BRT, f.CLB, f.DNC, f.LTR, f.MSG, f.NAN, f.NTS, f.SKP,
a.Target, a.ActiveCustomers, a.NotValidType, a.PreferenceChange, b.CampaignID,
b.Campaign, b.CallDate, b.TotalCalls, b.Agreed_Accepted, b.Refused_Rejected, b.DoNotContact,
b.Busy_Retry, b.No_Answer, b.Bad_Number, b.Call_Back, b.LeftMessage, c.campaignid, c.DateRemoved,
c.Removed, d.ReportRevenue
January 28, 2009 at 2:15 pm
You have a GO statement after the
Insert Into rs_OTMHeadersTable (CampaignID)
Select distinct
CampaignId
From lifeextension_prod.dbo.otmdispositions
Where CampaignId is not null
This signals the end of the batch. Comment it out or delete it and you'll be fine
Francis
January 28, 2009 at 2:18 pm
I had to be something that simple, right?
Thanks a lot, it's working now.
John
January 28, 2009 at 2:29 pm
no problem. Sometimes all it takes is a second set of eyes
Francis
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply