sql server 2012

What's the "rule" for the 1 and 15 ? why did that become 15 ?

Looks like we roll into first row, sum amounts and make first trantype ODI.

What happens to the second row ? Delete I presume ?

Need more "rules" as to why and when we combine rows....

Example, they have the same  InstCllnHdr_Fk, they are adjacent rows, and so forth. The more information, the better.

Sounds like a transaction table. Thus it is pretty uncommon to modify its contents..

I would start with

DECLARE @Sample TABLE
    (
        pk_id INT ,
        InstCllnHdr_Fk INT ,
        TranType_Fk INT ,
        trantype CHAR(3) ,
        trantype2 CHAR(3) ,
        TrantypeAmt INT
    );

INSERT INTO @Sample ( pk_id ,
                      InstCllnHdr_Fk ,
                      TranType_Fk ,
                      trantype ,
                      trantype2 ,
                      TrantypeAmt )
VALUES ( 65, 156421522, 1, 'BC', 'ODI', 1000 ) ,
       ( 66, 156421522, 15, 'ODC', 'ODI', 1050 ) ,
       ( 63, 156421523, 1, 'BC', 'ODI', 1000 ) ,
       ( 64, 156421521, 15, 'ODC', 'ODI', 1050 );

SELECT O.* ,
       IIF(
           1 = (   SELECT COUNT(*)
                   FROM   @Sample I
                   WHERE  I.InstCllnHdr_Fk = O.InstCllnHdr_Fk
                          AND I.trantype2 = O.trantype2
                          AND (   I.TranType_Fk = 1
                                  AND I.trantype = 'BC'
                                  AND O.TranType_Fk = 15
                                  AND O.trantype = 'ODC'
                                  OR I.TranType_Fk = 15
                                     AND I.trantype = 'ODC'
                                     AND O.TranType_Fk = 1
                                     AND O.trantype = 'BC' )) ,
           'Y' ,
           'N') AS HasCounterpart
FROM   @Sample O;

Select allOpen in new window

E.g.

DECLARE @Sample TABLE
    (
        pk_id INT ,
        InstCllnHdr_Fk INT ,
        TranType_Fk INT ,
        trantype CHAR(3) ,
        trantype2 CHAR(3) ,
        TrantypeAmt INT
    );

INSERT INTO @Sample ( pk_id ,
                      InstCllnHdr_Fk ,
                      TranType_Fk ,
                      trantype ,
                      trantype2 ,
                      TrantypeAmt )
VALUES ( 65, 156421522, 1, 'BC', 'ODI', 1000 ) ,
       ( 66, 156421522, 15, 'ODC', 'ODI', 1050 ) ,
       ( 63, 156421523, 1, 'BC', 'ODI', 1000 ) ,
       ( 64, 156421521, 15, 'ODC', 'ODI', 1050 );

WITH PairedRows
AS ( SELECT O.*
     FROM   @Sample O
     WHERE  1 = (   SELECT COUNT(*)
                    FROM   @Sample I
                    WHERE  I.InstCllnHdr_Fk = O.InstCllnHdr_Fk
                           AND I.trantype2 = O.trantype2
                           AND (   I.TranType_Fk = 1
                                   AND I.trantype = 'BC'
                                   AND O.TranType_Fk = 15
                                   AND O.trantype = 'ODC'
                                   OR I.TranType_Fk = 15
                                      AND I.trantype = 'ODC'
                                      AND O.TranType_Fk = 1
                                      AND O.trantype = 'BC' ))) ,
     UnpairedRows
AS ((SELECT O.*
     FROM   @Sample O
     WHERE  1 != (   SELECT COUNT(*)
                     FROM   @Sample I
                     WHERE  I.InstCllnHdr_Fk = O.InstCllnHdr_Fk
                            AND I.trantype2 = O.trantype2
                            AND (   I.TranType_Fk = 1
                                    AND I.trantype = 'BC'
                                    AND O.TranType_Fk = 15
                                    AND O.trantype = 'ODC'
                                    OR I.TranType_Fk = 15
                                       AND I.trantype = 'ODC'
                                       AND O.TranType_Fk = 1
                                       AND O.trantype = 'BC' ))))
SELECT   MIN(pk_id) ,
         PairedRows.InstCllnHdr_Fk ,
         15 ,
         'ODI' ,
         'ODI' ,
         SUM(TrantypeAmt)
FROM     PairedRows
GROUP BY InstCllnHdr_Fk
UNION ALL
SELECT *
FROM   UnpairedRows;

Select allOpen in new window

CREATE TABLE #temp
  (
     PK_Id          INT IDENTITY (1, 1),
     InstCllnHdr_FK INT,
     TranType_FK    INT,
     Trantype       VARCHAR (10),
     TranTypeAmt    NUMERIC (18, 2)
  )



Insert into #temp  VALUES(156421522,28,'ARS',19000.00)

Insert into #temp VALUES  (156421522,1,'BC',1000.00)

Insert into #temp  VALUES (156421522,15,'ODc',      1050.00)

Insert into #temp VALUES  (156421522,14,'SEZ',      1050.00)

SELECT * FROM #temp

Iam giving sample table structure if trantype BC and ODC  means sum amount will display in trantypeamt column else simply that amount wll show.

Expected output is :

PK_Id      InstCllnHdr_FK      TranType_FK      Trantype      TranTypeAmt
1      156421522      28      ARS      19000.00
3      156421522      15      ODI      2050.00
4      156421522      14      SEZ      1050.00

Iam giving sample table structure if trantype BC and ODC  means trantype changes as ODI  and  sum amount will display in trantypeamt column else simply that amount wll show.

can u pls explain the query

i'm splitting the records into 2 groups:

1. first group (cte) contains records with Trantype not equals to BC / ODc, this will be the records that not being affected.
2. second group (cte3) contains the calculated results based on the requirements

assuming there will be just max 1 pair of BC / ODc, i do a self join (using cross apply) to get the total amt, with the condition the first Trantype must be BC and the second Trantype must be ODc, OR first Trantype must be ODc and the second Trantype must be BC.

in addition, I'm using row_number so that it only return 1 record from this pool of records

later i use union clause to join this statements together to produce the final result.

hope this clarifies

thanku Ryan Chong  jj

thanku :-)