Solved

SQL 2000 + converting a funtion to a query

Posted on 2008-10-14
1
159 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

896 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

13 Experts available now in Live!

Get 1:1 Help Now