Be seen. Boost your questionâ€™s priority for more expert views and faster solutions
Select
PktCtrlNbr,
Lane,
LaneId,
LaneInt,
Wave,
Case
When PickArea = 'UN1' Then Ceiling(Sum(PickQty))
Else
0
End As Un1Qy,
Case
When PickArea = 'UN2' Then Ceiling(Sum(PickQty))
Else
0
End As Un2Qy,
Case
When PickArea = 'NCP' Then Ceiling(Sum(PickQty / PackQty))
Else
0
End As CpnQy,
Case
When PickArea = 'OCP' Then Ceiling(Sum(PickQty / PackQty))
Else
0
End As CpoQy,
Case
When PickArea = 'NCP' Or PickArea = 'OCP' Then Ceiling(Sum(PickQty / PackQty))
Else
0
End As CPQy,
Case
When PickArea = 'EOS' Or PickArea = 'COS' Or PickArea = 'NRS' Or
PickArea = 'ORS' Then Ceiling(Sum(PickQty / PackQty))
Else
0
End As RsvQy,
Case
When PickArea = 'EOS' Or PickArea = 'COS' Or PickArea = 'NRS' Or
PickArea = 'ORS' Then Ceiling(Sum(PackVol * (PickQty / PackQty)))
Else
0
End As RsvVl,
Case
When PickArea = 'UN1' Or PickArea = 'UN2' Then Ceiling(Sum(UnitWt * PickQty))
When PickArea = 'NCP' Or PickArea = 'OCP' Or PickArea = 'EOS' Or
PickArea = 'COS' Or PickArea = 'NRS' Or PickArea = 'ORS' Then Ceiling(Sum(PackWt * (PickQty / PackQty)))
End As Weight,
Case
When PickArea = 'UN1' Or PickArea = 'UN2' Then Ceiling(Sum(UnitVol * PickQty))
When PickArea = 'NCP' Or PickArea = 'OCP' Or PickArea = 'EOS' Or
PickArea = 'COS' Or PickArea = 'NRS' Or PickArea = 'ORS' Then Ceiling(Sum(PackVol * (PickQty / PackQty)))
End As Volume,
Shipto,
Customer,
Address1,
Address2,
Address3,
City,
State,
ZipCode,
Country,
CustPO,
Routing
FROM
OrderVolume
Group By
Wave,
PktCtrlNbr,
PickArea,
Lane,
LaneId,
LaneInt,
Wave,
Shipto,
Customer,
Address1,
Address2,
Address3,
City,
State,
ZipCode,
Country,
CustPO,
Routing
Select
PktCtrlNbr,
Lane,
LaneId,
LaneInt,
Wave,
Case
When PickArea = 'UN1' Then Ceiling(Sum(PickQty))
Else
0
End As Un1Qy,
Case
When PickArea = 'UN2' Then Ceiling(Sum(PickQty))
Else
0
End As Un2Qy,
Case
When PickArea = 'NCP' And IsNull(PackQty, 0) <> 0 Then Ceiling(Sum(PickQty / PackQty))
Else
0
End As CpnQy,
Case
When PickArea = 'OCP' And IsNull(PackQty, 0) <> 0 Then Ceiling(Sum(PickQty / PackQty))
Else
0
End As CpoQy,
Case
When PickArea IN ('NCP', 'OCP') And IsNull(PackQty, 0) <> 0 Then Ceiling(Sum(PickQty / PackQty))
Else
0
End As CPQy,
Case
When PickArea IN ('EOS', 'COS', 'NRS', 'ORS') And IsNull(PackQty, 0) <> 0 Then Ceiling(Sum(PickQty / PackQty))
Else
0
End As RsvQy,
Case
When PickArea IN ('EOS', 'COS', 'NRS', 'ORS') And IsNull(PackQty, 0) <> 0 Then Ceiling(Sum(PackVol * (PickQty / PackQty)))
Else
0
End As RsvVl,
Case
When PickArea = 'UN1' Or PickArea = 'UN2' Then Ceiling(Sum(UnitWt * PickQty))
When PickArea IN ('NCP', 'OCP', 'EOS', 'COS', 'NRS', 'ORS') And IsNull(PackQty, 0) <> 0 Then Ceiling(Sum(PackWt * (PickQty / PackQty)))
End As Weight,
Case
When PickArea = 'UN1' Or PickArea = 'UN2' Then Ceiling(Sum(UnitVol * PickQty))
When PickArea IN ('NCP', 'OCP', 'EOS', 'COS', 'NRS', 'ORS') And IsNull(PackQty, 0) <> 0 Then Ceiling(Sum(PackVol * (PickQty / PackQty)))
End As Volume,
Shipto,
Customer,
Address1,
Address2,
Address3,
City,
State,
ZipCode,
Country,
CustPO,
Routing
FROM
OrderVolume
Group By
Wave,
PktCtrlNbr,
PickArea,
Lane,
LaneId,
LaneInt,
Wave,
Shipto,
Customer,
Address1,
Address2,
Address3,
City,
State,
ZipCode,
Country,
CustPO,
Routing
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.
Case
When PickArea = 'NCP' Then Ceiling(Sum(PickQty / PackQty))
Else
0
End As CpnQy
to
Case
When PickArea = 'NCP' Then CASE WHEN PackQty =0 THEN NULL ELSE Ceiling(Sum(PickQty / PackQty)) END
Else
0
End As CpnQy