• Replies 13 replies
  • Subscribers 960 subscribers
  • Views 384 views
  • Users 0 members are here
Related

Output/SQL: Constituent Info, Order Details to include Initiator

Charles Vann
$organization

Hello all,

We have a need to pull a report/query that includes the following data:

  • Constituent contact info
    • First Name
    • Last Name
    • City
    • State
    • Postal code
    • Email
    • Phone
  • Order details
    • Order date
    • Order #
    • Initiator display name
    • Delivery method
    • Sales channel
    • Performances
      • Perf date
      • Perf name
    • Price type
    • Tkt count
    • Order $

I've tried lists an output sets but not all fields are available. Would this better as a SQL query? If so, what are the primary tables that contain this data? I've looked at the "T_" tables, via SSMS, and view tables. But, I think the need for the "initiator" has me hung up.

Any suggestions?

Thanks,

Charles

Parents
  • $organization

    Here's some help to get you started. You'll still need to add in the contact perf tables you'd like include. Make sure to define which criteria you need in the WHERE clause too.

    SELECT DISTINCT o.order_dt, o.order_no, o.initiator_no, ini_name.display_name AS initiator_name, o.customer_no, own_name.display_name AS order_owner_name, o.delivery,
    d.description AS delivery_method_desc, o.channel, sc.description AS channel_desc
    FROM T_ORDER AS o
    JOIN FT_CONSTITUENT_DISPLAY_NAME() AS own_name ON own_name.customer_no = o.customer_no
    JOIN TR_SALES_CHANNEL AS sc ON o.channel = sc.id
    JOIN TR_SHIP_METHOD AS d ON o.delivery = d.id
    LEFT JOIN FT_CONSTITUENT_DISPLAY_NAME() AS ini_name ON o.initiator_no = ini_name.customer_no

Reply
  • $organization

    Here's some help to get you started. You'll still need to add in the contact perf tables you'd like include. Make sure to define which criteria you need in the WHERE clause too.

    SELECT DISTINCT o.order_dt, o.order_no, o.initiator_no, ini_name.display_name AS initiator_name, o.customer_no, own_name.display_name AS order_owner_name, o.delivery,
    d.description AS delivery_method_desc, o.channel, sc.description AS channel_desc
    FROM T_ORDER AS o
    JOIN FT_CONSTITUENT_DISPLAY_NAME() AS own_name ON own_name.customer_no = o.customer_no
    JOIN TR_SALES_CHANNEL AS sc ON o.channel = sc.id
    JOIN TR_SHIP_METHOD AS d ON o.delivery = d.id
    LEFT JOIN FT_CONSTITUENT_DISPLAY_NAME() AS ini_name ON o.initiator_no = ini_name.customer_no

Children