I'd be inclined to make both of those in-line UDFs, rather than multi-statement UDFs:
DROP FUNCTION f_PendingDCDetails;
GO
CREATE FUNCTION f_PendingDCDetails
(
@SID Varchar(32),
@iFID varchar(32),
@iTID varchar(32),
@CID varchar(32),
@EnDate varchar(11),
@iWR tinyint,
@iLoc varchar(8),
@iScrVNo varchar(80),
@iVtp varchar(20),
@iSubType varchar(8),
@iFinancialYear varchar(8),
@iMnth varChar(8),
@ivYear varchar(8),
@iLocation varchar(8),
@iForLocation varchar(8),
@iAorB varchar(1),
@iVNo integer,
@iSrNo integer,
@ForWhichForm varchar(2)
)
Returns Table
As
Return
(
Select
Max(T.QueryNo) QueryNo,
Max(T.ScrVoucher_No) ScrVoucher_No,
Max(T.Vtp) Vtp,
Max(T.SubType) SubType,
Max(T.FinancialYear) FinancialYear,
Max(T.Mnth) Mnth,
Max(T.vYear) vYear,
Max(T.Location) Location,
Max(T.ForLocation) ForLocation,
Max(T.AorB) AorB,
Max(T.vno) vno,
Max(T.srno) srno,
Max(T.dated) dated,
Max(T.Item_ID) Item_ID,
Sum(T.Delivered) Delivered,
Sum(T.Returned) Returned,
Sum(T.Invoiced) Invoiced,
Sum(T.Rate) Rate,
Max(T.Remarks) Remarks,
Max(T.Project_ID) Project_ID,
Max(T.Client_ID) Client_ID,
Max(T.Emp_ID) Emp_ID,
Max(T.DeliveryChallanNo) DeliveryChallanNo,
Sum(T.GDNDelivered) GDNDelivered,
Max(T.EngineNo) EngineNo,
Max(T.FrameNo) FrameNo,
Max(T.VRegNo) VRegNo,
Max(T.ClaimType) ClaimType,
Max(T.PrincipalCompany_ID) PrincipalCompany_ID,
Sum(T.STPercent) STPercent,
Sum(T.EDPercent) EDPercent,
Sum(T.OTPercent) OTPercent,
Sum(T.DiscP) DiscP,
Sum(T.DiscA) DiscA,
Sum(T.GrossRate) GrossRate,
Sum(T.MainDiscP) MainDiscP,
Max(T.Party_ID) Party_ID,
Max(T.ClientPONo) ClientPONo,
Max(T.ClientPODate) ClientPODate,
Max(T.PaymentTerms) PaymentTerms,
Max(T.Job_ID) Job_ID,
Max(T.CostCenter_ID) CostCenter_ID,
Max(T.BatchNo) BatchNo,
Max(T.ExpiryDate) ExpiryDate,
Max(T.Transporter_ID) Transporter_ID,
Sum(T.BQO) BQO,
Sum(T.BQD) BQD,
Max(T.Gift_ID) Gift_ID,
Sum(T.GQO) GQO,
Sum(T.GQD) GQD,
Max(T.BiltyNo) BiltyNo,
Max(T.BiltyDate) BiltyDate,
Max(T.OldSoftNo) OldSoftNo
From
(
...
) T
Group By
T.VTP,
T.SubType,
T.FinancialYear,
T.Mnth,
T.vYear,
T.Location,
T.ForLocation,
T.AorB,
T.VNo,
T.SrNo,
T.Item_ID
Having
(
@iWR = 4
Or
(@iWR = 0 And Sum(T.Delivered) > Sum(T.Invoiced - T.Returned))
Or
(@iWR = 1 And Aum(T.Delivered) > Sum(T.GDNDelivered))
Or
(@iWR = 2 And Sum(T.Delivered) < Sum(T.Invoiced - T.Returned))
Or
(@iWR = 3 And Sum(T.Delivered) < Sum(T.GDNDelivered))
)
);
DROP FUNCTION f_PendingDC;
GO
CREATE FUNCTION f_PendingDC
(
@SID varchar(32),
@iFID varchar(32),
@iTID varchar(32),
@CID varchar(32),
@EnDate varchar(11),
@iWR tinyint,
@iLoc varchar(8),
@iScrVNo varchar(80),
@iVtp varchar(20),
@iSubType varchar(8),
@iFinancialYear varchar(8),
@iMnth varchar(8),
@ivYear varchar(8),
@iLocation varchar(8),
@iForLocation varchar(8),
@iAorB varchar(1),
@iVNo integer,
@iSrNo integer,
@ForWhichForm varchar(2)
)
Returns Table
As
Return
(
Select
T.ScrVoucher_No ScrVoucher_NoWithSrNo,
T.Vtp VTP,
T.SubType SubType,
T.FinancialYear FinancialYear,
T.Mnth Mnth,
T.vYear vYear,
T.Location Location,
T.ForLocation ForLocation,
T.AorB AorB,
T.VNo VNo,
T.srno SrNo,
T.dated Dated,
T.Client_ID Client_ID,
C.Title Client_Title,
T.Item_ID Item_ID,
IsNull(I.Title, D.Title) Item_Title,
T.Delivered Delivered,
T.Returned Returned,
T.Invoiced Invoiced,
I.Unit Unit,
T.Rate Rate,
Left(T.Remarks, 1000) Remarks,
T.Emp_ID Emp_ID,
ED.Title Emp_Title,
T.DeliveryChallanNo DeliveryChallanNo,
T.GDNDelivered GDNDelivered,
@iWR iWR,
T.EngineNo EngineNo,
T.FrameNo FrameNo,
T.DeliveryChallanNo ScrVoucher_No,
Case When I.ID Is Null Then 'Services' Else 'Parts' End ServiceOrPart,
T.VRegNo VRegNo,
T.ClaimType ClaimType,
CP.ID PrincipalCompany_ID,
CP.Title PrincipalCompany_Title,
T.STPercent STPercent,
T.EDPercent EDPercent,
T.OTPercent OTPercent,
T.DiscP DiscP,
T.DiscA DiscA,
T.GrossRate GrossRate,
T.MainDiscP MainDiscP,
A.ID Party_ID,
A.Title Party_Title,
T.ClientPONo ClientPONo,
T.ClientPODate ClientPODate,
V.ID1 Main_VTP,
T.GrossRate SaleRate,
T.PaymentTerms PaymentTerms,
CA.Terms PaymentTermsCoa,
T.Job_ID Job_ID,
DJ.Title Job_Title,
T.CostCenter_ID CostCenter_ID,
T.Project_ID Project_ID,
CC.Title CostCenter_Title,
DP.Title Project_Title,
T.BatchNo BatchNo,
T.ExpiryDate ExpiryDate,
TT.ID Transporter_ID,
TT.Title Transporter_Title,
Case When MR.VTP Is Null Then I.PackSize Else MR.PackSize End QtyPerCTN,
T.BQO BQO,
T.BQD BQD,
T.Gift_ID Gift_ID,
G.Title Gift_Title,
T.GQO GQO,
T.GQD GQD,
T.BiltyNo BiltyNo,
T.BiltyDate BiltyDate,
T.OldSoftNo OldSoftNo
From
f_PendingDCDetails(@SID, @iFID, @iTID, @CID, @EnDate, @iWR, @iLoc, @iScrVNo, @iVtp, @iSubType, @iFinancialYear, @iMnth, @ivYear, @iLocation, @iForLocation, @iAorB, @iVNo, @iSrNo, @ForWhichForm) T
Left Join Coa31 I on I.id = T.Item_ID
Left Join Coa31 G on G.id = T.Gift_ID
Left Join Coa32 C On T.Client_ID = C.ID
Left Join EmployeeDetails ED On T.Emp_ID = ED.ID
Left Join DefJobs D On T.Item_ID = D.ID
Left Join Coa32 CP On T.PrincipalCompany_ID = CP.ID
Left Join AnalysisCodes A On T.Party_ID = A.ID
Left Join Vtp2 V On T.VTP = V.ID2
Left Join Coa321 CA on CA.ID = IsNull(CP.ID, C.ID)
Left Join DefJobs DJ On DJ.ID = T.Job_ID
Left Join CostCenters CC On CC.ID = T.CostCenter_ID
Left Join DefProjects DP On DP.ID = T.Project_ID
Left Join Coa32 TT On T.Transporter_ID = TT.ID
Left Join ManufacturingOrder_Det M On T.BatchNo = M.BatchNo
Left Join MiscItemRecepie MR On IsNull(M.RefVTP, '') = MR.VTP
And IsNull(M.RefSubType, '') = MR.SubType
And IsNull(M.RefFinancialYear, '') = MR.FinancialYear
And IsNull(M.RefMnth, '') = MR.Mnth
And IsNull(M.RefvYear, '') = MR.vYear
And IsNull(M.RefLocation, '') = MR.Location
And IsNull(M.RefForLocation, '') = MR.ForLocation
And IsNull(M.RefAorB, '') = MR.AorB
And IsNull(M.RefVNo, '') = MR.VNo
);
If it's still slow, use the Index Tuning Wizard[^] to see if it thinks there are any missing indexes.
Also, bear in mind that SQL 2000 is no longer supported by Microsoft. The "Extended Lifecycle Support" ended in April 2013. If possible, you should consider upgrading to a supported version.
NB: You won't be able to upgrade directly to 2012 or higher, since you can't restore a 2000 database on a 2012 instance. Instead, you'll have to go via 2008 R2: Migration SQL Server 2000 to SQL Server 2012[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|