Results 1 to 2 of 2

Thread: query problem

  1. #1
    Join Date
    Aug 2001
    Posts
    16

    Unanswered: query problem

    Hi i just dont know how to start on this,
    the problem is that i want to generate a result on an accounting format for a set of reciepts and payments:
    The scenario is as attached below:
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    something like

    select a.account_id ,
    date = a.date,
    "Invoice/CollectionId" = a.id ,
    Debit = case when type = 1 then right(space(20) + convert(varchar(20),a.amount),20) else '' end ,
    Credit = case when type = 2 then right(space(20) + convert(varchar(20),a.amount),20) else '' end
    from
    (
    select id = i.invoice_id, i.amount, date = i.invoice_date, i.order_id, o.account_id from invoices i, order o where o.order_id = i.order_id, type = 1
    union
    select id = c.collection_id, c.amount, c.collection_date, null, c.account_id, 2 from collections c
    ) as amount a
    order by a.account_id , a.date desc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •