Bob,
Make sure to put approprite # of '0' in the IIF
SELECT ;
ord.OrderID, ;
SUM( IIF(shp.ShipDate < ldFromDate, shp.ShipAmt, 000000000.00)) AS ShipAmtBefore, ;
SUM( IIF(shp.ShipDate >= ldFromDate, shp.ShipAmt, 000000000.00)) AS ShipAmtAfter ;
FROM Order ord
JOIN Shipment shp ON ord.OrderID = shp.OrderID ;
GROUP BY ord.OrderID
>
>I have two tables and want to write a single SQL statement to return results. The two tables are Order and Shipment. The Order table has OrderID as its PK and the Shipment table has OrderID as a FK. The Shipment table also has a ShipDate column and a ShipAmt column.
>
>What I would like to do is have a result set that shows the OrderID and two columns that are SUM's of ShipAmt. The first SUM should be the sum of ShipAmt for ShipDate less than a FromDate parameter and the second SUM should be the sum of ShipAmt for ShipDate between the FromDate parameter and a ToDate parameter.
>
>So, in summary, I would like to list each order in the order table with columns showing total shipped before a from date parameter and total shipped in the period defined by the from date parameter and a to date parameter.
>
--sb--