Adam,

In SQL Server, you can base a query on the result set returned by another query, so off the top of my head for your example below you could do:

SELECT i.deliverydate, i.zip, SUM(i.oZipCount), SUM(i.bZipCount)
FROM (
SELECT o.deliverydate,o.zip,1 AS oZipCount,0 as bZipCount
  FROM [OrderData] o
  WHERE o.deliverydate >= ldDate
UNION ALL
SELECT b.billingdate, b.zip, 0 , 1
  FROM [BillingInfo] b
  WHERE b.billingdate >= ldDate 
) i
  GROUP BY i.deliverydate, i.zip

Cheers,

Andrew

>I am attempting to create a SQL query to return two COUNT() columns from two different tables, plus a 2 othe columns. I had previously posted this, but wasn't clear enough causing confusion, so here goes again...
>
>First I have 2 SQL-Server tables (OrderInfo & DeliveryInfo), let's call them oInfo & dInfo.
>
>The fields I am concerned with in each are:
>oInfo.oDate = date the order was placed.
>oInfo.oZip = zipcode order was placed from.
>dInfo.dDate = date the order was delivered.
>dInfo.dZip = zipcode order to be delivered to.
>
>I am creating a table, to be displayed on a website, that would show the total number of orders & deliveries per zipcode, per date. There would be a query obtaining all DISTINCT(zipcodes) from dInfo for a RANGE of dates. What I would then like to have, is the number of ORDERS and DELIVERIES for each zipcode on each date. The joins between the two tables would be the zipcodes and dates (WHERE oZip = dZip AND oDate = dDate). Then I could display 4 columns, grouped by DATE, with COUNT(oInfo), zipcode, COUNT(dInfo) Ordered by zipcode.
>
>Rich gave me a suggestion below, and in VFP it works, however, I'm populating a table for a webpage, and to have the SELECT's below run for each line would be very load intensive. Thus, I was looking for a query that would do it all in one server construct. If anyone has any othe ideas for a single statement, or other advice, I'd be thankful.
>
>

>SELECT o.deliverydate,o.zip,00001 AS oZipCount,00000 as bZipCount;
>  FROM [OrderData] o;
>  WHERE o.deliverydate >= ldDate;
>UNION ALL;
>SELECT b.billingdate,b.zip,00000,00001;
>  FROM [BillingInfo] b;
>  WHERE b.billingdate >= ldDate;
>INTO CURSOR curFirst
>
>SELECT deliverydate,zip,SUM(oZipCount),SUM(bZipCount);
>  FROM curFirst;
>  GROUP BY 1,2
>