I wrote one stored procedure.
My requirement is i have to show the data like if any No column has different Account_Names i have to show like Account_Name :Multipul(varchar))
Create PROCEDURE [dbo].[OpenVendorBIllsSP] (@FromDate Date,@ToDate Date)
AS
BEGIN
select
ROW_NUMBER() OVER(Order by vendor.VendorName ) AS ID,
PH.PurchasingHeaderID as No,
PH.TotalPriceCompanyCurrency as Balance,
acc.AccountName as [Account_Name],
PD.LineMemo as Memo,
from PurchasingHeader PH
LEFT OUTER JOIN TransactionType Trans ON PH.TransactionTypeID =Trans.TransactionTypeID
LEFT OUTER JOIN Vendor vendor on PH.VendorID=vendor.VendorID
LEFT OUTER JOIN PaymentTerm PT on PT.PaymentTermID = vendor.PaymentTermID
LEFT OUTER JOIN PurchasingDetail PD on PD.PurchasingHeaderID = PH.PurchasingHeaderID
LEFT OUTER JOIN Account Acc on Acc.AccountID= PD.FinancialAccountID
where PH.TransactionTypeID=7
Group by vendor.VendorName,
PH.PurchasingHeaderID,PH.TotalPriceCompanyCurrency,acc.AccountName
END
GO
with this result:
ID No Account_Name Balance Memo**
-------------------------------------------------------------
101 VB1000 Cash-Petty Cash 4000.00 Memo
102 VB1001 Accounts Receivable 5000.00 Memo
103 VB1003 Cash-PettyCash 6000.00 Memo
104 VB1003 Cash-PettyCash 6000.00 Memo
105 VB1004 UndepositedFunds 7000.00 Memo
I need this result :
ID No Account_Name Balance Memo
------------------------------------------------------
101 VB1000 Cash-PettyCash 4000.00 Memo1
102 VB1001 AccountsReceivable 5000.00 Memo2
103 VB1003 ---Multiple---- 6000.00 Memo3
104 VB1004 UndepositedFunds 7000.00 Memo4
Can any one tell me how can i write the sp to get aboue result.
Edited SP :
select
ROW_NUMBER() OVER(Order by vendor.VendorName ) AS ZID,
PH.PurchasingHeaderID as No,
PH.TotalPriceCompanyCurrency as Balance,
Case When count(PH.PurchasingHeaderID) > 1 Then 'Multipul' else acc.AccountName END
from PurchasingHeader PH
LEFT OUTER JOIN TransactionType Trans ON PH.TransactionTypeID =Trans.TransactionTypeID
LEFT OUTER JOIN Vendor vendor on PH.VendorID=vendor.VendorID
LEFT OUTER JOIN PaymentTerm PT on PT.PaymentTermID = vendor.PaymentTermID
LEFT OUTER JOIN PurchasingDetail PD on PD.PurchasingHeaderID = PH.PurchasingHeaderID
LEFT OUTER JOIN Account Acc on Acc.AccountID= PD.FinancialAccountID
where PH.TransactionTypeID=7
Group by vendor.VendorName,PH.PurchasingHeaderID ,
PH.PurchasingHeaderID,PH.TotalPriceCompanyCurrency,acc.AccountName
