Solved

SQL 2000 + converting a funtion to a query

Posted on 2008-10-14
1
158 Views
Last Modified: 2012-08-14
For performance reasons...I need to change a user defined function into a query or stored procedure call...preferably something more simple if possible.  There are two functions.  I am attaching both.  I really only need this to be a select/subselect type of query if possible.

Thank you!
---function 1
 
 

CREATE FUNCTION [dbo].[OnyxAvgCompWorkorderEqpCost] 

(

	-- Add the parameters for the function here

	@projectID varchar(20)

)

RETURNS varchar(20)

AS

BEGIN

	-- Declare the return variable here

	DECLARE @cost varchar(20),

			@total money,

			@comp decimal(5,2)
 

	-- Add the T-SQL statements to compute the return value here

	SELECT     @total=sum(sl.EXTDCOST)

    FROM        cms..SVC00200 sc join  cms.dbo.SVC00203 AS sl on sc.callnbr=sl.callnbr

				INNER JOIN dbo.OnyxProjectSvcCallList AS osl ON sl.CALLNBR = osl.SvcCallNumber 

				INNER JOIN dbo.OnyxProjectSiteList AS opsl ON osl.SvcCall2ProjectSite = opsl.objectid 

				INNER JOIN dbo.OnyxProjectHeader AS ph2 ON opsl.SiteList2Project = ph2.objectid

    WHERE     ph2.ProjectID = @projectid and sc.srvstat>='70C' AND(sl.LINITMTYP = 'P') AND (sl.ITEMUSETYPE = 'I' OR

                           sl.ITEMUSETYPE = 'C') and QTYSOLD>0
 

	select @comp= count(SSsc.objectid) from onyxprojectsitelist SSpsl join onyxprojectheader SSph on SSpsl.sitelist2project=SSph.objectid

				join OnyxProjectSvcCallList SSsc on SSsc.svcCall2projectsite=SSpsl.objectid

				join cms..SVC00200 SSc on SSsc.SvcCallNumber=SSc.callnbr

	where SSph.projectid=@projectid and SSc.SRVSTAT>='70C'
 

	if @comp>0 begin

		select @cost=convert(varchar(20),convert(money,@total/@comp),1)

	end else begin

		set @cost='0'

	end

	-- Return the result of the function

	RETURN @cost
 

END
 
 
 

----function 2
 
 

CREATE FUNCTION [dbo].[OnyxAvgCompWorkorderLaborCost] 

(

	-- Add the parameters for the function here

	@projectID varchar(20)

)

RETURNS varchar(20)

AS

BEGIN

	-- Declare the return variable here

	DECLARE @cost varchar(20),

			@total money,

			@comp decimal(5,2)
 

	-- Add the T-SQL statements to compute the return value here

	SELECT     @total=sum(sl.EXTDCOST)

    FROM        cms..SVC00200 sc join  cms.dbo.SVC00203 AS sl on sc.callnbr=sl.callnbr

				INNER JOIN dbo.OnyxProjectSvcCallList AS osl ON sl.CALLNBR = osl.SvcCallNumber 

				INNER JOIN dbo.OnyxProjectSiteList AS opsl ON osl.SvcCall2ProjectSite = opsl.objectid 

				INNER JOIN dbo.OnyxProjectHeader AS ph2 ON opsl.SiteList2Project = ph2.objectid

    WHERE     ph2.ProjectID = @projectid and sc.srvstat>='70C' AND(sl.LINITMTYP = 'A') AND QTYSOLD>0
 

	select @comp= count(SSsc.objectid) from onyxprojectsitelist SSpsl join onyxprojectheader SSph on SSpsl.sitelist2project=SSph.objectid

				join OnyxProjectSvcCallList SSsc on SSsc.svcCall2projectsite=SSpsl.objectid

				join cms..SVC00200 SSc on SSsc.SvcCallNumber=SSc.callnbr

	where SSph.projectid=@projectid and SSc.SRVSTAT>='70C'
 

	if @comp>0 begin

		select @cost=convert(varchar(20),convert(money,@total/@comp),1)

	end else begin

		set @cost='0'

	end

	-- Return the result of the function

	RETURN @cost
 

END

Open in new window

0
Comment
Question by:Robb Hill
1 Comment
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 22718604
the first one

select case when t2.comp > 0 then convert(varchar(20),convert(money,total/comp),1)
            else '0' end as cost
from
(      SELECT     sum(sl.EXTDCOST) as total
    FROM        cms..SVC00200 sc join  cms.dbo.SVC00203 AS sl on sc.callnbr=sl.callnbr
                        INNER JOIN dbo.OnyxProjectSvcCallList AS osl ON sl.CALLNBR = osl.SvcCallNumber
                        INNER JOIN dbo.OnyxProjectSiteList AS opsl ON osl.SvcCall2ProjectSite = opsl.objectid
                        INNER JOIN dbo.OnyxProjectHeader AS ph2 ON opsl.SiteList2Project = ph2.objectid
    WHERE     ph2.ProjectID = @projectid and sc.srvstat>='70C' AND(sl.LINITMTYP = 'P') AND (sl.ITEMUSETYPE = 'I' OR
                           sl.ITEMUSETYPE = 'C') and QTYSOLD>0
) t1
cross join
(
      select count(SSsc.objectid) as comp
      from onyxprojectsitelist SSpsl join onyxprojectheader SSph on SSpsl.sitelist2project=SSph.objectid
                        join OnyxProjectSvcCallList SSsc on SSsc.svcCall2projectsite=SSpsl.objectid
                        join cms..SVC00200 SSc on SSsc.SvcCallNumber=SSc.callnbr
      where SSph.projectid=@projectid and SSc.SRVSTAT>='70C'
) t2

the second one

select case when t1.comp > 0 then convert(varchar(20),convert(money,total/comp),1)
                  else '0' end as cost
from
(      SELECT     sum(sl.EXTDCOST) as total
    FROM        cms..SVC00200 sc join  cms.dbo.SVC00203 AS sl on sc.callnbr=sl.callnbr
                        INNER JOIN dbo.OnyxProjectSvcCallList AS osl ON sl.CALLNBR = osl.SvcCallNumber
                        INNER JOIN dbo.OnyxProjectSiteList AS opsl ON osl.SvcCall2ProjectSite = opsl.objectid
                        INNER JOIN dbo.OnyxProjectHeader AS ph2 ON opsl.SiteList2Project = ph2.objectid
    WHERE     ph2.ProjectID = @projectid and sc.srvstat>='70C' AND(sl.LINITMTYP = 'A') AND QTYSOLD>0
) t1 cross join
(
      select count(SSsc.objectid) as comp
      from onyxprojectsitelist SSpsl join onyxprojectheader SSph on SSpsl.sitelist2project=SSph.objectid
                        join OnyxProjectSvcCallList SSsc on SSsc.svcCall2projectsite=SSpsl.objectid
                        join cms..SVC00200 SSc on SSsc.SvcCallNumber=SSc.callnbr
      where SSph.projectid=@projectid and SSc.SRVSTAT>='70C'
) t2
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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.
Viewers will learn how the fundamental information of how to create a table.

747 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

12 Experts available now in Live!

Get 1:1 Help Now