[thelist] QUICK SQL Sub Query

[Rob Smith]

Pardon my ignorance, but I'd like to do a subquery on the fly to count
up the number of different shipping methods in a particular order. (Win
2k, SQL 2005). So here's the query:

SELECT lexjet_purchase_receipt.purchase_num,
lexjet_purchase_receipt.ship_to_name,
lexjet_purchase_receipt.ship_to_state,
lexjet_purchase_receipt_lineitem.vendor_sku,
lexjet_purchase_receipt_lineitem.list_price,
lexjet_purchase_receipt_lineitem.quantity,
lexjet_purchase_receipt.date_created,
lexjet_purchase_receipt_lineitem.shipping_method,
lexjet_purchase_receipt_lineitem.ship_via,
lexjet_purchase_receipt.ship_to_zip,
lexjet_purchase_receipt.ship_to_street,
lexjet_purchase_receipt.ship_to_street2,
lexjet_purchase_receipt.ship_to_street3,
lexjet_purchase_receipt.ship_to_city FROM lexjet_purchase_receipt INNER
JOIN lexjet_vendor ON lexjet_purchase_receipt.shopper_id =
lexjet_vendor.shopper_id INNER JOIN lexjet_purchase_receipt_lineitem ON
lexjet_purchase_receipt.purchase_id =
lexjet_purchase_receipt_lineitem.purchase_id WHERE
(lexjet_vendor.shopper_id = 'SE99DEMQGES92LP60Q0781AF7RNEF62C') AND
(lexjet_purchase_receipt.date_created BETWEEN '4/12/2006' and
'4/15/2006')  ORDER BY lexjet_purchase_receipt.purchase_num DESC

...which gets all orders from a particular vendor between a short time
period. 

Here's a sample of the data, after some calculations, of what I'm trying
to accomplish:

HR0207680000000000020207687821012      80525               
HI020768000000000003PO-78210                               
A1020768000000000004Company Name Here                     
A2020768000000000005Address 1                         
A3020768000000000006Address 2                                 
A4020768000000000007                                        
A5020768000000000008City                          CO       
A602076800000000000987654                                   
CI020768000000000010                                        
DT000014321  0000011HEWC6787A           00001EATM           UPS_Ground
DT000021482  0000012HEWC6789A           00001EATM
FedEx_Standard_Overnight
HR0207680000000000020207687820512      89523               
HI020768000000000003PO-78205                               
A1020768000000000004Another Company                   
A2020768000000000005123 Anywhere St.                            
A3020768000000000006                                        
A4020768000000000007                                        
A5020768000000000008Reno                           NV       
A602076800000000000989523                                   
CI020768000000000010                                        
DT000019800  0000011HEWC4941A for AB     00001EATM           UPS_Ground
DT000019800  0000012HEWC4943A for AB     00001EATM           UPS_Ground
DT000019800  0000013HEWC4945A for AB     00001EATM           UPS_Ground

The HR row starts a new order. In my particular business scenario, if
the order comprises of two or more different types of shipping methods
(air and ground), then break up the order into two or more different
orders with different transaction id. That'd be cool if I can calculate
that count in the above query without hitting the database twice for
every order. There could be several hundred orders per day, soon to be
in the thousands. Can you offer some advice?


Rob Smith
LexJet
rob.smith at lexjet.com
http://www.lexjet.com
(800)453-9538
(941)330-1210 Int'l
(941)330-1220 Fax
1680 Fruitville Road, 3rd Floor
Sarasota, FL 34236