• Replies 1 reply
  • Subscribers 337 subscribers
  • Views 14 views
  • Users 0 members are here
Related

Finding historical package sales data

Siobhan Parker (Past Member)
$organization

Hi

I need to produce a list of packages that had already been paid for this time last year. The problem I am encountering is that we use the rollover system so the majority of package orders had already been created in March 08, but not all paid for. This means I can't just use the create_dt as I'd like, and I can't find a way to join the payment table to the order table apart from on the transaction number - and I believe the transaction number changes when an order is reloaded?

Any help would be great as it's too hot to think.

Thanks

Siobhan

  • $organization

    Hi Siobhan

    Have you tried looking at T_TRANSACTION, and in particular the trn_dt? For example...

    select  * from t_order a
        join t_transaction b
            on a.order_no = b.order_no
        where a.order_no = 123
            and b.perf_no > 0

    Just bear in mind there may be multiple rows for each order, depending on how many payment have been taken, so you'll need some group by clauses. Also, make sure you get your trn_types correct too - these reference the id column in TR_TRANSACTION_TYPE.

    That might give you a better picture!

    Thanks, Ben