Solved

SQL 2000 + converting a funtion to a query

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
create an aggregate function 9 36
Increment column based of a FK 8 23
Proper Case SQL Command 2 13
average of calculation (TSQL) 4 10
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

832 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