CTE

ALTER PROCEDURE [dbo].[GetPOBasicParentDataByParamsForAdvanceBill_SP]
@FromDate date,
@ToDate date,
@POId int = 2011,
@SupplierId int=0,
@CompanyId int=0,
@userId int = 3
AS
	;WITH AlreadyBilledAmount AS (
	SELECT 
		bic.Po_Id,
		SUM(ISNULL(bigc.billAmount,0.00)) - SUM(ISNULL(bigc.Discount,0.00)) as TotalBilledAmount
		FROM dbo.BillInvoice_Parent bip
		INNER JOIN dbo.BillInvoice_Child bic ON bip.ID = bic.Bill_ParentId
		INNER JOIN dbo.BillInvoice_GrandChild bigc ON bigc.Bill_ParentId = bip.ID AND	bigc.Child_ParentId = bic.ID
		WHERE (bip.CompanyId = @CompanyId or @CompanyId = 0) AND (bip.SupplierId = @SupplierId or @SupplierId = 0)
		GROUP BY bic.Po_Id
	), PO_Amount AS(
	select pc.PurOrderMasterID, 
	sum(pc.UnitPrice * pc.ItemQty) AS POAmnt, 
	sum((pc.UnitPrice * pc.ItemQty) *  pc.VATPercent/ 100) AS  VatAmnt,
	 sum((pc.UnitPrice * pc.ItemQty) *  pc.AITPercent/ 100) AS  AITAmnt
	FROM dbo.PurchaseOrderMaster pm WITH (NOLOCK)
	INNER JOIN dbo.PurchaseOrderChild pc WITH (NOLOCK) ON pm.ID = pc.PurOrderMasterID 
	WHERE (pm.CompanyId = @CompanyId or @CompanyId = 0) 
	AND (pm.SupplierId = @SupplierId or @SupplierId = 0)
	AND (pm.ID = @POId or @POId = 0)
   
   GROUP BY pc.PurOrderMasterID--, pc.ItemID
)



	SELECT 0 id,0 parentId,POM.ID poId, POM.Code AS poNo,pom.TotalValue poTotalAmount,

	SUM(POC.UnitPrice*POC.ItemQty) poCompanyAmount_Old,
	(POA.POAmnt + ISNULL(POA.VatAmnt,0) - ISNULL(POA.AITAmnt,0) + ISNULL(pom.CarringCharge,0)) AS poCompanyAmount, ---New Added
	ISNULL(POA.AITAmnt,0) AS PO_AITAmnt
    

	,isnull(SUM(POC.UnitPrice*POC.ItemQty)-ISNULL(tbl.Amount,0.00),0.00) - ISNULL(ABA.TotalBilledAmount,0.00) amount,POM.VatPercent vds,pom.AitPercent tds,'' Note
	,ISNULL(tbl.Amount,0.00) alreadyAdvanceBillAmount
	,COM.ID CompanyId,POM.SupplierID,com.Name CompanyName,SUPP.SupplierName
	,ISNULL(ABA.TotalBilledAmount,0.00) TotalBilledAmount
	,POM.CurrencyID
	
	FROM  dbo.PurchaseOrderMaster POM WITH (NOLOCK)
	inner join dbo.PurchaseOrderChild POC WITH (NOLOCK) on poc.PurOrderMasterID=pom.ID
	INNER JOIN PurchaseRequisitionMaster PRM ON PRM.ID=POC.PurReqMasterID
	INNER JOIN Store ST ON ST.ID=PRM.StoreID
	INNER JOIN Company COM ON COM.ID=ST.CompanyID
	INNER JOIN Supplier SUPP ON SUPP.ID=POM.SupplierID
	LEFT JOIN (SELECT AC.Po_Id,sum(AC.Amount) Amount FROM AdvanceBill_Child AC group by AC.Po_Id) tbl on tbl.Po_Id=pom.ID
	LEFT JOIN AlreadyBilledAmount ABA ON ABA.PO_Id=POM.ID
	LEFT JOIN PO_Amount POA  ON POA.PurOrderMasterID = pom.ID 

	WHERE (POM.ID=@POId OR @POId=0)
	AND ((CONVERT(DATE,POM.creation_date) BETWEEN @FromDate AND @ToDate) OR (@FromDate='' AND @ToDate=''))
	AND (POM.SupplierID = @SupplierId OR @SupplierId = 0)
	AND (COM.ID = @CompanyId OR @CompanyId = 0)
	AND (COM.ID in (SELECT COM.CompanyID FROM UserCompanyMap COM WHERE COM.UserID=@userId)) 
	
	
	GROUP BY POM.ID,POM.Code,POM.VatPercent,pom.AitPercent,COM.ID,POM.SupplierID,pom.TotalValue,ISNULL(tbl.Amount,0.00),com.Name,SUPP.SupplierName,ABA.TotalBilledAmount
	,POA.POAmnt , ISNULL(POA.VatAmnt,0) , ISNULL(POA.AITAmnt,0),pom.CarringCharge,	POM.CurrencyID

CTE Vs TempTbl

কখন CTE ব্যবহার করা ভালো আর কখন Temp Table ব্যবহার করা ভালো?

কখন CTE ব্যবহার করা ভাল:
1. Complex query ভেঙে সুন্দরভাবে লিখতে চাইলে CTE ভালো।
2. যদি result set একবারই ব্যবহার করতে হয় → CTE ভালো
3. Data ছোট হলে performance সমস্যা হয় না

কখন Temp Table ব্যবহার করা ভালো:
1. একই result set যদি ২–৩ জায়গায় লাগে → Temp Table ভালো।
2. যদি লাখ লাখ row হয় → Temp Table ভালো performance দেয়।
কারণ:
  i. tempdb তে physically store হয়
  ii. indexing করা যায়
  
সহজ সিদ্ধান্ত নেওয়ার নিয়ম
👉 Single use + small data → CTE
👉 Multiple use + large data + indexing দরকার → Temp Table