[thelist] QUICK SQL Sub Query
Rob Smith
rob.smith at lexjet.com
Wed May 17 08:13:34 CDT 2006
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
More information about the thelist
mailing list