• Replies 2 replies
  • Subscribers 337 subscribers
  • Views 79 views
  • Users 0 members are here
Related

T_ORDER_SCHEDULE & T_ACCOUNT_DATA

Ben Magson
$organization

Hi

How do I find out which entry from T_ACCOUNT_DATA is being referenced by T_ORDER_SCHEDULE?

I've written a confirmation report for our current direct debit subscription series, but the SQL's a little dumb, as I'm capturing the max(act_name) where act_type = 7, but if there is more than 1 row for the act_type then I can't be sure I'm getting the correct one...

:(

Ben

  • $organization

    Hi Ben,

     

    I am not sure whether you are talking about invoice billing.

    if it is about invoice billing, there is another table need to be considered. it is T_INVOICE_BILL_DETAIL.

     

    I have a stored procedure to pick up expired credit cards.

    it needs a parameter which is sheduled direct debit date.

    have fun

    :-):-):-)

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    USE

     

     

    [impresario]

    GO

    /****** Object: StoredProcedure [dbo].[LP_MTC_CREDIT_CARD_CHECK] Script Date: 05/18/2009 14:12:18 ******/

    SET

     

     

    ANSI_NULLS

    ON

    GO

    SET

     

     

    QUOTED_IDENTIFIER

    ON

    GO

    create

     

     

    procedure [dbo].

    [LP_MTC_CREDIT_CARD_CHECK]

    @duedate

     

    datetime

    as

    /*

    [LP_CREDIT_CARD_CHECK] '2009-04-08'

    */

    begin

    Set

     

     

    NoCount On

    ;

    declare

     

     

    @datekey

    int

    declare

     

     

    @duedatePlus

    datetime

    set

     

     

    @duedatePlus=@duedate+

    1

    declare

     

     

    @duedateMinus

    datetime

    set

     

     

    @duedateMinus=@duedate-

    1

    set

     

     

    @datekey=(year(isnull(@duedate,getdate()))-2000)*100+month(isnull(@duedate,getdate

    ()))

     

    SELECT

     

     

    T_ACCOUNT_DATA.id, T_ACCOUNT_DATA.customer_no, T_ACCOUNT_DATA.act_no_four, T_ACCOUNT_DATA.act_type, T_ACCOUNT_DATA.act_name,

    T_ACCOUNT_DATA

     

    .card_expiry_dt, T_ACCOUNT_DATA.inactive, TR_ACCOUNT_TYPE.description,

     

     

    CAST(RIGHT(CAST(YEAR(T_ACCOUNT_DATA.card_expiry_dt) AS varchar(10)), 2) + CASE WHEN len(CAST(month(T_ACCOUNT_DATA.card_expiry_dt)

     

     

    AS varchar(10))) < 2 THEN '0' + CAST(month(T_ACCOUNT_DATA.card_expiry_dt) AS varchar(10)) ELSE CAST(month(T_ACCOUNT_DATA.card_expiry_dt)

     

     

    AS varchar(10)) END AS int) AS expiry_Dt, CASE WHEN len(CAST(month(T_ACCOUNT_DATA.card_expiry_dt) AS varchar(10)))

     

     

    < 2 THEN '0' + CAST(month(T_ACCOUNT_DATA.card_expiry_dt) AS varchar(10)) ELSE CAST(month(T_ACCOUNT_DATA.card_expiry_dt) AS varchar(10))

     

     

    END + '/' + RIGHT(CAST(YEAR(T_ACCOUNT_DATA.card_expiry_dt) AS varchar(10)), 2) AS standard_Date, T_INVOICE_BILL_DETAIL.amount_due,

    T_INVOICE_BILL_DETAIL

     

    .current_due, T_INVOICE_BILL_DETAIL.past_due, T_INVOICE_BILL_DETAIL.bill_amt, T_ORDER_SCHEDULE.

    due_dt

    into

     

     

    #m1

    FROM

     

     

    T_ACCOUNT_DATA INNER

    JOIN

    TR_ACCOUNT_TYPE

     

    ON T_ACCOUNT_DATA.act_type = TR_ACCOUNT_TYPE.id INNER

    JOIN

    T_INVOICE_BILL_DETAIL

     

    ON T_ACCOUNT_DATA.id = T_INVOICE_BILL_DETAIL.act_id INNER

    JOIN

    T_ORDER_SCHEDULE

     

    ON T_INVOICE_BILL_DETAIL.payment_no = T_ORDER_SCHEDULE.

    order_no

    GROUP

     

     

    BY T_ACCOUNT_DATA.id, T_ACCOUNT_DATA.customer_no, T_ACCOUNT_DATA.act_no_four, T_ACCOUNT_DATA.act_type, T_ACCOUNT_DATA.act_name,

    T_ACCOUNT_DATA

     

    .card_expiry_dt, T_ACCOUNT_DATA.inactive, TR_ACCOUNT_TYPE.description,

     

     

    CAST(RIGHT(CAST(YEAR(T_ACCOUNT_DATA.card_expiry_dt) AS varchar(10)), 2) + CASE WHEN len(CAST(month(T_ACCOUNT_DATA.card_expiry_dt)

     

     

    AS varchar(10))) < 2 THEN '0' + CAST(month(T_ACCOUNT_DATA.card_expiry_dt) AS varchar(10)) ELSE CAST(month(T_ACCOUNT_DATA.card_expiry_dt)

     

     

    AS varchar(10)) END AS int), CASE WHEN len(CAST(month(T_ACCOUNT_DATA.card_expiry_dt) AS varchar(10)))

     

     

    < 2 THEN '0' + CAST(month(T_ACCOUNT_DATA.card_expiry_dt) AS varchar(10)) ELSE CAST(month(T_ACCOUNT_DATA.card_expiry_dt) AS varchar(10))

     

     

    END + '/' + RIGHT(CAST(YEAR(T_ACCOUNT_DATA.card_expiry_dt) AS varchar(10)), 2), T_INVOICE_BILL_DETAIL.amount_due,

    T_INVOICE_BILL_DETAIL

     

    .current_due, T_INVOICE_BILL_DETAIL.past_due, T_INVOICE_BILL_DETAIL.bill_amt, T_ORDER_SCHEDULE.

    due_dt

    HAVING

     

     

    (CAST(RIGHT(CAST(YEAR(T_ACCOUNT_DATA.card_expiry_dt) AS varchar(10)), 2) + CASE WHEN len(CAST(month(T_ACCOUNT_DATA.card_expiry_dt)

     

     

    AS varchar(10))) < 2 THEN '0' + CAST(month(T_ACCOUNT_DATA.card_expiry_dt) AS varchar(10)) ELSE CAST(month(T_ACCOUNT_DATA.card_expiry_dt)

     

     

    AS varchar(10)) END AS int) < @datekey) AND (T_ORDER_SCHEDULE.due_dt < @duedatePlus

    )

    ORDER

     

     

    BY T_ACCOUNT_DATA.customer_no, T_ACCOUNT_DATA.

    id

    ---need check paid order.

    SELECT

     

     

    customer_no, [description],act_name, act_no_four,card_expiry_dt, standard_Date, amount_due, current_due, past_due, bill_amt,

    due_dt

    into

     

     

    #m2

    FROM

     

     

    #m1

    WHERE

     

     

    (due_dt > @duedateMinus

    )

    SELECT

     

     

    customer_no, description, act_name, act_no_four, card_expiry_dt, standard_Date,

    due_dt

    FROM

     

     

    #m2

    GROUP

     

     

    BY customer_no, description, act_name, act_no_four, card_expiry_dt, standard_Date,

    due_dt

    drop

     

     

    table

    #m1

    drop

     

     

    table

    #m2

    End

     

     

     

     

  • $organization in reply to Ben Gu

    Hi Ben

    Thanks for the response. After some more digging, it turns out the join I was looking for was in the T_ORDER table all along - T_ORDER.act_id.

    Don't I feel silly!

    Ben