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
Title | # Comments | Views | Activity |
---|---|---|---|
Cannot resolve the collation - tempdb..#Carriers | 3 | 44 | |
SQl server restarts itself | 6 | 29 | |
configure service broker on all databases | 2 | 69 | |
Analysis of table use | 7 | 30 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
15 Experts available now in Live!