• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 50
  • Last Modified:

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.
0
kowsika devi
Asked:
kowsika devi
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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 ChongCommented:
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
 
kowsika deviAuthor Commented:
can u pls explain the query
0
 
Ryan ChongCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now