none
How would I get my second calculation result rows to appear after my first? RRS feed

  • Question

  • I have my first results that I want to display all the PartNo and Qty. 

    Select 
    PartNo,
     Case when Flag = 1
    		Then [Full Cartons]
    		Else Qty
    		End
    From
    (
    Select * , 
     Qty/per as [# of Cartons],
     FLOOR(Qty/per) as [Full Cartons],
     (Qty/per) - FLOOR(Qty/per) as Remainder,
     ((Qty/per) - FLOOR(Qty/per)) * (Per) as [In Pieces] 
    From Impact
    ) Q

    Then I want the results with  PartNo and [In Pieces] to show up right after.

    Select 
    PartNo,
    [In Pieces]
    From 
    (
    Select
    *,  Qty/per as [# of Cartons],
     FLOOR(Qty/per) as [Full Cartons],
     (Qty/per) - FLOOR(Qty/per) as Remainder,
     ((Qty/per) - FLOOR(Qty/per)) * (Per) as [In Pieces]
    From Impact
    ) Q
    where Flag = 1
    My question is how I can I combine the two results of data?
    Create table dbo.Impact
     (
    PartNo char(25) not null,
    Qty decimal not null,
    Flag bit not null,
    Per int not null
    );
    
    Insert into dbo.Impact (PartNo, Qty,Flag,Per)
    Values
    ('30248',1.00,0,''),
    ('351005',1.00,0,''),
    ('37010',3.00,0,''),
    ('ANF-11-20279.0',7.00,0,'') ,      
    ('ANF-11-20280.0',12.00,0,''),
     ('ANF-11-30205',2.00,0,''),
     ('ANF-11-30206',4.00,0,''),
     ('ANF-11-30211',100.0,1,12),
     ('ANF-11-30234',8.00,1,10),
     ('ANF-11-30235',32.00,1,10),
    ( 'ANF-11-30245',7.00,1,10),
     ('ANF-11-30247',53.00,1,12),
     ('ANF-11-30392',2.00,0,''),
     ('ANF-11-30394',1.00,0,''),
     ('ANF-11-31211',2.00,0,''),
     ('ANF-11-35052.1',2.00,0,''),
     ('ANF-11-35053.1',2.00,0,''),
     ('ANF-11-35061',4.00,0,'')


    • Edited by David9501 Wednesday, November 7, 2018 3:36 PM
    Wednesday, November 7, 2018 3:21 PM

Answers

  • Create table dbo.Impact
     (
    PartNo char(25) not null,
    Qty decimal not null,
    Flag bit not null,
    Per int not null
    );
    
    Insert into dbo.Impact (PartNo, Qty,Flag,Per)
    Values
    ('30248',1.00,0,''),
    ('351005',1.00,0,''),
    ('37010',3.00,0,''),
    ('ANF-11-20279.0',7.00,0,'') ,      
    ('ANF-11-20280.0',12.00,0,''),
     ('ANF-11-30205',2.00,0,''),
     ('ANF-11-30206',4.00,0,''),
     ('ANF-11-30211',100.0,1,12),
     ('ANF-11-30234',8.00,1,10),
     ('ANF-11-30235',32.00,1,10),
    ( 'ANF-11-30245',7.00,1,10),
     ('ANF-11-30247',53.00,1,12),
     ('ANF-11-30392',2.00,0,''),
     ('ANF-11-30394',1.00,0,''),
     ('ANF-11-31211',2.00,0,''),
     ('ANF-11-35052.1',2.00,0,''),
     ('ANF-11-35053.1',2.00,0,''),
     ('ANF-11-35061',4.00,0,'')
    
    
    Select 
    PartNo,
     Case when Flag = 1
    		Then [Full Cartons]
    		Else Qty
    		End as [In Pieces]
    From
    (
    Select * , 
     Qty/per as [# of Cartons],
     FLOOR(Qty/per) as [Full Cartons],
     (Qty/per) - FLOOR(Qty/per) as Remainder,
     ((Qty/per) - FLOOR(Qty/per)) * (Per) as [In Pieces] 
    From Impact
    ) Q
    UNION ALL
    Select 
    PartNo,
    [In Pieces]
    From 
    (
    Select
    *,  Qty/per as [# of Cartons],
     FLOOR(Qty/per) as [Full Cartons],
     (Qty/per) - FLOOR(Qty/per) as Remainder,
     ((Qty/per) - FLOOR(Qty/per)) * (Per) as [In Pieces]
    From Impact
    ) Q
    where Flag = 1
    
    
     drop table dbo.Impact

    Wednesday, November 7, 2018 3:42 PM
    Moderator

All replies

  • Create table dbo.Impact
     (
    PartNo char(25) not null,
    Qty decimal not null,
    Flag bit not null,
    Per int not null
    );
    
    Insert into dbo.Impact (PartNo, Qty,Flag,Per)
    Values
    ('30248',1.00,0,''),
    ('351005',1.00,0,''),
    ('37010',3.00,0,''),
    ('ANF-11-20279.0',7.00,0,'') ,      
    ('ANF-11-20280.0',12.00,0,''),
     ('ANF-11-30205',2.00,0,''),
     ('ANF-11-30206',4.00,0,''),
     ('ANF-11-30211',100.0,1,12),
     ('ANF-11-30234',8.00,1,10),
     ('ANF-11-30235',32.00,1,10),
    ( 'ANF-11-30245',7.00,1,10),
     ('ANF-11-30247',53.00,1,12),
     ('ANF-11-30392',2.00,0,''),
     ('ANF-11-30394',1.00,0,''),
     ('ANF-11-31211',2.00,0,''),
     ('ANF-11-35052.1',2.00,0,''),
     ('ANF-11-35053.1',2.00,0,''),
     ('ANF-11-35061',4.00,0,'')
    
    
    Select 
    PartNo,
     Case when Flag = 1
    		Then [Full Cartons]
    		Else Qty
    		End as [In Pieces]
    From
    (
    Select * , 
     Qty/per as [# of Cartons],
     FLOOR(Qty/per) as [Full Cartons],
     (Qty/per) - FLOOR(Qty/per) as Remainder,
     ((Qty/per) - FLOOR(Qty/per)) * (Per) as [In Pieces] 
    From Impact
    ) Q
    UNION ALL
    Select 
    PartNo,
    [In Pieces]
    From 
    (
    Select
    *,  Qty/per as [# of Cartons],
     FLOOR(Qty/per) as [Full Cartons],
     (Qty/per) - FLOOR(Qty/per) as Remainder,
     ((Qty/per) - FLOOR(Qty/per)) * (Per) as [In Pieces]
    From Impact
    ) Q
    where Flag = 1
    
    
     drop table dbo.Impact

    Wednesday, November 7, 2018 3:42 PM
    Moderator

  • It can be either of the below depending on what you want

    1.

    ;With CTE
    AS
    (
    Select * , 
     Qty/per as [# of Cartons],
     FLOOR(Qty/per) as [Full Cartons],
     (Qty/per) - FLOOR(Qty/per) as Remainder,
     ((Qty/per) - FLOOR(Qty/per)) * (Per) as [In Pieces] 
    From Impact
    )
    
    SELECT PartNo,
    CASE WHEN Flag = 1 THEN [Full Cartons] ELSE Qty END 
    FROM CTE
    
    UNION ALL
    
    SELECT PartNo,
    [In Pieces]
    FROM CTE
    WHERE Flag = 1

    Or

    2.

    ;With CTE
    AS
    (
    Select * , 
     Qty/per as [# of Cartons],
     FLOOR(Qty/per) as [Full Cartons],
     (Qty/per) - FLOOR(Qty/per) as Remainder,
     ((Qty/per) - FLOOR(Qty/per)) * (Per) as [In Pieces] 
    From Impact
    )
    
    SELECT PartNo,
    CASE WHEN Flag = 1 THEN [Full Cartons] ELSE Qty END,
    CASE WHEN Flag = 1 THEN [In Pieces] END 
    FROM CTE
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, November 7, 2018 4:01 PM