• Replies 3 replies
  • Subscribers 962 subscribers
  • Views 256 views
  • Users 0 members are here
Related

Extraction Tables

Jonathan Thomas
$organization

Hello,

I'm having trouble finding any documentation on where data regarding extractions live in the database. I'm trying to create a sql script that pulls all customers in a list and what segment name they belong to in a specific extraction. The Extraction Count Report contains the names that I need. The report pulls from T_KEYCODE and T_KA_Header, but these tables contain already aggregated data. What table is it aggregating this data from? Any clues would be helpful. Thanks!

  • $organization

    Hi Jonathan,

    Does this snippet do what you're looking for?

    declare @ka_no int
    declare @list_no int

    set @ka_no = 229 --<put your Extraction ID here>
    set @list_no = 4720 --<put your List no here>

    select
    eh.description extraction_desc,
    k.select_priority ,
    k.description segment_desc,
    ck.customer_no,
    cdn.display_name full_name,
    cdn.sort_name,
    k.suppression_flag
    from impresario_cci.dbo.T_KA_HEADER eh
    join impresario_cci.dbo.t_keycode k
    on k.ka_no=eh.ka_no
    join impresario_cci.dbo.T_CUST_KEYCODE ck
    on ck.ka_no=eh.ka_no
    and ck.keycode=k.keycode
    join dbo.FT_CONSTITUENT_DISPLAY_NAME() cdn
    on cdn.customer_no=ck.customer_no
    and eh.ka_no=@ka_no
    and ck.customer_no in (select customer_no from t_list_contents where list_no = @list_no)

    Martin

  • $organization in reply to Martin Keen (Past Member)

    Thanks for your help, Martin. Been slammed and just getting back to this project. I don't have FT_CONSTITUENT_DISPLAY_NAME in our install. We host our own install if that makes a difference. What fields are in that table? Maybe I can find an equivalent 

  • $organization in reply to Jonathan Thomas

    Hi Jonathan,

    I'd always assumed (given it's naming convention) that this was a Tessitura supplied function.  However, here's a script to create it...

    CREATE FUNCTION [dbo].[FT_CONSTITUENT_DISPLAY_NAME]()
    RETURNS TABLE
    AS
    RETURN
    ( Select c.customer_no,
    display_name = Case When Coalesce(c.fname,'') <> ''
    Then Coalesce(p.description + ' ', '')
    + c.fname + ' '
    + Case When len(c.mname) > 0 Then c.mname + ' ' Else '' End
    + c.lname
    + Case When s.id > 0 Then ', ' + s.description Else '' End
    Else c.lname End,
    display_name_short = Case When Coalesce(c.fname,'') <> ''
    Then c.fname + ' '
    + c.lname
    + Case When s.id > 0 Then ', ' + s.description Else '' End
    Else c.lname End,
    display_name_tiny = Case When Coalesce(c.fname,'') <> ''
    Then substring(c.fname, 1, 1) + substring(c.lname, 1, 7) Else substring(c.lname, 1, 8) End,
    c.sort_name,
    c.cust_type
    From dbo.T_CUSTOMER c
    LEFT JOIN dbo.TR_PREFIX p WITH (NOLOCK) on c.prefix = p.id and p.id > 0
    LEFT JOIN dbo.TR_SUFFIX s WITH (NOLOCK) on c.suffix = s.id and s.id > 0
    )

    GO

    Try it and see how you go!

    Martin