asked on

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.

ste5an

[..] 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.

Pawan Kumar

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

Ryan Chong

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

Pawan Kumar

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

Ryan Chong

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:

my output:

hope this clarifies

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

Question abandoned.
Provided tested solutions.