Solved

Turning a Query into a Function

Posted on 2009-04-08
9
210 Views
Last Modified: 2012-05-06
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?
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

Open in new window

0
Comment
Question by:jriggin
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 500 total points
ID: 24096954
Hi,

When you assign result from a select to variables you only get one row.

Do like this instead:


/peter
INSERT INTO @MCHH

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

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24096961
That's because you are only assigning one value to variables, then inserting the variables into the function.

Try:

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

 

INSERT @MCHH(IDKey,Channel,CTime,CDate,CallDateTime)

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

 
 
 

RETURN;

END;

GO

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24096978
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?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24097012
> "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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24097036
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

Open in new window

0
 
LVL 1

Author Closing Comment

by:jriggin
ID: 31568013
Peter (pivar) was first and correct.

Thanks to all!  
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24097166
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.
0
 
LVL 1

Author Comment

by:jriggin
ID: 24097172
BTW - could not use proc as need to use results in view.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24097201
"Peter (pivar) was first and correct."

While true, we posted at nearly the same time, both providing a good explanation and working code. :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now