sql server 2012

hi,
  i have two rows with diff name trantype (BC,ODC ) if both already tere i need to give the name as ODI both two rows will show as single row with BC and ODC amount as sum in same column.

pk_id      InstCllnHdr_Fk      TranType_Fk      trantype      Trantype      TrantypeAmt
179650565      156421522      1      BC      ODI      1000
179650566      156421522      15      ODC      ODI      1050
                              
                              
                              
pk_id      InstCllnHdr_Fk      TranType_Fk      trantype      Trantype      TrantypeAmt
179650565      156421522      15      ODI      ODI      2050
                              
i need to show like that.
kowsika deviAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
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.
0
ste5anSenior DeveloperCommented:
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;

Open 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;

Open in new window

0
kowsika deviAuthor Commented:
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
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

kowsika deviAuthor Commented:
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.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
can't think for a better solution, which i think below codes can be improved but it will give you the result you want:

declare @temp table
  (
     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)

;with cte as
(
	select * from @temp where Trantype not in ('BC', 'ODc')
)
, cte2 as
(
	select * from @temp where Trantype in ('BC', 'ODc')
), cte3 as
(
	select a.PK_Id, a.InstCllnHdr_FK, a.TranType_FK,
	a.Trantype,
	case when (a.Trantype = 'BC' and b.Trantype = 'ODc') or (b.Trantype = 'BC' and a.Trantype = 'ODc') then 'ODI' else '' end TrantypeX,
	a.TranTypeAmt, 
	sum(case when (a.Trantype = 'BC' and b.Trantype = 'ODc') or (b.Trantype = 'BC' and a.Trantype = 'ODc') then a.TranTypeAmt+b.TranTypeAmt else 0 end) TranTypeAmtX,
	row_number() over (order by a.TranType_FK desc) idx
	from cte2 a cross apply cte2 b
	group by a.PK_Id, a.InstCllnHdr_FK, a.TranType_FK, a.Trantype, a.TranTypeAmt, b.Trantype
)
select PK_Id, InstCllnHdr_FK, TranType_FK, Trantype, TranTypeAmt
from cte
union
select PK_Id, InstCllnHdr_FK, TranType_FK,
case when TrantypeX <> '' then TrantypeX else Trantype end,
case when TranTypeAmtX > 0 then TranTypeAmtX else TranTypeAmt end 
from cte3 where idx = 1
order by 1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kowsika deviAuthor Commented:
can u pls explain the query
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
0
kowsika deviAuthor Commented:
thanku Ryan Chong  jj
0
kowsika deviAuthor Commented:
thanku :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.