Database

Re: view in which each column contains data from a different table

7-May-16 16:22

I am doing a student attendance project in php & mysql.
I have a monthly table for recording attendance of students. the first column in the table is the roll number which is common for all the tables and the dates of each month have a column of their own.
suppose if the attendance is of 5 months, I have 5 tables of the same repeated table.
I want to create a table or view that has columns for showing total of each month with the students roll number i.e. student roll number column, first month total column, second month total column, so on.
Can anyone please help me how to do it in mysql?

Mycroft Holmes7-May-16 17:55

Mycroft Holmes

7-May-16 17:55

Member 11840363 wrote:
if the attendance is of 5 months, I have 5 tables of the same repeated table.

Let me get this clear, every month you create a new table for attendances. In which case you should start again and design your data structure to be sustainable, that design is absolutely NOT.

You need just 1 table
StudentID (roll number)
Date

If you have a table for each month then whatever you create you are going to have to redo every month unless the tables are year independent IE tblJan, tblFeb etc. This is still a really bad design but can be worked with under duress.

You can create a view using UNION and a select statement for each month table but you will need to add another select every month.

Never underestimate the power of human stupidity
RAH

Re: view in which each column contains data from a different table

Member 118403637-May-16 18:09

Re: view in which each column contains data from a different table

7-May-16 18:09

Thanks for taking out some time to reply to my query. I have tried Union and select statements but I am not getting the desired result. It would be of great help if you show me some snippet or syntax of the query for example joining 4 tables using union and select.
For the tables creation part I was thinking about creating procedures to create a table whenever needed, and at the time of creation the name of the table to be created and the table it references should be asked.
It would be great help if you could help with the above both.

Mycroft Holmes7-May-16 18:33

Mycroft Holmes

7-May-16 18:33

Member 11840363 wrote:
For the tables creation part I was thinking about creating procedures to create a table

NO! just don't go down this path, redesign you data to have only 1 table. Seriously you are making a HUGE mistake if you do not structure your data properly.

If you are able to write procedures then I assume you own the database. Restructure NOW before you do any more work. You need 1 table with a min of 2 fields RollNo and date.

Never underestimate the power of human stupidity
RAH

Re: view in which each column contains data from a different table

Jörgen Andersson8-May-16 19:58

Jörgen Andersson

8-May-16 19:58

Listen to the man, seriously, it's the best advice you'll ever get regarding databases.

Wrong is evil and must be defeated. - Jeff Ello

Re: view in which each column contains data from a different table

Member 118403639-May-16 4:37

Re: view in which each column contains data from a different table

9-May-16 4:37

Thanks a lot guys for your valuable time. I realize that it is a complicated design to manage and not that convenient either for maintaining. but can anyone help me the possible solutions that would help in such a scenario for knowledge purpose.

Eddy Vluggen9-May-16 4:52

Eddy Vluggen

9-May-16 4:52

That's like asking how to build a car with the wheels on top for knowledge purposes.

Normalize your tables, and querying becomes simpeler.

Bastard Programmer from Hell Suspicious | :suss:

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

Re: view in which each column contains data from a different table

Mycroft Holmes9-May-16 14:11

Mycroft Holmes

9-May-16 14:11

Don't you get the impression you may have done something incorrectly, the only response you have had is to fix your data structure. As Eddy said once yo have done that the query becomes trivial.

It is possible to hack around the terrible structure you have built but us showing you for any reason is doing you a disservice. We are here to help you learn, help fix problems, we are not interested in perpetrating bad design.

Never underestimate the power of human stupidity
RAH

Re: view in which each column contains data from a different table

CHill608-May-16 13:31

CHill60

8-May-16 13:31

I wish I could up vote this from my phone! Spot on advice

Query Execution Time Management.

Nasir Abbas5-May-16 7:27

Nasir Abbas

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

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

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

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

addition having null values in between

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.

Member 118403633-May-16 8:22

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.

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?

Member 118403633-May-16 9:18

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

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)

Member 118403633-May-16 15:03

3-May-16 15:03

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

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.

Member 118403633-May-16 9:32

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

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.

Member 118403633-May-16 9:42