Re: addition having null values in between - Database Discussion Boards

Nasir Abbas5-May-16 7:27

5-May-16 7:27

I have two UDF in SQL Server 2000 

1. f_PendingDCDetails (which gives around 1.3 M records In 3 Minutes)

2. f_PendingDC who uses f_PendingDCDetails functions (which gives 20 pending records but takes 10+ Minutes).

Re: Query Execution Time Management.

Richard Deeming5-May-16 7:34

Richard Deeming

5-May-16 7:34

And?

Apart from the fact that you've forgotten to ask a question, we can't see your database, your data, or the code for the two functions.

Assuming your question was meant to be "how do I improve the performance of the second UDF", there's no way we can answer that with the information you've given us.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Query Execution Time Management.

Nasir Abbas5-May-16 19:41

Nasir Abbas

5-May-16 19:41

Extremely sorry for incomplete question, however I mentioned that I m using SQL Server 2000. Yes you are right my question was

how do I improve the performance of the second UDF

Also I encode/attach my UDFs (I again apologize if I wrongly do the same as I don't know the exact way to attach my code, however, if u will guide I will be care full in near future.

SQL

Alter 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 @T Table (
	QueryNo Varchar (4), 
	ScrVoucher_No Varchar (101), 
	Vtp Varchar (20), 
	SubType Varchar (8), 
	FinancialYear Varchar (8), 
	Mnth Varchar (8), 
	vYear Varchar (8), 
	Location Varchar (8), 
	ForLocation Varchar (8), 
	AorB Varchar (1), 
	vno Varchar (4), 
	srno Varchar (4), 
	dated DateTime, 
	Item_ID Varchar (32), 
	Delivered Double Precision, 
	Returned Integer, 
	Invoiced Double Precision, 
	Rate Double Precision, 
	Remarks Text, 
	Project_ID Varchar (32), 
	Client_ID Varchar (32), 
	Emp_ID Varchar (32), 
	DeliveryChallanNo Varchar (70), 
	GDNDelivered Double Precision, 
	EngineNo Varchar (64), 
	FrameNo Varchar (64), 
	VRegNo Varchar (32), 
	ClaimType Varchar (32), 
	PrincipalCompany_ID Varchar (32), 
	STPercent Double Precision, 
	EDPercent Double Precision, 
	OTPercent Double Precision, 
	DiscP Double Precision, 
	DiscA Double Precision, 
	GrossRate Double Precision, 
	MainDiscP Double Precision, 
	Party_ID Varchar (5), 
	ClientPONo Varchar (200), 
	ClientPODate DateTime, 
	PaymentTerms Varchar (32), 
	Job_ID Varchar (32), 
	CostCenter_ID Varchar (32), 
	BatchNo Varchar (40), 
	ExpiryDate DateTime, 
	Transporter_ID Varchar (32), 
	BQO Double Precision, 
	BQD Double Precision, 
	Gift_ID Varchar (32), 
	GQO Double Precision, 
	GQD Double Precision, 
	BiltyNo Varchar (120), 
	BiltyDate DateTime, 
	OldSoftNo Varchar (32)
)
as
Begin
	Insert @T
	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 (
	Select 1 QueryNo, T.ScrVoucher_No+'/'+Cast(TD.SrNo as Varchar(30)) ScrVoucher_No, TD.Vtp, TD.SubType, TD.FinancialYear, TD.Mnth, TD.vYear, 
		TD.Location, TD.ForLocation, TD.AorB, TD.vno, TD.srno, T.dated, TD.Item_ID, TD.Qty Delivered, 0.00 Returned, 0.00 Invoiced, 
		CD.Rate Rate, ' ' Remarks, O.Project_ID, T.Client_ID, C.Emp_ID Emp_ID, T.ScrVoucher_No DeliveryChallanNo, 0.00 GDNDelivered, 
		TD.EngineNo, TD.FrameNo, '' VRegNo, '' ClaimType, '' PrincipalCompany_ID, C.SalesTaxPercentage STPercent, 0 EDPercent, 0 OTPercent, CD.DiscP, 
		CD.DiscAmt DiscA, CD.GrossRate, 0 MainDiscP, C.Party_ID, C.PONo ClientPONo, C.PO_Date ClientPODate, '' PaymentTerms, 
		TD.Job_ID, O.CostCenter_ID, TD.BatchNo, TD.ExpiryDate, T.Transporter_ID, TD.BonusQty BQO, 0 BQD, TD.Gift_ID, TD.GiftQty GQO, 0 GQD, '' BiltyNo, Null BiltyDate, T.OldSoftNo from DeliveryChallan_Det TD  
		Left Join DeliveryChallan T on TD.VTP=T.VTP and TD.SubType=T.SubType and TD.FinancialYear=T.FinancialYear and TD.Mnth=T.Mnth and TD.vYear=T.vYear and TD.Location=T.Location and TD.ForLocation=T.ForLocation and TD.AorB=T.AorB and TD.VNo=T.VNo
--		Left Join DeliveryOrder_Det DD On IsNull(TD.CO_VTP,'')=DD.VTP and IsNull(TD.CO_SubType,'')=DD.SubType and IsNull(TD.CO_FinancialYear,'')=DD.FinancialYear and IsNull(TD.CO_Mnth,'')=DD.Mnth and IsNull(TD.CO_vYear,'')=DD.vYear and IsNull(TD.CO_Location,'')=DD.Location and IsNull(TD.CO_ForLocation,'')=DD.ForLocation and IsNull(TD.CO_AorB,'')=DD.AorB and IsNull(TD.CO_VNo,IsNull(DD.CO_VNo,''))=DD.VNo and IsNull(TD.CO_SrNo,IsNull(DD.CO_SrNo,''))=DD.SrNo
--		Left Join ClientOrder_Det CD On IsNull(DD.CO_VTP,IsNull(TD.CO_VTP,''))=CD.VTP and IsNull(DD.CO_SubType,IsNull(TD.CO_SubType,''))=CD.SubType and IsNull(DD.CO_FinancialYear,IsNull(TD.CO_FinancialYear,''))=CD.FinancialYear and IsNull(DD.CO_Mnth,IsNull(TD.CO_Mnth,''))=CD.Mnth and IsNull(DD.CO_vYear,IsNull(TD.CO_vYear,''))=CD.vYear and IsNull(DD.CO_Location,IsNull(TD.CO_Location,''))=CD.Location and IsNull(DD.CO_ForLocation,IsNull(TD.CO_ForLocation,''))=CD.ForLocation and IsNull(DD.CO_AorB,IsNull(TD.CO_AorB,''))=CD.AorB and IsNull(DD.CO_VNo,IsNull(TD.CO_VNo,0))=CD.VNo and IsNull(DD.CO_SrNo,IsNull(TD.CO_SrNo,0))=CD.SrNo
		Left Join ClientOrder_Det CD On IsNull(TD.CO_VTP,'')=CD.VTP and IsNull(TD.CO_SubType,'')=CD.SubType and IsNull(TD.CO_FinancialYear,'')=CD.FinancialYear and IsNull(TD.CO_Mnth,'')=CD.Mnth and IsNull(TD.CO_vYear,'')=CD.vYear and IsNull(TD.CO_Location,'')=CD.Location and IsNull(TD.CO_ForLocation,'')=CD.ForLocation and IsNull(TD.CO_AorB,'')=CD.AorB and IsNull(TD.CO_VNo,0)=CD.VNo and IsNull(TD.CO_SrNo,0)=CD.SrNo
		Left Join ClientOrder C On CD.VTP=C.VTP and CD.SubType=C.SubType and CD.FinancialYear=C.FinancialYear and CD.Mnth=C.Mnth and CD.vYear=C.vYear and CD.Location=C.Location and CD.ForLocation=C.ForLocation and CD.AorB=C.AorB and CD.VNo=C.VNo
		Left Join Offer_Det OD On IsNull(CD.Ref_VTP,'')=OD.VTP and IsNull(CD.Ref_SubType,'')=OD.SubType and IsNull(CD.Ref_FinancialYear,'')=OD.FinancialYear and IsNull(CD.Ref_Mnth,'')=OD.Mnth and IsNull(CD.Ref_vYear,'')=OD.vYear and IsNull(CD.Ref_Location,'')=OD.Location and IsNull(CD.Ref_ForLocation,'')=OD.ForLocation and IsNull(CD.Ref_AorB,'')=OD.AorB and IsNull(CD.Ref_VNo,IsNull(OD.Ref_VNo,''))=OD.VNo and IsNull(CD.Ref_SrNo,IsNull(OD.Ref_SrNo,''))=OD.SrNo
		Left Join Offer O On OD.VTP=O.VTP and OD.SubType=O.SubType and OD.FinancialYear=O.FinancialYear and OD.Mnth=O.Mnth and OD.vYear=O.vYear and OD.Location=O.Location and OD.ForLocation=O.ForLocation and OD.AorB=O.AorB and OD.VNo=O.VNo
		Left Join GlobalSettingsNew G On G.ID='COPYGOODSDELIVERYNOTETOSALESINVOICE'
		Where (@SID='' or T.Client_ID=@SID) 
--		and G.vluCheckBox=0
		and (@iFID='' or TD.Item_ID Between @iFID and @iTID) 
--		and (@CID='' or T.Project_ID=@CID)
		and (@EnDate='' or T.dated<=@EnDate)
		and (@iLoc='' or T.Location=@iLoc)
		and (@iScrVNo='' or T.ScrVOucher_No=@iScrVNo)
		and (@iVTP='' or (T.VTP=@iVTP and T.Mnth=@iMnth and T.vYear=@ivYear and T.Location=@iLocation and T.ForLocation=@iForLocation and T.AorB=@iAorB and T.VNo=@iVNo))
		and (@iSrNo=0 or TD.SrNo=@iSrNo)
	Union All
	Select 2, T.ScrVoucher_No+'/'+Cast(TD.SrNo as Varchar(30))
		ScrVoucher_No, TD.Vtp, TD.SubType, TD.FinancialYear, TD.Mnth, TD.vYear, 
		TD.Location, TD.ForLocation, TD.AorB, TD.vno, TD.srno, T.dated, 
		TD.Item_ID, TD.Qty Delivered, 0.00 Returned, 0.00 Invoiced, 
		CD.Rate Rate, ' ' Remarks, Null Project_ID, T.Client_ID, C.Emp_ID Emp_ID,
		T.ScrVoucher_No DeliveryChallanNo, 0.00 GDNDelivered, TD.EngineNo, TD.FrameNo, '' VRegNo, '' ClaimType,
		'' PrincipalCompany_ID, CD.SalesTaxPerc, CD.EDPercent, CD.OTPercent, CD.DiscP, CD.DiscAmt DiscA, CD.GrossRate, 0 MainDiscP,
		IsNull(C.Party_ID, T.Client_ID), C.PONo, C.PO_Date, C.PaymentTerms_ID, Null Job_ID, Null CostCenter_ID, TD.BatchNo, TD.ExpDate, '', 0 BQO, 0 BQD, Null Gift_ID, 0 GQO, 0 GQD, T.BiltyNo, T.BuiltyDate, '' from GDN_Det TD
		Left Join GDN T on TD.VTP=T.VTP and TD.SubType=T.SubType and TD.FinancialYear=T.FinancialYear and TD.Mnth=T.Mnth and TD.vYear=T.vYear and TD.Location=T.Location and TD.ForLocation=T.ForLocation and TD.AorB=T.AorB and TD.VNo=T.VNo
		Left Join ClientOrder_Det CD On IsNull(TD.COVTP,'')=CD.VTP and IsNull(TD.COSubType,'')=CD.SubType and IsNull(TD.COFinancialYear,'')=CD.FinancialYear and IsNull(TD.COMnth,'')=CD.Mnth and IsNull(TD.COvYear,'')=CD.vYear and IsNull(TD.COLocation,'')=CD.Location and IsNull(TD.COForLocation,'')=CD.ForLocation and IsNull(TD.COAorB,'')=CD.AorB and IsNull(TD.COVNo,0)=CD.VNo and IsNull(TD.COSrNo,0)=CD.SrNo
		Left Join ClientOrder C On CD.VTP=C.VTP and CD.SubType=C.SubType and CD.FinancialYear=C.FinancialYear and CD.Mnth=C.Mnth and CD.vYear=C.vYear and CD.Location=C.Location and CD.ForLocation=C.ForLocation and CD.AorB=C.AorB and CD.VNo=C.VNo
		Left Join GlobalSettingsNew G On G.ID='COPYGOODSDELIVERYNOTETOSALESINVOICE'
		Where (G.vluCheckBox<>0 and (DB_Name()<>'ERPZxmcoP' or (DB_Name()='ERPZxmcoP' and TD.COVTP='CRQ')))
		and TD.COVtp<>'PRN'
		and (@SID='' or T.Client_ID=@SID)
		and (@iFID='' or TD.Item_ID Between @iFID and @iTID) 
--		and (@CID='' or T.Project_ID=@CID)
		and (@EnDate='' or T.dated<=@EnDate)
		and (@iLoc='' or T.Location=@iLoc)
		and (@iScrVNo='' or T.ScrVOucher_No=@iScrVNo)
		and (@iVTP='' or (T.VTP=@iVTP and T.Mnth=@iMnth and T.vYear=@ivYear and T.Location=@iLocation and T.ForLocation=@iForLocation and T.AorB=@iAorB and T.VNo=@iVNo))
		and (@iSrNo=0 or TD.SrNo=@iSrNo)
	Union All
	Select 4, Case When JOS.VTP Is Null Then J.ScrVoucher_No+'/'+Cast(JD.SrNo as Varchar(30)) Else JOS.ScrVoucher_No+'/1' End ScrVoucher_No, 
		Case When JOS.VTP Is Null Then JD.Vtp Else JOS.VTP End VTP, 
		Case When JOS.VTP Is Null Then JD.SubType Else JOS.SubType End SubType, 
		Case When JOS.VTP Is Null Then JD.FinancialYear Else JOS.FinancialYear End FinancialYear, 
		Case When JOS.VTP Is Null Then JD.Mnth Else JOS.Mnth End Mnth, 
		Case When JOS.VTP Is Null Then JD.vYear Else JOS.vYear End vYear, 
		Case When JOS.VTP Is Null Then JD.Location Else JOS.Location End Location, 
		Case When JOS.VTP Is Null Then JD.ForLocation Else JOS.ForLocation End ForLocation, 
		Case When JOS.VTP Is Null Then JD.AorB Else JOS.AorB End AorB, 
		Case When JOS.VTP Is Null Then JD.vno Else JOS.vno End vno, 
		Case When JOS.VTP Is Null Then JD.srno Else 1 End srno, 
		Case When JOS.VTP Is Null Then J.dated Else JOS.Dated End, 
		IsNull(JD.Item_ID, TD.Item_ID), TD.Qty Delivered, 0.00 Returned, 0.00 Invoiced, 
		IsNull(JD.Rate,0) Rate, ' ' Remarks, Null Project_ID, IsNull(J.Client_ID, JOS.Client_ID), IsNull(J.Mech_ID,JOS.JobAssigned_ID) Emp_ID,
		IsNull(J.ScrVoucher_No, JOS.ScrVoucher_No) DeliveryChallanNo, 0.00 GDNDelivered, Null EngineNo, Null FrameNo, J.VRegNo, J.ClaimType,
		J.PrincipalCompany_ID, JD.SalesTaxPerc, JD.EDPercent, JD.OTPercent, JD.DiscP, JD.DiscA, JD.GrossRate, J.DiscPerc,
		J.Party_ID, J.PORefNo, Null, J.PaymentTerms, Null Job_ID, Null CostCenter_ID, '' BatchNo, Null ExpiryDate, '', 0 BQO, 0 BQD, Null Gift_ID, 0 GQO, 0 GQD, '' BiltyNo, Null BiltyDate, '' from StoreIssue_Det TD  
		Left Join StoreIssue T on TD.VTP=T.VTP and TD.SubType=T.SubType and TD.FinancialYear=T.FinancialYear and TD.Mnth=T.Mnth and TD.vYear=T.vYear and TD.Location=T.Location and TD.ForLocation=T.ForLocation and TD.AorB=T.AorB and TD.VNo=T.VNo
		Left Join StoreReq_Det SD On IsNull(TD.SQVTP,'')=SD.VTP and IsNull(TD.SQSubType,'')=SD.SubType and IsNull(TD.SQFinancialYear,'')=SD.FinancialYear and IsNull(TD.SQMnth,'')=SD.Mnth and IsNull(TD.SQvYear,'')=SD.vYear and IsNull(TD.SQLocation,'')=SD.Location and IsNull(TD.SQForLocation,'')=SD.ForLocation and IsNull(TD.SQAorB,'')=SD.AorB and IsNull(TD.SQVNo,0)=SD.VNo and IsNull(TD.SQSrNo,0)=SD.SrNo
		Left Join StoreReq S On SD.VTP=S.VTP and SD.SubType=S.SubType and SD.FinancialYear=S.FinancialYear and SD.Mnth=S.Mnth and SD.vYear=S.vYear and SD.Location=S.Location and SD.ForLocation=S.ForLocation and SD.AorB=S.AorB and SD.VNo=S.VNo 
		Left Join JobOrder3SDealers_Det JD On IsNull(SD.EPOVTP,'')=JD.VTP and IsNull(SD.EPOSubType,'')=JD.SubType and IsNull(SD.EPOFinancialYear,'')=JD.FinancialYear and IsNull(SD.EPOMnth,'')=JD.Mnth and IsNull(SD.EPOvYear,'')=JD.vYear and IsNull(SD.EPOLocation,'')=JD.Location and IsNull(SD.EPOForLocation,'')=JD.ForLocation and IsNull(SD.EPOAorB,'')=JD.AorB and IsNull(SD.EPOVNo,0)=JD.VNo and IsNull(SD.EPOSrNo,0)=JD.SrNo
		Left Join JobOrder3SDealers J On JD.VTP=J.VTP and JD.SubType=J.SubType and JD.FinancialYear=J.FinancialYear and JD.Mnth=J.Mnth and JD.vYear=J.vYear and JD.Location=J.Location and JD.ForLocation=J.ForLocation and JD.AorB=J.AorB and JD.VNo=J.VNo
		Left Join JobOrderAfterSale JOS On IsNull(S.EPOVTP,'')=JOS.VTP and IsNull(S.EPOSubType,'')=JOS.SubType and IsNull(S.EPOFinancialYear,'')=JOS.FinancialYear and IsNull(S.EPOMnth,'')=JOS.Mnth and IsNull(S.EPOvYear,'')=JOS.vYear and IsNull(S.EPOLocation,'')=JOS.Location and IsNull(S.EPOForLocation,'')=JOS.ForLocation and IsNull(S.EPOAorB,'')=JOS.AorB and IsNull(S.EPOVNo,0)=JOS.VNo
		Left Join Vtp2 V On IsNull(J.VTP, JOS.VTP)=V.ID2
		Where DB_Name()='ERPLCTRLM' 
		and ((V.ID1='JO3S' and IsNull(JD.ForWorkOrder,0)=0) or V.ID1='JOAS')
		and (@SID='' or J.Client_ID=@SID)
		and (@iFID='' or JD.Item_ID Between @iFID and @iTID) 
		and (@EnDate='' or T.dated<=@EnDate)
		and (@iLoc='' or T.Location=@iLoc)
		and (@iScrVNo='' or Case When JOS.VTP Is Null Then J.ScrVOucher_No Else JOS.ScrVoucher_No End=@iScrVNo)
		and (@iVTP='' or (IsNull(JOS.VTP, IsNull(JD.VTP, T.VTP))=@iVTP and IsNull(JOS.Mnth, IsNull(JD.Mnth, T.Mnth))=@iMnth 
			and IsNull(JOS.vYear, IsNull(JD.vYear, T.vYear))=@ivYear and IsNull(JOS.Location, IsNull(JD.Location, T.Location))=@iLocation 
			and IsNull(JOS.ForLocation, IsNull(JD.ForLocation, T.ForLocation))=@iForLocation and IsNull(JOS.AorB, IsNull(JD.AorB, T.AorB))=@iAorB 
			and IsNull(JOS.VNo, IsNull(JD.VNo, T.VNo))=@iVNo))
		and (@iSrNo=0 or TD.SrNo=@iSrNo)
	Union All
	Select 5, T.ScrVoucher_No+'/'+Cast(TD.SrNo as Varchar(30))
		ScrVoucher_No, TD.Vtp, TD.SubType, TD.FinancialYear, TD.Mnth, TD.vYear, 
		TD.Location, TD.ForLocation, TD.AorB, TD.vno, TD.srno, T.dated, 
		TD.Service_ID, TD.Qty Delivered, 0.00 Returned, 0.00 Invoiced, 
		TD.Rate Rate, ' ' Remarks, Null Project_ID, T.Client_ID, T.Mech_ID Emp_ID,
		T.ScrVoucher_No DeliveryChallanNo, 0.00 GDNDelivered, Null EngineNo, Null FrameNo, T.VRegNo, T.ClaimType,
		T.PrincipalCompany_ID, TD.SalesTaxPerc, TD.EDPercent, TD.OTPercent, TD.DiscP, TD.DiscA, TD.GrossRate, 
		T.DiscPerc, T.Party_ID, '', Null, T.PaymentTerms, Null Job_ID, Null CostCenter_ID, '' BatchNo, Null ExpiryDate, '', 0 BQO, 0 BQD, Null Gift_ID, 0 GQO, 0 GQD, '' BiltyNo, Null BiltyDate, '' from JobOrder3SDealers_Services TD  
		Left Join JobOrder3SDealers T on TD.VTP=T.VTP and TD.SubType=T.SubType and TD.FinancialYear=T.FinancialYear and TD.Mnth=T.Mnth and TD.vYear=T.vYear and TD.Location=T.Location and TD.ForLocation=T.ForLocation and TD.AorB=T.AorB and TD.VNo=T.VNo
		Where (@SID='' or T.Client_ID=@SID)
		and (@iFID='' or TD.Service_ID Between @iFID and @iTID) 
--		and (@CID='' or T.Project_ID=@CID)
		and (@EnDate='' or T.dated<=@EnDate)
		and (@iLoc='' or T.Location=@iLoc)
		and (@iScrVNo='' or T.ScrVOucher_No=@iScrVNo)
		and (@iVTP='' or (T.VTP=@iVTP and T.Mnth=@iMnth and T.vYear=@ivYear and T.Location=@iLocation and T.ForLocation=@iForLocation and T.AorB=@iAorB and T.VNo=@iVNo))
		and (@iSrNo=0 or TD.SrNo=@iSrNo)
	Union All
	Select 6, J.ScrVoucher_No+'/'+Cast(JD.SrNo as Varchar(30))
		ScrVoucher_No, JD.Vtp, JD.SubType, JD.FinancialYear, JD.Mnth, JD.vYear, 
		JD.Location, JD.ForLocation, JD.AorB, JD.vno, JD.srno, J.dated, 
		JD.Item_ID, TD.Qty Delivered, 0.00 Returned, 0.00 Invoiced, 
		JD.Rate Rate, ' ' Remarks, Null Project_ID, J.Client_ID, J.Mech_ID Emp_ID,
		J.ScrVoucher_No DeliveryChallanNo, 0.00 GDNDelivered, Null EngineNo, Null FrameNo, J.VRegNo, J.ClaimType,
		J.PrincipalCompany_ID, 0, 0, 0, 0, 0, 0, 0, '', '', Null, '', Null Job_ID, Null CostCenter_ID, '' BatchNo, Null ExpiryDate, '', 0 BQO, 0 BQD, Null Gift_ID, 0 GQO, 0 GQD, '' BiltyNo, Null BiltyDate, '' from MarketingWO_Det TD  
		Left Join MarketingWO T on TD.VTP=T.VTP and TD.SubType=T.SubType and TD.FinancialYear=T.FinancialYear and TD.Mnth=T.Mnth and TD.vYear=T.vYear and TD.Location=T.Location and TD.ForLocation=T.ForLocation and TD.AorB=T.AorB and TD.VNo=T.VNo
		Left Join JobOrder3SDealers_Det JD On IsNull(TD.PQVTP,'')=JD.VTP and IsNull(TD.PQSubType,'')=JD.SubType and IsNull(TD.PQFinancialYear,'')=JD.FinancialYear and IsNull(TD.PQMnth,'')=JD.Mnth and IsNull(TD.PQvYear,'')=JD.vYear and IsNull(TD.PQLocation,'')=JD.Location and IsNull(TD.PQForLocation,'')=JD.ForLocation and IsNull(TD.PQAorB,'')=JD.AorB and IsNull(TD.PQVNo,0)=JD.VNo and IsNull(TD.PQSrNo,0)=JD.SrNo
		Left Join JobOrder3SDealers J On JD.VTP=J.VTP and JD.SubType=J.SubType and JD.FinancialYear=J.FinancialYear and JD.Mnth=J.Mnth and JD.vYear=J.vYear and JD.Location=J.Location and JD.ForLocation=J.ForLocation and JD.AorB=J.AorB and JD.VNo=J.VNo
		Where JD.VTP Is NOt Null and (@SID='' or J.Client_ID=@SID)
		and (@iFID='' or JD.Item_ID Between @iFID and @iTID) 
--		and (@CID='' or T.Project_ID=@CID)
		and (@EnDate='' or T.dated<=@EnDate)
		and (@iLoc='' or T.Location=@iLoc)
		and (@iScrVNo='' or J.ScrVOucher_No=@iScrVNo)
		and (@iVTP='' or (J.VTP=@iVTP and J.Mnth=@iMnth and J.vYear=@ivYear and J.Location=@iLocation and J.ForLocation=@iForLocation and J.AorB=@iAorB and J.VNo=@iVNo))
		and (@iSrNo=0 or TD.SrNo=@iSrNo)
	Union All
	Select 7, IsNull(TD.CO_VoucherNo,''), IsNull(TD.CO_Vtp, ''), IsNull(TD.CO_SubType, ''), IsNull(TD.CO_FinancialYear, ''), 
		IsNull(TD.CO_Mnth, ''), IsNull(TD.CO_vYear, ''), IsNull(TD.CO_Location, ''), IsNull(TD.CO_ForLocation, ''), 
		IsNull(TD.CO_AorB, ''), IsNull(TD.CO_Vno, ''), TD.CO_srno, Null, IsNull(TD.Item_ID, TD.Service_ID), 0.00, 
		0.00, TD.Qty, 0, '', Null CostCenter_ID, T.Client_ID, '', '', 0.00, TD.EngineNo, TD.FrameNo, '', '', '', 0, 0, 0, 
		0, 0, 0, 0, '', '', Null, '', TD.Service_ID Job_ID, Null CostCenter_ID, '' BatchNo, Null ExpiryDate, '', 0 BQO, TD.BonusQty BQD, Null Gift_ID, 0 GQO, TD.GiftQty GQD, '' BiltyNo, Null BiltyDate, '' from SaleInvoice_Det TD
		Left Join SaleInvoice T on TD.VTP=T.VTP and TD.SubType=T.SubType and TD.FinancialYear=T.FinancialYear and TD.Mnth=T.Mnth and TD.vYear=T.vYear and TD.Location=T.Location and TD.ForLocation=T.ForLocation and TD.AorB=T.AorB and TD.VNo=T.VNo
		Where (@iWR=0 or @iWR=4 or @iWR=2) and (@SID='' or T.Client_ID=@SID)
		and (@iFID='' or TD.Item_ID Between @iFID and @iTID) 
--		and (@CID='' or TD.CostCenter_ID=@CID)
		and (@EnDate='' or T.dated<=@EnDate)
		and IsNull(TD.CO_VTP,'')<>''
		and (@iLoc='' or T.Location=@iLoc)
		and (@iScrVNo='' or TD.CO_ScrVoucher_No=@iScrVNo)
		and (@iVTP='' or (IsNull(TD.CO_VTP,'')=@iVTP and IsNull(TD.CO_Mnth,'')=@iMnth and IsNull(TD.CO_vYear,'')=@ivYear and IsNull(TD.CO_Location,'')=@iLocation and IsNull(TD.CO_ForLocation,'')=@iForLocation and IsNull(TD.CO_AorB,'')=@iAorB and IsNull(TD.CO_VNo,0)=@iVNo))
		and (@iSrNo=0 or TD.SrNo=@iSrNo)
	Union All
	Select 8, IsNull(TD.COVoucherNo,''), IsNull(TD.COVtp, ''), IsNull(TD.COSubType, ''), IsNull(TD.COFinancialYear, ''), 
		IsNull(TD.COMnth, ''), IsNull(TD.COvYear, ''), IsNull(TD.COLocation, ''), IsNull(TD.COForLocation, ''), 
		IsNull(TD.COAorB, ''), IsNull(TD.COVno, ''), TD.COsrno, Null, TD.Item_ID, 0.00, 
		0.00, 0.00, 0, '', Null CostCenter_ID, T.Client_ID, '', '', TD.Qty, '', '', '', '', '', CD.SalesTaxPerc, CD.EDPercent, CD.OTPercent, 
		0, 0, 0, 0, '', '', Null, '', Null Job_ID, Null CostCenter_ID, '' BatchNo, Null ExpiryDate, '', 0 BQO, TD.BonusQty BQD, Null Gift_ID, 0 GQO, TD.GiftQty GQD, '' BiltyNo, Null BiltyDate, '' from GDN_Det TD
		Left Join GDN T on TD.VTP=T.VTP and TD.SubType=T.SubType and TD.FinancialYear=T.FinancialYear and TD.Mnth=T.Mnth and TD.vYear=T.vYear and TD.Location=T.Location and TD.ForLocation=T.ForLocation and TD.AorB=T.AorB and TD.VNo=T.VNo
		Left Join ClientOrder_Det CD On IsNull(TD.COVTP,'')=CD.VTP and IsNull(TD.COSubType,'')=CD.SubType and IsNull(TD.COFinancialYear,'')=CD.FinancialYear and IsNull(TD.COMnth,'')=CD.Mnth and IsNull(TD.COvYear,'')=CD.vYear and IsNull(TD.COLocation,'')=CD.Location and IsNull(TD.COForLocation,'')=CD.ForLocation and IsNull(TD.COAorB,'')=CD.AorB and IsNull(TD.COVNo,0)=CD.VNo and IsNull(TD.COSrNo,0)=CD.SrNo
		Left Join ClientOrder C On CD.VTP=C.VTP and CD.SubType=C.SubType and CD.FinancialYear=C.FinancialYear and CD.Mnth=C.Mnth and CD.vYear=C.vYear and CD.Location=C.Location and CD.ForLocation=C.ForLocation and CD.AorB=C.AorB and CD.VNo=C.VNo
		Left Join Vtp2 V On V.ID2=TD.COVTP
		Where (@iWR=1 or @iWR=2) 
		and V.ID1 In ('DC') and (@SID='' or T.Client_ID=@SID)
		and (@iFID='' or TD.Item_ID Between @iFID and @iTID) 
--		and (@CID='' or TD.CostCenter_ID=@CID)
		and (@EnDate='' or T.dated<=@EnDate)
		and IsNull(TD.COVTP,'')<>''
		and (@iLoc='' or T.Location=@iLoc)
		and (@iScrVNo='' or TD.COScrVoucher_No=@iScrVNo)
	) 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 sum(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)))
	Return
End

SQL

Alter 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 @T Table (
	ScrVoucher_NoWithSrNo Varchar (101), 
	VTP Varchar (20), 
	SubType Varchar (8), 
	FinancialYear Varchar (8), 
	Mnth Varchar (8), 
	vYear Varchar (8), 
	Location Varchar (8), 
	ForLocation Varchar (8), 
	AorB Varchar (1), 
	VNo Varchar (4), 
	SrNo Varchar (4), 
	Dated DateTime, 
	Client_ID Varchar (32), 
	Client_Title Varchar (90), 
	Item_ID Varchar (32), 
	Item_Title Varchar (250), 
	Delivered Double Precision, 
	Returned Varchar (4), 
	Invoiced Double Precision, 
	Unit Varchar (10), 
	Rate Double Precision, 
	Remarks Varchar (1000), 
	Emp_ID Varchar (32), 
	Emp_Title Varchar (70), 
	DeliveryChallanNo Varchar (70), 
	GDNDelivered Double Precision, 
	iWR TinyInt, 
	EngineNo Varchar (64), 
	FrameNo Varchar (64), 
	ScrVoucher_No Varchar (70), 
	ServiceOrPart Varchar (8), 
	VRegNo Varchar (32), 
	ClaimType Varchar (32), 
	PrincipalCompany_ID Varchar (32), 
	PrincipalCompany_Title Varchar (90), 
	STPercent Double Precision, 
	EDPercent Double Precision, 
	OTPercent Double Precision, 
	DiscP Double Precision, 
	DiscA Double Precision, 
	GrossRate Double Precision, 
	MainDiscP Double Precision, 
	Party_ID Varchar (5), 
	Party_Title Varchar (70), 
	ClientPONo Varchar (200), 
	ClientPODate DateTime, 
	Main_VTP Varchar (10), 
	SaleRate Double Precision, 
	PaymentTerms Varchar (32), 
	PaymentTermsCoa Varchar (10), 
	Job_ID Varchar (32), 
	Job_Title Varchar (170), 
	CostCenter_ID Varchar (32), 
	Project_ID Varchar (32), 
	CostCenter_Title Varchar (120), 
	Project_Title Varchar (70), 
	BatchNo Varchar (40), 
	ExpiryDate DateTime, 
	Transporter_ID Varchar (32), 
	Transporter_Title Varchar (90), 
	QtyPerCTN Double Precision, 
	BQO Double Precision, 
	BQD Double Precision, 
	Gift_ID Varchar (32), 
	Gift_Title Varchar (250), 
	GQO Double Precision, 
	GQD Double Precision, 
	BiltyNo Varchar (120), 
	BiltyDate DateTime, 
	OldSoftNo Varchar (32)
)
as
Begin
	Insert @T
	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, (Substring(T.Remarks, 1, 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=Case WHen CP.ID Is Null Then C.ID Else CP.ID End
		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
/*		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 (T.Delivered)>(T.Invoiced-T.Returned)) or 
			(@iWR=1 and (T.Delivered)>(T.GDNDelivered)) or 
			(@iWR=2 and (T.Delivered)<(T.Invoiced-T.Returned)) or 
			(@iWR=3 and (T.Delivered)<(T.GDNDelivered))
		)
*/	Return
End

Re: Query Execution Time Management.

Richard Deeming6-May-16 0:53

Richard Deeming

6-May-16 0:53

I'd be inclined to make both of those in-line UDFs, rather than multi-statement UDFs:

SQL

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))
    )
);

SQL

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

Re: Query Execution Time Management.

Nasir Abbas8-May-16 23:32

Nasir Abbas

8-May-16 23:32

Thank you very much Richard for ur prompt reply (I was away and hence was unable to see it right in-time then).

Previously (some days ago), these were in-line functions but as records increase, an "Insufficient Memory" error was faced although systems had around 16, 32 GBs of Rams, due to which these have to be converted to multi-statement functions. Also, I m using SQL Server 2008 too but getting almost the same results.

May u pls suggest some better way to write queries of these natures.

addition having null values in between

Member 118403633-May-16 8:22

Member 11840363

3-May-16 8:22

I am designing a database for students management. For flexibility reasons I have kept it on a monthly basis wherein the students are awarded the number of lectures they have attended and zero if not. the department doesn't follow a strict time table hence there are times when there is no lecture for that subject during the entire day. The default value is null for such scenario. In short for each subject a student gets as following:
If present - number of lectures attended
if absent - zero
if no lectures for the day - NULL.
The problem is when adding up the entire column(yes column) if there is a null value the final answer is null. I want the answer to be summation of all the numbers present and Not count in the NULL values.

Re: addition having null values in between

Tomas Takac3-May-16 8:59

Tomas Takac

3-May-16 8:59

How do you add up the values? SUM should give you the correct result with NULL values. What database engine do you use?

Re: addition having null values in between

Member 118403633-May-16 9:18

Member 11840363

3-May-16 9:18

create or replace view Total_TABLE as
select 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,29,30,31,1+2+3+4+5+6+7+8+9+10+11+12+13+14+15+16+17+18+19+20+21+22+23+24+25+26+27+28+29+29+30+31 as Total
from Add_attend;

the problem is when there is a null value in the table the final result after adding is Null and I want the result of all all the numbers and NOT null value.

I am using mysql wampserver

Re: addition having null values in between

Tomas Takac3-May-16 9:51

Tomas Takac

3-May-16 9:51

Having a column per day is a poor design choice IMO. I'd strongly suggest you review it. As RyanDev already mentioned, you should use COALESCE[^], but you need to apply it to every value you are adding up.

SQL

COALESCE(1, 0) + COALESCE(2, 0) + ... + COALESCE(31, 0)

Re: addition having null values in between

Member 118403633-May-16 15:03

Member 11840363

3-May-16 15:03

Thanks a lot sir. it has been solved Smile | :)

Re: addition having null values in between

ZurdoDev3-May-16 9:07

ZurdoDev

3-May-16 9:07

If this is Microsoft SQL server you can easily use IsNull() or COALESCE().

There are only 10 types of people in the world, those who understand binary and those who don't.

Re: addition having null values in between

Member 118403633-May-16 9:32

Member 11840363

3-May-16 9:32

I got your point sir. I know i am an inexperienced newbie, this the first time I am doing this. It would be of great help if you can help me with this.
I am using Mysql server

Re: addition having null values in between

ZurdoDev3-May-16 9:34

ZurdoDev

3-May-16 9:34

Member 11840363 wrote:
It would be of great help if you can help me with this.

I did.

There are only 10 types of people in the world, those who understand binary and those who don't.

Re: addition having null values in between

Member 118403633-May-16 9:42

Member 11840363

3-May-16 9:42

10 is TWO in binary.
can you please help me in framing the correct query? Please

Re: addition having null values in between

ZurdoDev3-May-16 9:45

ZurdoDev

3-May-16 9:45

Did you lookup how to use IsNull or COALESCE()?

It's very simple.

SELECT COALESCE(field1, 0)
or
SELECT IsNull(field1, 0)

If field1 is null then you will get 0. So, if you are adding them up then adding 0 will not get counted.

There are only 10 types of people in the world, those who understand binary and those who don't.

Alternatives to IBM DB2/Oracle?

Member 1249383629-Apr-16 10:36

Member 12493836

29-Apr-16 10:36

Looking to switch from the high cost of ownership or licensing fees, for transaction based processing we've looked at a few providers and don't want to switch to open source tbh. Frown | :(

Re: Alternatives to IBM DB2/Oracle?

rick_lively30-Apr-16 10:40

rick_lively

30-Apr-16 10:40

There is a postgres add-on called EnterpriseDB you may want to look into, it has a lot of compatibility with PL/SQL which may help reduce your conversion costs. They charge to support it, but it should be less than what you would pay Oracle.

OmniDB - An Open Source Web Tool For Database Management and Conversion

William Ivanski29-Apr-16 5:11

William Ivanski

29-Apr-16 5:11

Guys,

We are developing a web tool called OmniDB: http://omnidb.com.br[^]

It is open source now, you can check its sources in: GitHub - OmniDB/OmniDB: Web tool for database management and conversion[^]

OmniDB is cross-platform and supports Firebird, MySQL, Oracle, PostgreSQL, SQLite, Microsoft SQL Server and Microsoft Access.

We are looking forward to hear feedback from the community. Thanks in advance!

Re: OmniDB - An Open Source Web Tool For Database Management and Conversion

Eddy Vluggen29-Apr-16 6:36

Eddy Vluggen

29-Apr-16 6:36

William Ivanski wrote:
We are looking forward to hear feedback

My feedback is that this is not a question.

There's a "Free Tools" section where you can point out free tools. It's in the "Product Lifecycle" category.

-- taking a peek at the website;

William Ivanski wrote:
OmniDB is cross-platform

Where is the download for Ubuntu?

..the bottom row of "features" does not look very appealing - having tabs in an editor is not something to brag about; it is like highlighting that your car has wheels.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: OmniDB - An Open Source Web Tool For Database Management and Conversion

William Ivanski29-Apr-16 6:52

William Ivanski

29-Apr-16 6:52

Hi, Eddy! I really appreciate your feedback.

Eddy Vluggen wrote:
There's a "Free Tools" section where you can point out free tools. It's in the "Product Lifecycle" category.

I'll post that, thanks for the advice.

Eddy Vluggen wrote:
having tabs in an editor is not something to brag about

It may sound crazy, but not all database management tool have tabs in their editors. Note that OmniDB is a web database management tool with tabs in its editor.

Eddy Vluggen wrote:
Where is the download for Ubuntu?

OmniDB is a web application, so it needs to be served by a webserver. For that purpose, you can use Mono XSP (which runs on any Linux distribution as well as on Mac OS X) or Cassini (Windows).

Re: OmniDB - An Open Source Web Tool For Database Management and Conversion

Eddy Vluggen29-Apr-16 7:21

Eddy Vluggen

29-Apr-16 7:21

William Ivanski wrote:
It may sound crazy, but not all database management tool have tabs in their editors.

Just as there still exists text-editors without tabs.

William Ivanski wrote:
Note that OmniDB is a web database management tool with tabs in its editor.

Nope, still doesn't work for me. The TabControl is over 16 years old, and I haven't seen much products that tout their MDI-interface as a core feature.

William Ivanski wrote:
OmniDB is a web application, so it needs to be served by a webserver.

Wouldn't that make the .NET webserver the platform? Smile | :)

I'm using Sql Management Studio at work, which is also free. How would you compare the two?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: OmniDB - An Open Source Web Tool For Database Management and Conversion

Rafael T. Castro29-Apr-16 9:34

Rafael T. Castro

29-Apr-16 9:34

There are plenty of cross-platform webservers that run ASP.NET applications.

Also, SQL Management Studio is a desktop application that manages SQL Server databases only, the applications have different purposes.

i want to create data base by using c++ language

Member 1249285029-Apr-16 0:01

Member 12492850

29-Apr-16 0:01

i want to create data base by using c++ language what i'm suppossed to do efore starting

Re: i want to create data base by using c++ language

Eddy Vluggen29-Apr-16 0:26

Eddy Vluggen

29-Apr-16 0:26

Read the documentation and look for examples and tutorials.

In managed C++, you'd open a connection, issue an command and read the results.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: i want to create data base by using c++ language

ZurdoDev29-Apr-16 8:09

ZurdoDev

29-Apr-16 8:09

Learn C++ and database concepts. You are taking on a huge task.

There are only 10 types of people in the world, those who understand binary and those who don't.

Last Visit: 31-Dec-99 18:00 Last Update: 27-Apr-24 17:41

Refresh

ᐊ Prev 1...103 104 105 106107108 109 110 111 112 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.