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

• 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 Wednesday, November 7, 2018 3:36 PM
Wednesday, November 7, 2018 3:21 PM

• ```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

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

• 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
```