I want to pass a parameter (@date1) in my SSIS OLE DB Source so I created a variable and I tried to pass the parameter using '?' but it's showing 'Syntax error, permission violation or other non-specific error'
I tried like this:
select dateAdd(second, 1, @date1=?) StartTime, --Selecting calls from the next second of last processed time.
convert(datetime, convert(char(19), dateAdd(minute, -1, CURRENT_TIMESTAMP), 120)) EndTime --TRIM to seconds.
But I know I can pass parameter in where condition but I want to Pass it in With Clause. Is it possible to pass parameter in where condition of a with clause?
Full Query:
ALTER PROCEDURE [dbo].[GET_CAll_LEVEL_DETAILS] ( @date1 DateTime )
As
With BACK_LOG_PICK(StartTime, ENDTIME) as
(
select dateAdd(second, 1, @date1) StartTime, --Selecting calls from the next second of last processed time.
convert(datetime, convert(char(19), dateAdd(minute, -1, CURRENT_TIMESTAMP), 120)) EndTime --TRIM to seconds.
--15 mins is considered as max call time. So calls before 15 mins are backloged and selected.
--select '18-mar-2014 18:52:00' StartTime,
--'18-mar-2014 18:54:00' EndTime
),
TCD(RouterCallKeyDay,RouterCallKey,CallStartTime,CallEndTime)
As
(
select RouterCallKeyDay,RouterCallKey
,Min(DateTime) as CallStartTime, Max(DateTime) as CallEndTime from Termination_Call_Detail
where DigitsDialed in('30013900', '30013901')
group by RouterCallKeyDay,RouterCallKey
having Min(DateTime)>=(Select StartTime from BACK_LOG_PICK)
and Min(DateTime) < (Select ENDTIME from BACK_LOG_PICK)
--Any Call started between our interested time is selected, even if the call is not ended in our time interval.
),
TCDRecords (AgentSkillTargetID,SkillGroupSkillTargetID,ServiceSkillTargetID,PeripheralID,RouteID,
RouterCallKeyDay,RouterCallKey,DateTime,PeripheralCallType,DigitsDialed,PeripheralCallKey,
CallDisposition,NetworkTime,Duration,RingTime,DelayTime,TimeToAband,HoldTime,TalkTime,
WorkTime,LocalQTime,CallSegmentTime,ConferenceTime,NetworkTargetID,TrunkGroupID,DNIS,
InstrumentPortNumber,AgentPeripheralNumber,ICRCallKey,ICRCallKeyParent,ICRCallKeyChild,
ANI,AnsweredWithinServiceLevel,Priority,Trunk,CallDispositionFlag,RouterCallKeySequenceNumber,
CED,CallTypeID,BadCallTag,ApplicationTaskDisposition,ApplicationData,NetQTime,CallTypeReportingDateTime,
NetworkSkillGroupQTime,EnterpriseQueueTime) as
(
select AgentSkillTargetID,SkillGroupSkillTargetID,ServiceSkillTargetID,PeripheralID,RouteID,
b.RouterCallKeyDay,b.RouterCallKey,DateTime,PeripheralCallType,DigitsDialed,PeripheralCallKey,
CallDisposition,NetworkTime,Duration,RingTime,DelayTime,TimeToAband,HoldTime,TalkTime,
WorkTime,LocalQTime,CallSegmentTime,ConferenceTime,NetworkTargetID,TrunkGroupID,DNIS,
InstrumentPortNumber,AgentPeripheralNumber,ICRCallKey,ICRCallKeyParent,ICRCallKeyChild,
ANI,AnsweredWithinServiceLevel,Priority,Trunk,CallDispositionFlag,RouterCallKeySequenceNumber,
CED,CallTypeID,BadCallTag,ApplicationTaskDisposition,ApplicationData,NetQTime,CallTypeReportingDateTime,
NetworkSkillGroupQTime,EnterpriseQueueTime from Termination_Call_Detail a , TCD b
where a.RouterCallKey = b.RouterCallKey and a.RouterCallKeyDay = b.RouterCallKeyDay
--and Min(DateTime)>=(Select StartTime from BACK_LOG_PICK)
--and Max(DateTime) < (Select ENDTIME from BACK_LOG_PICK)
),
CallDisposition(RouterCallKey, RouterCallKeyDay, Hangup_Flag) as
(
select RouterCallKey, RouterCallKeyDay,
(
case
when max(CallDisposition) = 52 then 'AD'
else
case
when max(CallDisposition) = 13 then 'CD'
else 'SD'
end
end) as Hangup_Flag
from TCDRecords group by RouterCallKey, RouterCallKeyDay
),
callType1prepare(RouterCallKeyDay,RouterCallKey,Duration, LocalQTime)
As
(
select distinct RouterCallKeyDay,RouterCallKey, MAX(Duration), SUM(LocalQTime) from TCDRecords
WHERE PeripheralCallType =1
group by RouterCallKeyDay,RouterCallKey
),
CallType1PrepareDistinct(RowNumber,RouterCallKeyDay,RouterCallKey,DateTime, DigitsDialed, DNIS,
ANI,CallDisposition,NetworkTime,Duration,RingTime,DelayTime,TimeToAband,HoldTime,
WorkTime,LocalQTime,CallSegmentTime,ConferenceTime,NetworkSkillGroupQTime,EnterpriseQueueTime) as
(
select Row_Number() Over(Partition by b.RouterCallKeyDay,b.RouterCallKey Order By b.RouterCallKeyDay,b.RouterCallKey
desc) As RowNumber,
b.RouterCallKeyDay,b.RouterCallKey, DateTime, DigitsDialed, DNIS, ANI,CallDisposition,NetworkTime,
b.Duration,
RingTime,DelayTime,TimeToAband,
HoldTime,WorkTime,b.LocalQTime,
CallSegmentTime,ConferenceTime as ConferenceTime,
NetworkSkillGroupQTime,EnterpriseQueueTime FROM TCDRecords a, callType1prepare b
WHERE a.RouterCallKeyDay = b.RouterCallKeyDay
and a.RouterCallKey = b.RouterCallKey
and a.Duration = b.Duration
),
CallType1(RouterCallKeyDay,RouterCallKey,DateTime, DigitsDialed, DNIS,
ANI,CallDisposition,NetworkTime,Duration,RingTime,DelayTime,TimeToAband,HoldTime,
WorkTime,LocalQTime,CallSegmentTime,ConferenceTime,NetworkSkillGroupQTime,EnterpriseQueueTime)
AS
(
SELECT RouterCallKeyDay,RouterCallKey,DateTime, DigitsDialed, DNIS, ANI,CallDisposition,NetworkTime,
Duration,
RingTime,DelayTime,TimeToAband,
HoldTime,WorkTime,LocalQTime,
CallSegmentTime,ConferenceTime as ConferenceTime,
NetworkSkillGroupQTime,EnterpriseQueueTime FROM CallType1PrepareDistinct WHERE RowNumber = 1
),
CallType2
(RouterCallKeyDay,RouterCallKey,CallDisposition,AgentSkillTargetID,SkillGroupSkillTargetID,
AgentPeripheralNumber,HoldTime,TalkTime,WorkTime,
DateTime, DigitsDialed, DNIS, ANI)
As
(
SELECT
RouterCallKeyDay,RouterCallKey,CallDisposition,AgentSkillTargetID,SkillGroupSkillTargetID,AgentPeripheralNumber,HoldTime
,TalkTime,WorkTime,
DateTime, DigitsDialed, DNIS, ANI
from TCDRecords WHERE PeripheralCallType =2
)
select
p1.RouterCallKey as RouterCallkey,
p1.RouterCallKeyDay as RouterCallKeyDay,
p1.DateTime ,
AgentPeripheralNumber,
AgentSkillTargetID,
SkillGroupSkillTargetID,
p1.DigitsDialed as DNIS,
p1.ANI,
p2.TalkTime,
p2.HoldTime,
p2.WorkTime,
Duration,
p2.DNIS as Extension,
p1.LocalQTime as LocalQTime,
CD.Hangup_Flag
from CallType1 p1
left outer join CallType2 p2
on p1.RouterCallKey = p2.RouterCallKey
and p1.RouterCallKeyDay = p2.RouterCallKeyDay
left outer join CallDisposition CD
on p1.RouterCallKey = CD.RouterCallKey
and p1.RouterCallKeyDay = CD.RouterCallKeyDay;
I would write it like this:
Or like this
The latter syntax is easier to check in mgt studio