HI ALL,

Below is my update statement. The issue my facing is , There are multiple rv.DividendsID for single  DV.DividendsID in the below statement.
In that i want to concatinate the max(rv.DividendsID) for that DividendID in TableA.

For example. While concatinating, I am getting 1454 and 1455 values for rv.DividendsID . In this the final concatination should
be with 1455. 

I hope i am not confusing here. Please help me.

update TableA set Comments=CONCAT(dv.Comments,'** Transaction reversed by DividendID = ', rv.DividendsID), 
IncludeReversals='Y' 
	FROM TABLEA dv join TABLEB RV ON
	   dv.GroupSeries=rv.GroupSeries
   and dv.ShortName=rv.ShortName
   and dv.[CUSIP]=rv.CUSIP
   and (dv.[CUSIPDescription]=rv.CUSIPDescription or (dv.CUSIPDescription is NULL and rv.CUSIPDescription is NULL))
   and dv.[TransactionID]=rv.TransactionID
   and (dv.[SubType]=rv.SubType or (dv.SubType is NULL and rv.SubType is NULL))
   and dv.[Interest]=rv.Interest
   and dv.[Shares]=rv.Shares
   and dv.[Rate]=rv.Rate
   and dv.[ExDate]=rv.ExDate
   and dv.[PayableDate]=rv.PayableDate
   and dv.[AddDate]=rv.AddDate
   and dv.[RecordDate]=rv.RecordDate
   and (dv.[SecurityType]=rv.SecurityType or (dv.SecurityType is NULL and rv.SecurityType is NULL))
   and (dv.[SecurityTypeDescription]=rv.SecurityTypeDescription or (dv.SecurityTypeDescription is NULL 
and rv.SecurityTypeDescription is NULL))
   where dv.DatasetID<rv.DatasetID and isdate(dv.ReversalDate)<>1 and dv.IncludeReversals is NULL 

Can you put a small sample table and a little data to help illustrate this?

It looks like there are a lot of columns in your join? Normally the CUSIP should be enough with a few extras as it is a unique number?

Hi ,

the table structure is below.

CREATE TABLE TABLEA
(
 [DividendsID] Int NOT NULL,
 [GroupSeries] Varchar(10) NULL,
 [ShortName] Varchar(13) NULL,
 [CUSIP] Varchar(10) NULL,
 [CUSIPDescription] Varchar(51) NULL,
 [TransactionID] Varchar(5) NULL,
 [SubType] Varchar(5) NULL,
 [Interest] Decimal(14,2) NULL,
 [Shares] Decimal(14,3) NULL,
 [Rate] Decimal(12,5) NULL,
 [ExDate] Date NULL,
 [PayableDate] Date NULL,
 [AddDate] Date NULL,
 [RecordDate] Date NULL,
 [ReversalDate] Date NULL,
 [Ticker] Varchar(7) NULL,
 [MissedPY] Varchar(1) NULL,
 [IncludeReversals] Varchar(1) NULL,
 [IgnoreFlag] Varchar(1) NULL,
 [IgnoreReason] Varchar(250) NULL,
 [SecurityType] Varchar(20) NULL,
 [SecurityTypeDescription] Varchar(50) NULL,
 [YESAccount] Varchar(50) NULL,
 [DatasetID] Int NULL,
 [ReconciliationID] Int NULL,
 [ReconciliationType] Varchar(50) NULL,
 [Comments] Varchar(250) NULL,
 [ImportDate] Date NULL,
 [UpdateType] Varchar (50)
)
ON [PRIMARY]

CREATE TABLE #rev
(
 [DividendsID] Int NOT NULL,
 [GroupSeries] Varchar(10) NULL,
 [ShortName] Varchar(13) NULL,
 [CUSIP] Varchar(10) NULL,
 [CUSIPDescription] Varchar(51) NULL,
 [TransactionID] Varchar(5) NULL,
 [SubType] Varchar(5) NULL,
 [Interest] Decimal(14,2) NULL,
 [Shares] Decimal(14,3) NULL,
 [Rate] Decimal(12,5) NULL,
 [ExDate] Date NULL,
 [PayableDate] Date NULL,
 [AddDate] Date NULL,
 [RecordDate] Date NULL,
 [ReversalDate] Date NULL,
 [Ticker] Varchar(7) NULL,
 [MissedPY] Varchar(1) NULL,
 [IncludeReversals] Varchar(1) NULL,
 [IgnoreFlag] Varchar(1) NULL,
 [IgnoreReason] Varchar(250) NULL,
 [SecurityType] Varchar(20) NULL,
 [SecurityTypeDescription] Varchar(50) NULL,
 [YESAccount] Varchar(50) NULL,
 [DatasetID] Int NULL,
 [ReconciliationID] Int NULL,
 [ReconciliationType] Varchar(50) NULL,
 [Comments] Varchar(250) NULL,
 [ImportDate] Date NULL,
 [UpdateType] Varchar (50)
)
ON [PRIMARY]

HI ALL,

Below is my update statement. The issue my facing is , There are multiple rv.DividendsID for single  DV.DividendsID in the below statement.
In that i want to concatinate the max(rv.DividendsID) for that DividendID in TableA.

For example. While concatinating, I am getting 1454 and 1455 values for rv.DividendsID . In this the final concatination should
be with 1455.

Hi DBALearner,

To concatenate the max(rv,DividentsID) for single DV.DividendsID, add an extra NOT EXISTS in your where clause. I don't see any key column defined in your TABLEB, according to the join conditions, may be a NOT EXISTS as below is good.

NOT EXISTS(SELECT 1 FROM TABLEB B WHERE RV.DividendsID<B.DividendsID
			AND	B.GroupSeries=rv.GroupSeries
			and B.ShortName=rv.ShortName
			and B.[CUSIP]=rv.CUSIP
			and (B.[CUSIPDescription]=rv.CUSIPDescription or (B.CUSIPDescription is NULL and rv.CUSIPDescription is NULL))
			and B.[TransactionID]=rv.TransactionID
			and (B.[SubType]=rv.SubType or (B.SubType is NULL and rv.SubType is NULL))
			and B.[Interest]=rv.Interest
			and B.[Shares]=rv.Shares
			and B.[Rate]=rv.Rate
			and B.[ExDate]=rv.ExDate
			and B.[PayableDate]=rv.PayableDate
			and B.[AddDate]=rv.AddDate
			and B.[RecordDate]=rv.RecordDate
			and (B.[SecurityType]=rv.SecurityType or (B.SecurityType is NULL and rv.SecurityType is NULL))
			and (B.[SecurityTypeDescription]=rv.SecurityTypeDescription or (B.SecurityTypeDescription is NULL 
			and rv.SecurityTypeDescription is NULL))
			)

If you have any question, feel free to let me know.