How to separate Multiple InvoiceNos of one Receip

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to separate Multiple InvoiceNos of one Receip

Author

Topic

rtg2005
Starting Member

1 Post

Posted - 2007-10-20 : 07:27:32

Hi ,
please tell me How to separate Multiple InvoiceNos of one ReceiptID by comma in storedprocedure using cursor.
I have Store procedure as like :-

USE [TestExample]
GO
ALTER Procedure [TestExample].[RptCashChequeReceiptVoucherHeaderSingle]

@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ReceiptID NVARCHAR(36)

AS

declare @CompanyCurrencySymb nvarchar(1)
declare @DHCurrencySymb nvarchar(1)
set @DHCurrencySymb=(select CurrenycySymbol from CurrencyTypes where
CompanyID =@CompanyID
AND DivisionID =@DivisionID
AND DepartmentID = @DepartmentID
and CurrencyId=(select CurrencyId from CurrencyTypes
where CompanyID =@CompanyID AND DivisionID =@DivisionID
AND DepartmentID = @DepartmentID and CurrencyId='DH'))

set @CompanyCurrencySymb=(select CurrenycySymbol from CurrencyTypes where
CompanyID =@CompanyID
AND DivisionID =@DivisionID
AND DepartmentID = @DepartmentID
and CurrencyId=(select CurrencyId from Companies
where CompanyID =@CompanyID AND DivisionID =@DivisionID
AND DepartmentID = @DepartmentID))

SELECT Distinct ReceiptsHeader.ReceiptID,
CASE
WHEN ReceiptsHeader.ReceiptTypeID = 'Check' THEN
ReceiptsHeader.CheckNumber
ELSE
'Cash'
END as ByCashOrChequeNo,
ReceiptsHeader.Amount,
ReceiptsHeader.ReceiptTypeID,
ReceiptsHeader.CheckNumber,
ReceiptsHeader.TransactionDate as ReceiptDate,
CustomerInformation.CustomerName,
ReceiptsDetail.DocumentNumber as InvoiceNumber,
@CompanyCurrencySymb as CompanyCurrencySymb ,
@DHCurrencySymb as DHCurrencySymb
FROM ReceiptsHeader Left OUTER JOIN ReceiptsDetail
ON ReceiptsHeader.CompanyID = ReceiptsDetail.CompanyID AND ReceiptsHeader.DivisionID = ReceiptsDetail.DivisionID
AND ReceiptsHeader.DepartmentID =ReceiptsDetail.DepartmentID and ReceiptsHeader.ReceiptID = ReceiptsDetail.ReceiptID
Left OUTER JOIN CustomerInformation ON ReceiptsHeader.CompanyID = CustomerInformation.CompanyID
AND ReceiptsHeader.DivisionID = CustomerInformation.DivisionID AND ReceiptsHeader.DepartmentID = CustomerInformation.DepartmentID
AND ReceiptsHeader.CustomerID = CustomerInformation.CustomerID
WHERE ReceiptsHeader.CompanyID = @CompanyID
AND ReceiptsHeader.DivisionID = @DivisionID
AND ReceiptsHeader.DepartmentID = @DepartmentID
AND ReceiptsHeader.ReceiptID = @ReceiptID

O/p of this StoredProcedure is as:

AssetID AssetTypeID Cost Additions Disposal subtotal, Depreciation, NetBookValue GlTransactionDate
a001 Building Improvements 10000.00 4000.00 2000.00 12000.00 8000.00 4000.00 2007-10-12 00:00:00.000
a003 Residence Furn/Equipment 30000.00 5000.00 4000.00 31000.00 7000.00 24000.00 2007-10-08 14:18:36.000

But when i generate report for one ReceiptID having Multiple InvoiceNos, this code generate report for each invoiceNo. seperately.
I want one Receipt report having one receiptID and multiple invoice nos separated by comma(,).i.e like following report

CASH/CHEQUE RECEIPT VOUCHER
ReceiptID : 2026
Date : 7/26/2007

Received from Mr./ Messrs : Technical Services

The Sum Of (Q.Rls): 456.0000

By Cash/Cheque No.: Cash
For........................................................
MaterialDetails ....................................................
Invoice No.: 2256,2040,5512,1045

Signature ...........................................................

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-20 : 08:20:15

take a look here http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources