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?
 
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
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Ryan ChongCommented:
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
 
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 ChongCommented:
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
 
Ryan ChongCommented:
,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
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.

All Courses

From novice to tech pro — start learning today.