Greetings again experts,
I have a request for a slight modification of this stored procedure.
As you can see from the code below, we are using dynamic WHERE clause.
This way, a user can search by one or more search parameters.
All search params produce results except the date searches(Start tme and end time).
Date format or starttime and endtime is in the following format:
year-month-day hour:minutes:seconds.
Example:
2015-01-01 14:37:34
Each time I perform a search, I get no records found.
Any ideas what I need to modify on the WHERE clause to get desired results when searching with date range of between startttime and endttime?
Many thanks in advance
ALTER PROCEDURE [dbo].[spGetCalls]
@uuid varchar(50),
@callerlist varchar(50),
@phone varchar(50),
@start varchar(150),
@Endd varchar(100),
@calltype varchar(100)
AS
BEGIN
SELECT
qp.phone_number,
callerid,
qp.call_list_id,
qp.lastbridged,
qp.startttime,
camapign_id,
q.queueName,
c.name as call_type,
connecttime,
qp.endtime,
CONVERT(TIME(0), DATEADD(SS,qp.duration,0),108) as duration,
CONVERT(TIME(0), DATEADD(SS,qp.fullduration,0),108) as fullduration,
ca.campName,
ct.call_termination_type,
qp.roll_id,
(e.first_name +' '+ e.last_name) employee,
i.name as cause_code,
box_id,
trunk_name,
qp.uuid,
customer_id,
s.name as telephonyservers
FROM cti.qpcdr qp
inner join cti.call_types c on qp.call_type_id = c.id
inner join cti.queues q on qp.queue_id = q.queueId
inner join cti.campaign ca on qp.camapign_id = ca.campaign_id
inner join cti.isdn_cause_code i On qp.cause_code = i.cause_code
inner join cti.employee e on qp.employee_id = e.employee_id
inner join cti.call_history ch On qp.call_list_id = ch.call_list_id
inner join cti.call_list cl on qp.call_list_id = cl.call_list_id
inner join cti.call_history_qpcdr_link cq On cq.call_history_id = ch.call_history_id
inner join cti.call_terminations ct On ch.call_termination_id = ct.call_termination_id
LEFT JOIN cti.server_settings ss ON ss.value = qp.box_id AND ss.attribute = 'asterisk_box_id'
LEFT JOIN cti.servers s ON s.id = ss.server_id
WHERE 1 = 1
AND ((@uuid IS NULL OR qp.uuid Like '%' + @uuid + '%')
AND (@callerlist IS NULL OR qp.call_list_id Like '%' + @callerlist + '%')
AND (@phone IS NULL OR cl.phone_number Like '%' + @phone + '%')
AND (@start IS NULL OR qp.startttime Like '%' + @start + '%')
AND (@Endd IS NULL OR qp.endtime Like '%' + @Endd + '%')
AND (@calltype IS NULL OR c.id Like '%' + @calltype + '%')
AND cl.phone_number IS NOT NULL)
END