Set '0' in duplicate row using SQL?

I have following table ,

 CREATE TABLE StockTransferDetails    
    (    
          StockTransferDetailsId numeric(18,0),
          StockTransferID numeric(18,0),
          SerialNo smallint,
          ItemMasterId smallint,
          Unit varchar(10),
          UnitQuantity decimal(18,3),
          TransactionType char(1),
          ReferenceStockTransferDetailsId  numeric(18,0)
    )
   
    INSERT INTO StockTransferDetails VALUES    
    (79189,      9360,      1,      11111,      'BOX',      52,      'I',      0),    
    (79190,      9360,      2,      6777,      'BOX',  52,      'I',      0),    
    (79191,      9360,      1,      11438,      'BOX',      2,      'R',      79189),
    (79192,      9360,      1,      9117,      'BOX',      2,      'R',      79190),    
    (79193,      9360,      1,      11361,      'BOX',      34,      'O',      79189),
    (79194,      9360,      1,      1172,      'BOX',      33,      'O',      79190),
    (79195,      9360,      2,      11433,      'BOX',      1,      'O',      79190),
    (83657,      9776,      1,      16035,      'BOX',      1,      'I',      0),    
    (83658,      9776,      2,      11284,      'BOX',      5,      'I',      0),    
    (83659,      9776,      1,      609,      'BOX',      1,      'O',      83657),    
    (83660,      9776,      1,      478      ,   'BOX',      4,      'O',      83658),    
    (83661,      9776,      2,      6195,      'BOX',      1,      'O',      83658),    
    (101,      9000,      1,      1000,      'BOX',      10,      'I',      0),    
    (102,      9000,      2,      11284,      'BOX',      5,      'I',      0),    
    (103,      9000,      1,      1000,      'BOX',      1,      'O',      101),    
    (104,      9000,      1,      1000,      'BOX',      4,      'O',      101),    
    (105,      9000,      2,      1002,      'BOX',      1,      'O',      101)

I prepared one SQL,

 Create table #FinishedReceipt
    (
          StockTransferID numeric(18,0),
          StockTransferDetailsId numeric(18,0),
          SerialNo smallint,
          ItemMasterId smallint,
          Unit varchar(10),
          UnitQuantity decimal(18,3),
          TransactionType char(1),
          ReferenceStockTransferDetailsId  numeric(18,0)      
    )
    Insert into #FinishedReceipt
    select
    StockTransferId
    ,StockTransferDetailsId
    ,SerialNo
    ,ItemMasterId
    ,Unit
    ,UnitQuantity
    ,TransactionType
    ,ReferenceStockTransferDetailsId
    from StockTransferDetails
    where
    TransactionType = 'I'

    Create table #ByProductReceipt
    (
          StockTransferID numeric(18,0),
          StockTransferDetailsId numeric(18,0),
          SerialNo smallint,
          ItemMasterId smallint,
          Unit varchar(10),
          UnitQuantity decimal(18,3),
          TransactionType char(1),
          ReferenceStockTransferDetailsId  numeric(18,0)      
    )
    Insert into #ByProductReceipt
    select
    StockTransferId
    ,StockTransferDetailsId
    ,SerialNo
    ,ItemMasterId
    ,Unit
    ,UnitQuantity
    ,TransactionType
    ,ReferenceStockTransferDetailsId
    from StockTransferDetails
    where
    TransactionType = 'R'

    Create table #StockIssue
    (      StockTransferID numeric(18,0),
          StockTransferDetailsId numeric(18,0),
          SerialNo smallint,
          ItemMasterId smallint,
          Unit varchar(10),
          UnitQuantity decimal(18,3),
          TransactionType char(1),
          ReferenceStockTransferDetailsId  numeric(18,0)
    )

    Insert into #StockIssue
    select
    StockTransferId
    ,StockTransferDetailsId
    ,SerialNo
    ,ItemMasterId
    ,Unit
    ,UnitQuantity
    ,TransactionType
    ,ReferenceStockTransferDetailsId
    from StockTransferDetails
    where
    TransactionType = 'O'

    select
    FR.*
    ,BR.ReferenceStockTransferDetailsId as  BYProductReferenceStockTransferDetailsId
    ,BR.SerialNo as ByProductSerialNo
    ,BR.ItemMasterId as ByProductItemMasterId
    ,BR.Unit as ByProductUnit
    ,BR.UnitQuantity as ByProductUnitQuantity
    ,BR.TransactionType as ByProductTransactionType    
    ,SI.ReferenceStockTransferDetailsId as  StockIssueReferenceStockTransferDetailsId
    ,SI.SerialNo as StockIssueSerialNo
    ,SI.ItemMasterId as StockIssueItemMasterId
    ,SI.Unit as StockIssueUnit
    ,SI.UnitQuantity as StockIssueUnitQuantity
    ,SI.TransactionType as StockIssueTransactionType
    from #FinishedReceipt FR
    LEFT OUTER JOIN #ByProductReceipt BR ON BR.ReferenceStockTransferDetailsId =  FR.StockTransferDetailsId AND BR.StockTransferId =  FR.StockTransferId
    LEFT OUTER JOIN #StockIssue SI ON SI.ReferenceStockTransferDetailsId =  FR.StockTransferDetailsId AND SI.StockTransferId =  FR.StockTransferId

    Drop table #FinishedReceipt
    Drop table #ByProductReceipt
    Drop table #StockIssue


When i have execute query i  will get output but i got same row in child table because parent contains two diiferent ids for Child So i want set unitQuanity '0' if it is repated.Please give me coorected SQl.

Please see image I want output like this
Payal sathavaraWindows Developer Asked:
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.

ste5anSenior DeveloperCommented:
[..] but i got same row in child table because parent contains two diiferent ids for Child [..]
In this case you need to repair the data model, cause it violates 2NF.
0
Pawan KumarDatabase ExpertCommented:
Please try this fix or you can changes your data model.

;WITH CTE AS
(
	select 
	ROW_NUMBER() OVER (PARTITION BY FR.StockTransferId,FR.StockTransferDetailsId ORDER BY (SELECT NULL)) RNK
	,FR.*
	,BR.ReferenceStockTransferDetailsId as  BYProductReferenceStockTransferDetailsId
	,BR.SerialNo as ByProductSerialNo
	,BR.ItemMasterId as ByProductItemMasterId
	,BR.Unit as ByProductUnit
	,BR.UnitQuantity as ByProductUnitQuantity
	,BR.TransactionType as ByProductTransactionType    
	,SI.ReferenceStockTransferDetailsId as  StockIssueReferenceStockTransferDetailsId
	,SI.SerialNo as StockIssueSerialNo
	,SI.ItemMasterId as StockIssueItemMasterId
	,SI.Unit as StockIssueUnit
	,SI.UnitQuantity as StockIssueUnitQuantity
	,SI.TransactionType as StockIssueTransactionType	
	from #FinishedReceipt FR 
	LEFT OUTER JOIN #ByProductReceipt BR ON BR.ReferenceStockTransferDetailsId =  FR.StockTransferDetailsId AND BR.StockTransferId =  FR.StockTransferId 
	LEFT OUTER JOIN #StockIssue SI ON SI.ReferenceStockTransferDetailsId =  FR.StockTransferDetailsId AND SI.StockTransferId =  FR.StockTransferId
)
SELECT  
  StockTransferID                         
 ,StockTransferDetailsId                  
 ,SerialNo 
 ,ItemMasterId 
 ,Unit       
 ,CASE WHEN rnk = 1 THEN UnitQuantity ELSE 0 END UnitQuantity
 ,TransactionType 
 ,ReferenceStockTransferDetailsId         
 ,BYProductReferenceStockTransferDetailsId 
 ,ByProductSerialNo 
 ,ByProductItemMasterId 
 ,ByProductUnit 
 ,CASE WHEN rnk = 1 THEN ByProductUnitQuantity ELSE 0 END ByProductUnitQuantity
 ,ByProductTransactionType 
 ,StockIssueReferenceStockTransferDetailsId 
 ,StockIssueSerialNo 
 ,StockIssueItemMasterId 
 ,StockIssueUnit StockIssueUnitQuantity                  
 ,StockIssueTransactionType
FROM CTE

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
StockTransferID                         StockTransferDetailsId                  SerialNo ItemMasterId Unit       UnitQuantity                            TransactionType ReferenceStockTransferDetailsId         BYProductReferenceStockTransferDetailsId ByProductSerialNo ByProductItemMasterId ByProductUnit ByProductUnitQuantity                   ByProductTransactionType StockIssueReferenceStockTransferDetailsId StockIssueSerialNo StockIssueItemMasterId StockIssueUnitQuantity StockIssueTransactionType
--------------------------------------- --------------------------------------- -------- ------------ ---------- --------------------------------------- --------------- --------------------------------------- ---------------------------------------- ----------------- --------------------- ------------- --------------------------------------- ------------------------ ----------------------------------------- ------------------ ---------------------- ---------------------- -------------------------
9000                                    101                                     1        1000         BOX        10.000                                  I               0                                       NULL                                     NULL              NULL                  NULL          NULL                                    NULL                     101                                       1                  1000                   BOX                    O
9000                                    101                                     1        1000         BOX        0.000                                   I               0                                       NULL                                     NULL              NULL                  NULL          0.000                                   NULL                     101                                       1                  1000                   BOX                    O
9000                                    101                                     1        1000         BOX        0.000                                   I               0                                       NULL                                     NULL              NULL                  NULL          0.000                                   NULL                     101                                       2                  1002                   BOX                    O
9000                                    102                                     2        11284        BOX        5.000                                   I               0                                       NULL                                     NULL              NULL                  NULL          NULL                                    NULL                     NULL                                      NULL               NULL                   NULL                   NULL
9360                                    79189                                   1        11111        BOX        52.000                                  I               0                                       79189                                    1                 11438                 BOX           2.000                                   R                        79189                                     1                  11361                  BOX                    O
9360                                    79190                                   2        6777         BOX        52.000                                  I               0                                       79190                                    1                 9117                  BOX           2.000                                   R                        79190                                     1                  1172                   BOX                    O
9360                                    79190                                   2        6777         BOX        0.000                                   I               0                                       79190                                    1                 9117                  BOX           0.000                                   R                        79190                                     2                  11433                  BOX                    O
9776                                    83657                                   1        16035        BOX        1.000                                   I               0                                       NULL                                     NULL              NULL                  NULL          NULL                                    NULL                     83657                                     1                  609                    BOX                    O
9776                                    83658                                   2        11284        BOX        5.000                                   I               0                                       NULL                                     NULL              NULL                  NULL          NULL                                    NULL                     83658                                     1                  478                    BOX                    O
9776                                    83658                                   2        11284        BOX        0.000                                   I               0                                       NULL                                     NULL              NULL                  NULL          0.000                                   NULL                     83658                                     2                  6195                   BOX                    O

(10 row(s) affected)

Open in new window

1
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
if null and record ordering is crucial, you can try this too:

;with cte as
(
select
    row_number() over (order by (select 1)) idx,
    row_number() over (partition by FR.StockTransferId,FR.StockTransferDetailsId order by FR.StockTransferId,FR.StockTransferDetailsId) rk
    ,FR.*
    ,BR.ReferenceStockTransferDetailsId as  BYProductReferenceStockTransferDetailsId
    ,BR.SerialNo as ByProductSerialNo
    ,BR.ItemMasterId as ByProductItemMasterId
    ,BR.Unit as ByProductUnit
    ,BR.UnitQuantity as ByProductUnitQuantity
    ,BR.TransactionType as ByProductTransactionType    
    ,SI.ReferenceStockTransferDetailsId as  StockIssueReferenceStockTransferDetailsId
    ,SI.SerialNo as StockIssueSerialNo
    ,SI.ItemMasterId as StockIssueItemMasterId
    ,SI.Unit as StockIssueUnit
    ,SI.UnitQuantity as StockIssueUnitQuantity
    ,SI.TransactionType as StockIssueTransactionType
    from #FinishedReceipt FR
    LEFT OUTER JOIN #ByProductReceipt BR ON BR.ReferenceStockTransferDetailsId =  FR.StockTransferDetailsId AND BR.StockTransferId =  FR.StockTransferId
    LEFT OUTER JOIN #StockIssue SI ON SI.ReferenceStockTransferDetailsId =  FR.StockTransferDetailsId AND SI.StockTransferId =  FR.StockTransferId
)
select
	StockTransferId
    ,StockTransferDetailsId
    ,SerialNo
    ,ItemMasterId
    ,Unit
    ,case when rk = 1 then UnitQuantity else 0 end UnitQuantity
    --,TransactionType
    --,ReferenceStockTransferDetailsId 
    ,BYProductReferenceStockTransferDetailsId
    ,ByProductSerialNo
    ,ByProductItemMasterId
    --,ByProductUnit
    ,case when rk = 1 then ByProductUnitQuantity else case when ByProductUnitQuantity is null then null else 0 end end ByProductUnitQuantity
    --,ByProductTransactionType
    ,StockIssueReferenceStockTransferDetailsId
    ,StockIssueSerialNo
    ,StockIssueItemMasterId
    --,StockIssueUnit
    ,StockIssueUnitQuantity
    --,StockIssueTransactionType
    from cte
    Order By idx

Open in new window

1
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Pawan KumarDatabase ExpertCommented:
Hi Ryan
Your solution is exactly same as mine. :)

Also below bold is unnecessary. when ByProductUnitQuantity is null then again you are making it NULL. :)
else case when ByProductUnitQuantity is null then null else 0
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Your solution is exactly same as mine. :)
I know it's similar but it's not exactly the same :) in which i made a claim that if null and record ordering is crucial

Also below bold is unnecessary. when ByProductUnitQuantity is null then again you are making it NULL. :)
else case when ByProductUnitQuantity is null then null else 0

well, that's not exactly right... if showing the result if null or 0 is crucial.

since the statement is comparing with rk and then its original value.

test 1:
case when rk = 1 then ByProductUnitQuantity else case when ByProductUnitQuantity is null then null else 0 end end ByProductUnitQuantity

Open in new window

this would generate ByProductUnitQuantity = null if rk <> 1 and its original value is null

test 2:
case when rk = 1 then ByProductUnitQuantity else 0 end ByProductUnitQuantity

Open in new window

this would generate ByProductUnitQuantity = 0 if rk <> 1 and its original value is null

your output:
Untitled.png
my output:
Untitled1.png
hope this clarifies
0
Pawan KumarDatabase ExpertCommented:
Well the solution is same, CTE,Ranking, first case statement. Only this little case is the addition in the column ByProductUnitQuantity. :)

Ok, Even if NULL(Author will confirm it.) is required double case is not required. We can do like below -
,CASE WHEN rnk <> 1 AND ByProductUnitQuantity IS NOT NULL THEN 0 ELSE ByProductUnitQuantity END ByProductUnitQuantity

Also note this line row_number() over (order by (select 1)) idx, is unnecessary as order is not asked by the user.

Updated solution if required.

;WITH CTE AS
(
	select 
	ROW_NUMBER() OVER (PARTITION BY FR.StockTransferId,FR.StockTransferDetailsId ORDER BY (SELECT NULL)) RNK
	,FR.*
	,BR.ReferenceStockTransferDetailsId as  BYProductReferenceStockTransferDetailsId
	,BR.SerialNo as ByProductSerialNo
	,BR.ItemMasterId as ByProductItemMasterId
	,BR.Unit as ByProductUnit
	,BR.UnitQuantity as ByProductUnitQuantity
	,BR.TransactionType as ByProductTransactionType    
	,SI.ReferenceStockTransferDetailsId as  StockIssueReferenceStockTransferDetailsId
	,SI.SerialNo as StockIssueSerialNo
	,SI.ItemMasterId as StockIssueItemMasterId
	,SI.Unit as StockIssueUnit
	,SI.UnitQuantity as StockIssueUnitQuantity
	,SI.TransactionType as StockIssueTransactionType	
	from #FinishedReceipt FR 
	LEFT OUTER JOIN #ByProductReceipt BR ON BR.ReferenceStockTransferDetailsId =  FR.StockTransferDetailsId AND BR.StockTransferId =  FR.StockTransferId 
	LEFT OUTER JOIN #StockIssue SI ON SI.ReferenceStockTransferDetailsId =  FR.StockTransferDetailsId AND SI.StockTransferId =  FR.StockTransferId
)
SELECT  
  StockTransferID                         
 ,StockTransferDetailsId                  
 ,SerialNo 
 ,ItemMasterId 
 ,Unit       
 ,CASE WHEN rnk = 1 THEN UnitQuantity ELSE 0 END UnitQuantity
 ,TransactionType 
 ,ReferenceStockTransferDetailsId         
 ,BYProductReferenceStockTransferDetailsId 
 ,ByProductSerialNo 
 ,ByProductItemMasterId 
 ,ByProductUnit 
 ,CASE WHEN rnk <> 1 AND ByProductUnitQuantity IS NOT NULL THEN 0 ELSE ByProductUnitQuantity END ByProductUnitQuantity
 ,ByProductTransactionType 
 ,StockIssueReferenceStockTransferDetailsId 
 ,StockIssueSerialNo 
 ,StockIssueItemMasterId 
 ,StockIssueUnit StockIssueUnitQuantity                  
 ,StockIssueTransactionType
FROM CTE

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
StockTransferID                         StockTransferDetailsId                  SerialNo ItemMasterId Unit       UnitQuantity                            TransactionType ReferenceStockTransferDetailsId         BYProductReferenceStockTransferDetailsId ByProductSerialNo ByProductItemMasterId ByProductUnit ByProductUnitQuantity                   ByProductTransactionType StockIssueReferenceStockTransferDetailsId StockIssueSerialNo StockIssueItemMasterId StockIssueUnitQuantity StockIssueTransactionType
--------------------------------------- --------------------------------------- -------- ------------ ---------- --------------------------------------- --------------- --------------------------------------- ---------------------------------------- ----------------- --------------------- ------------- --------------------------------------- ------------------------ ----------------------------------------- ------------------ ---------------------- ---------------------- -------------------------
9000                                    101                                     1        1000         BOX        10.000                                  I               0                                       NULL                                     NULL              NULL                  NULL          NULL                                    NULL                     101                                       1                  1000                   BOX                    O
9000                                    101                                     1        1000         BOX        0.000                                   I               0                                       NULL                                     NULL              NULL                  NULL          NULL                                    NULL                     101                                       1                  1000                   BOX                    O
9000                                    101                                     1        1000         BOX        0.000                                   I               0                                       NULL                                     NULL              NULL                  NULL          NULL                                    NULL                     101                                       2                  1002                   BOX                    O
9000                                    102                                     2        11284        BOX        5.000                                   I               0                                       NULL                                     NULL              NULL                  NULL          NULL                                    NULL                     NULL                                      NULL               NULL                   NULL                   NULL
9360                                    79189                                   1        11111        BOX        52.000                                  I               0                                       79189                                    1                 11438                 BOX           2.000                                   R                        79189                                     1                  11361                  BOX                    O
9360                                    79190                                   2        6777         BOX        52.000                                  I               0                                       79190                                    1                 9117                  BOX           2.000                                   R                        79190                                     1                  1172                   BOX                    O
9360                                    79190                                   2        6777         BOX        0.000                                   I               0                                       79190                                    1                 9117                  BOX           2.000                                   R                        79190                                     2                  11433                  BOX                    O
9776                                    83657                                   1        16035        BOX        1.000                                   I               0                                       NULL                                     NULL              NULL                  NULL          NULL                                    NULL                     83657                                     1                  609                    BOX                    O
9776                                    83658                                   2        11284        BOX        5.000                                   I               0                                       NULL                                     NULL              NULL                  NULL          NULL                                    NULL                     83658                                     1                  478                    BOX                    O
9776                                    83658                                   2        11284        BOX        0.000                                   I               0                                       NULL                                     NULL              NULL                  NULL          NULL                                    NULL                     83658                                     2                  6195                   BOX                    O

(10 row(s) affected)

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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
,CASE WHEN rnk <> 1 AND ByProductUnitQuantity IS NOT NULL THEN 0 ELSE ByProductUnitQuantity END ByProductUnitQuantity
yes, no issue on that, it's just a matter how we wish to write the conditions. but you got a more clever way to write that

Also note this line row_number() over (order by (select 1)) idx, is unnecessary.
that field is for sorting purposes, in case that's crucial :)
2
Pawan KumarDatabase ExpertCommented:
Question abandoned.
Provided tested solutions.
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.