Invoice query with related customer addresses, , where zip is NYC

Morris S

·

Follow

2 min read

·

Sep 18, 2024

--

Listen

Share

SELECT DISTINCT
    trans.tranid AS "Invoice",
    trans.id AS "Invoice ID",
    cust.entityid AS "Customer Name",
    cust.id AS "Customer ID",
    cust.Email,
    item.itemid AS "Item",
    tranline.item AS "Item ID",
    tranline.rate AS "Rate",
    ROUND(tranline.quantityuom * tranline.rate, 2) AS "Amount", 
    addr.addr1,
    addr.addr2,
    addr.addr3,
    addr.city,
    addr.state,
    addr.zip
FROM
    transactionline AS tranline
LEFT JOIN
    transaction AS trans
    ON tranline.transaction = trans.id
LEFT JOIN
    customer AS cust
    ON trans.entity = cust.id
LEFT JOIN
    item
    ON tranline.item = item.id
LEFT JOIN
    customerAddressbook AS Addressbook
    ON cust.id = Addressbook.entity
LEFT JOIN
    customerAddressbookEntityAddress AS addr
    ON addr.recordOwner = Addressbook.addressBookAddress
WHERE 
    trans.type = 'CustInvc'
    AND addr.zip IN ('10001', '10002', '10003')
    AND tranline.mainline = 'F'
    AND tranline.taxline = 'F'
    AND trans.id = '<Insert internal id>'
ORDER BY
    item.itemid;

Invoice query with ship to address, where zip is NYC

SELECT
    trans.tranid AS "Invoice",
    trans.id AS "Invoice ID", 
    cust.entityid AS "Customer name",
    cust.id AS "ces customer id",
    cust.email AS "Email",
    item.itemid AS "Item",
    tranline.item AS "Item ID",
    tranline.quantity AS "Base Quantity",
    uom.abbreviation AS "Base Unit Abbr",
    uom.unitname AS "Base Unit",
    uom.baseunit AS "Is Base Unit",
    tranline.rate AS "Rate",
    ROUND(tranline.quantity * tranline.rate, 2) AS "Amount",
    addr.address1 AS "Shipping Address Line 1",
    addr.address2 AS "Shipping Address Line 2",
    addr.city AS "Shipping City",
    addr.state AS "Shipping State",
    addr.zip AS "Shipping ZIP",
    addr.country AS "Shipping Country"
FROM
    transactionline AS tranline
LEFT JOIN
    transaction AS trans
    ON tranline.transaction = trans.id
LEFT JOIN
    customer AS cust
    ON trans.entity = cust.id
LEFT JOIN
    item
    ON tranline.item = item.id
LEFT JOIN
    unitstypeuom AS uom
    ON uom.internalid = item.purchaseunit
LEFT JOIN
    unitstype
    ON item.unitstype = unitstype.id
LEFT JOIN
    transactionaddress AS transaddr
    ON trans.id = transaddr.transaction
LEFT JOIN
    address AS addr
    ON transaddr.address = addr.id
WHERE 
    trans.type = 'CustInvc'
    AND (addr.zip IN ('10001', '10002', '10003'))
    AND tranline.mainline = 'F'
    AND tranline.taxline = 'F'
    AND trans.id = '<Insert internal id>';