Member_2_241474
asked on
Turning a Query into a Function
I have a query that returns 345 records. I converted it to a function, but the function only returns 1 record (the most recent).
The query and function seem exactly thw same to me.
Why would the function only return 1 record?
The query and function seem exactly thw same to me.
Why would the function only return 1 record?
Working Query, returns 345 records:
DECLARE @start DATETIME
DECLARE @end DATETIME
DECLARE @mid INT
SELECT
@Start = '04-06-2009 00:00:00',
@End = '04-06-2009 23:59:59',
@mid = 3
select
m.IDKey,
m.Channel,
w.CTime,
w.CDate,
w.CallDateTime
from
OesData..utESGMarketChannelMap m
left outer join
(
SELECT
h1.ivrs + h2.ivrs as civrs,
CASE WHEN (datename(n, h1.CallDateTime) < 30) THEN
CASE WHEN (datename(hh, h1.CallDateTime) < 10) THEN '0' + datename(hh, h1.CallDateTime)
+ ':00' ELSE datename(hh, h1.CallDateTime) + ':00' END
ELSE CASE WHEN (datename(hh, h1.CallDateTime) < 10) THEN '0' + datename(hh, h1.CallDateTime)
+ ':30' ELSE datename(hh, h1.CallDateTime) + ':30' END
END AS CTime,
h1.CallDateTime,
CONVERT(varchar(50), h1.CallDateTime, 101) + ' ' + DATENAME(dw, h1.CallDateTime) AS CDate
FROM
oesdata..uvTSIBHistory h1
left outer join
oesdata..uvTSIBHistory h2
ON
h1.phonenum = h2.phonenum
and h1.dnis = h2.dnis
and h1.calldatetime < h2.calldatetime
and h1.historyid < h2.historyid
and h2.projectid != 2000
and datediff(ss,(dateadd(ss,h1.connecttime,h1.calldatetime)),h2.calldatetime) < 5
WHERE
h1.calldatetime BETWEEN @Start AND @End
AND h2.calldatetime BETWEEN @Start AND @End
AND h2.ProjectID IN(SELECT projectid FROM uvTSProjects WHERE projectname like '%ESG%')
AND h1.dnis != 2985
AND h2.dnis != 2985
AND h1.projectid = 2000
) as w
on
w.civrs = m.IvrsPath
where m.IDKey = @mid
order by calldatetime desc
Call to function: select * from fnMktCallsHalfHour('04-06-2009', '04-06-2009', 3)
Function that returns only 1 record:
ALTER FUNCTION fnMktCallsHalfHour
(
@StartDate datetime,
@Enddate datetime,
@MIDX int
)
RETURNS @MCHH TABLE (
IDKey Int null,
Channel varchar(50) null,
CTime varchar(50) null,
CDate varchar(50) null,
CallDateTime DateTime null
) AS
BEGIN
DECLARE
@MID int,
@start DATETIME,
@end DATETIME,
@IDKey Int,
@Channel varchar(50),
@CTime varchar(50),
@CDate varchar(50),
@CallDateTime DateTime
set @Start = @StartDate + ' 00:00:00'
set @End = @EndDate + ' 23:59:59'
set @MID=@MIDX
select
@IDKey = m.IDKey,
@Channel= m.Channel,
@CTime =w.CTime,
@CDate = w.CDate,
@CallDateTime = w.CallDateTime
from
OesData..utESGMarketChannelMap m
left outer join
(
SELECT
h1.ivrs + h2.ivrs as civrs,
CASE WHEN (datename(n, h1.CallDateTime) < 30) THEN
CASE WHEN (datename(hh, h1.CallDateTime) < 10) THEN '0' + datename(hh, h1.CallDateTime)
+ ':00' ELSE datename(hh, h1.CallDateTime) + ':00' END
ELSE CASE WHEN (datename(hh, h1.CallDateTime) < 10) THEN '0' + datename(hh, h1.CallDateTime)
+ ':30' ELSE datename(hh, h1.CallDateTime) + ':30' END
END AS CTime,
h1.CallDateTime,
CONVERT(varchar(50), h1.CallDateTime, 101) + ' ' + DATENAME(dw, h1.CallDateTime) AS CDate
FROM
oesdata..uvTSIBHistory h1
left outer join
oesdata..uvTSIBHistory h2
ON
h1.phonenum = h2.phonenum
and h1.dnis = h2.dnis
and h1.calldatetime < h2.calldatetime
and h1.historyid < h2.historyid
and h2.projectid != 2000
and datediff(ss,(dateadd(ss,h1.connecttime,h1.calldatetime)),h2.calldatetime) < 5
WHERE
h1.calldatetime BETWEEN @Start AND @End
AND h2.calldatetime BETWEEN @Start AND @End
AND h2.ProjectID IN(SELECT projectid FROM uvTSProjects WHERE projectname like '%ESG%')
AND h1.dnis != 2985
AND h2.dnis != 2985
AND h1.projectid = 2000
) as w
on
w.civrs = m.IvrsPath
where
m.IDKey = @MID
INSERT @MCHH
SELECT @IDKey, @Channel, @CTime, @CDate, @CallDateTime;
RETURN;
END;
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
query in function will work like loop, each time you assign
@IDKey = m.IDKey,
@Channel= m.Channel,
@CTime =w.CTime,
@CDate = w.CDate,
@CallDateTime = w.CallDateTime
so each time it overwrite by the recent value and finally when you return it, it will return the most recent value?
is there any specific reason to make function? why don't you make SP?
@IDKey = m.IDKey,
@Channel= m.Channel,
@CTime =w.CTime,
@CDate = w.CDate,
@CallDateTime = w.CallDateTime
so each time it overwrite by the recent value and finally when you return it, it will return the most recent value?
is there any specific reason to make function? why don't you make SP?
> "is there any specific reason to make function? why don't you make SP?"
One reason I can think of, on behalf of the asker, is that they may need to JOIN to it.
One reason I can think of, on behalf of the asker, is that they may need to JOIN to it.
do like this...
create proc testProc (
@start DATETIME,
@end DATETIME,
@mid INT
)
as
begin
select
m.IDKey,
m.Channel,
w.CTime,
w.CDate,
w.CallDateTime
from
OesData..utESGMarketChannelMap m
left outer join
(
SELECT
h1.ivrs + h2.ivrs as civrs,
CASE WHEN (datename(n, h1.CallDateTime) < 30) THEN
CASE WHEN (datename(hh, h1.CallDateTime) < 10) THEN '0' + datename(hh, h1.CallDateTime)
+ ':00' ELSE datename(hh, h1.CallDateTime) + ':00' END
ELSE CASE WHEN (datename(hh, h1.CallDateTime) < 10) THEN '0' + datename(hh, h1.CallDateTime)
+ ':30' ELSE datename(hh, h1.CallDateTime) + ':30' END
END AS CTime,
h1.CallDateTime,
CONVERT(varchar(50), h1.CallDateTime, 101) + ' ' + DATENAME(dw, h1.CallDateTime) AS CDate
FROM
oesdata..uvTSIBHistory h1
left outer join
oesdata..uvTSIBHistory h2
ON
h1.phonenum = h2.phonenum
and h1.dnis = h2.dnis
and h1.calldatetime < h2.calldatetime
and h1.historyid < h2.historyid
and h2.projectid != 2000
and datediff(ss,(dateadd(ss,h1.connecttime,h1.calldatetime)),h2.calldatetime) < 5
WHERE
h1.calldatetime BETWEEN @Start AND @End
AND h2.calldatetime BETWEEN @Start AND @End
AND h2.ProjectID IN(SELECT projectid FROM uvTSProjects WHERE projectname like '%ESG%')
AND h1.dnis != 2985
AND h2.dnis != 2985
AND h1.projectid = 2000
) as w
on
w.civrs = m.IvrsPath
where m.IDKey = @mid
order by calldatetime desc
end
GO
--run like this
exec testProc @Start = '04-06-2009 00:00:00',@End = '04-06-2009 23:59:59',@mid = 3
ASKER
Peter (pivar) was first and correct.
Thanks to all!
Thanks to all!
A correction to my code after looking at your date handling:
set @Start = @StartDate + ' 00:00:00'
set @End = @EndDate + ' 23:59:59'
Should be:
set @Start = dateadd(d, datediff(d, 0, @StartDate),0)
set @End = dateadd(d, datediff(d, 0, @EndDate)+1,0)
And your use of it:
h1.calldatetime BETWEEN @Start AND @End
AND h2.calldatetime BETWEEN @Start AND @End
Should be:
h1.calldatetime >= @Start AND h1.calldatetime < @End
AND h2.calldatetime >= @Start AND h2.calldatetime < @End
It's a more reliable way of doing it since will always include the FULL day of @EndDatetime. Your way would miss everything that occurs in the last second of the day. Would it be a noticeable problem, probably not. But it can happen and it's not correct.
set @Start = @StartDate + ' 00:00:00'
set @End = @EndDate + ' 23:59:59'
Should be:
set @Start = dateadd(d, datediff(d, 0, @StartDate),0)
set @End = dateadd(d, datediff(d, 0, @EndDate)+1,0)
And your use of it:
h1.calldatetime BETWEEN @Start AND @End
AND h2.calldatetime BETWEEN @Start AND @End
Should be:
h1.calldatetime >= @Start AND h1.calldatetime < @End
AND h2.calldatetime >= @Start AND h2.calldatetime < @End
It's a more reliable way of doing it since will always include the FULL day of @EndDatetime. Your way would miss everything that occurs in the last second of the day. Would it be a noticeable problem, probably not. But it can happen and it's not correct.
ASKER
BTW - could not use proc as need to use results in view.
"Peter (pivar) was first and correct."
While true, we posted at nearly the same time, both providing a good explanation and working code. :)
While true, we posted at nearly the same time, both providing a good explanation and working code. :)
Try:
Open in new window