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
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
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
SQL Server DeDupe Table | 3 | 35 | |
Passing value to a stored procedure | 8 | 89 | |
Analysis of table use | 7 | 40 | |
Query to Add Late Tolerance | 10 | 59 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
7 Experts available now in Live!