With attempting to convert a crystal report formula into a SQL CASE expression, I seem to be having trouble understanding the concept of "total_pallet_weight" and "order_no" utilizing the "AND" in the formula with "cubic feet" and "order_no". I thought with my attempt below I would start out with a CASE just for "total_pallet_weight" then have a sub CASE for cubic feet to represent the "AND" in the formula. But I'm not to familiar with how the syntax "({a_ras_shipping_order_PCF_vw.total_pallet_weight}, {a_ras_truck_shipment_vw.order_no}) > 0" works. Is the forumla saying "total pallet weight + order_no > 0"?
Crystal Report Formula:
IF Sum ({a_ras_shipping_order_PCF_vw.total_pallet_weight},
{a_ras_truck_shipment_vw.order_no}) > 0
AND Sum ({a_ras_shipping_order_PCF_vw.cubic_feet},
{a_ras_truck_shipment_vw.order_no}) > 0
THEN
Sum ({a_ras_shipping_order_PCF_vw.total_pallet_weight},
{a_ras_truck_shipment_vw.order_no})
/Sum ({a_ras_shipping_order_PCF_vw.cubic_feet},
{a_ras_truck_shipment_vw.order_no})
ELSE 0
Attempt at replacing formula:
CASE WHEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) > 0
THEN
,CASE WHEN (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches *
TPM.dim_ext_x * TPM.dim_ext_y) / 1728))) > 0
THEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) +
(SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches *
TPM.dim_ext_x * TPM.dim_ext_y) / 1728)))
ELSE 0
END
ELSE 0
END AS 'Total_PCF'
Syntax for "total pallet weight"
SUM (TCT.[weight]) + SUM(TPM.[weight]) AS 'Total_Pallet_Weight'
Syntax for "cubic feet"
CASE WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND
TPM.dim_ext_y > 0
THEN CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x *
TPM.dim_ext_y) / 1728)
ELSE 0 END AS 'cubic_feet',