1

I would like to thank in advance for any help.

My problem relates to two tables in MySQL (Now switching to postgresql). The tables are related to a ticketing database.

a) booking. It has four columns       ccode,date,time,amount
b) account  It has three columns      ccode,date,amount

The booking table has ticket bookings and account table has advances and payments received.

I have to prepare a statement of account based on ccode (customer code).

The statement shows columns as below

*Ccode    Type    Date     time    amount    balance*

- the report in sorted on ccode and then on date (account table row appears first)
- Type column displays B or A depending on record type
- Time column is present only in booking table
- Report has a running balance for each row 
- At the end for a customercode, the totals of amount and balance is displayed

I have had success, so far in creating a join as below. (and after dicussion below, have been able to generate TYPE column using IF) 

   
SELECT     booking.cname, booking.bdate, booking.btime, booking.rate, booking.ID,
           IF(booking.btime IS NOT NULL, "B", "A") AS type, account.cname, account.date,
           account.amount, account.ID 
FROM       booking 
LEFT JOIN  account ON booking.bdate = account.date AND booking.cname=account.cname AND 
           booking.rate = account.amount  
UNION 
SELECT     booking.cname, booking.bdate, booking.btime, booking.rate, booking.ID, 
           IF(booking.btime IS NOT NULL, "B", "A") AS type, account.cname, account.date,
           account.amount, account.ID  
FROM       booking 
RIGHT JOIN account ON booking.bdate = account.date AND booking.cname=account.cname AND 
           booking.rate = account.amount

It displays all the records. A report can be generated using this table.

But is there a way to display the formatted report just by SQL. I can change the order of columns and even add or remove existing ones as long as Record type is known and a running balance is displayed against each record.

A SAMPLE REPORT ---- REPORT A

CODE          DATE           TYPE              AMOUNT            BALANCE    TIME

A1           02/19/2011       A                 50               50        
A1           02/20/2011       B                 35               15          1230
A1           02/21/2011       A                 40               55
A1           02/21/2011       B                 20               35          1830


optional > TOTAL Account = 90    Booking = 55    Balance = 35

A SAMPLE REPORT ---- REPORT B

CODE          AMOUNT BOOKED             AMOUNT PAID           BALANCE

A1                      50               50                     0
A1                      35               15                     20
A1                      40               55                    -15
A1                      20               35                    -15

this is a weekly statement version of REPORT A. 
the reason is i can add where and between to get only records in a given week. 
and since it is a weekly report, running balance is just omitted. 
It is report grouped for all entries with customercode A1 present 
   in booking and account tables.

thanx
arvind
  • 1,385
  • 1
  • 13
  • 21
  • 1
    You might want to have a look at [this question](http://stackoverflow.com/questions/5032360/running-sums-for-multiple-categories-in-mysql) about running totals in MySQL. – Andriy M Aug 17 '11 at 05:30
  • yes, thanx that solves a part of query. any idea how to update TYPE column with A or B and merge two tables on basis of date. My query just places one table below the other in place of arranging records on date – arvind Aug 17 '11 at 08:27
  • 1
    I would probably store the result set of your UNION into a temporary table, then tried to apply the first solution from Andomar's answer from the linked page to it, replacing the mere `SUM(Amount)` in the subquery with something like `SUM(Amount * CASE TYPE WHEN 'A' THEN 1 ELSE -1 END)`. I'm afraid I cannot supply you with more than this vague idea at the moment. Maybe I'll return to this problem later when I'm at leisure, and if you haven't had much luck by that time, I'll try to think up of something like a query to demonstrate better what I mean. – Andriy M Aug 17 '11 at 09:34
  • i will give it a try. but type can only be a sql generated column. Type as a column must hold value A if the row is from accounts table and B if row is from Bookings table. can an If condition with column update be applied on time column like @type = IF time != null then "A" else "B" – arvind Aug 17 '11 at 10:28
  • i tried the following link [link](http://stackoverflow.com/questions/742508/sql-query-with-if-statment), but need more help to combine with the above query. – arvind Aug 17 '11 at 10:34
  • Ok, I figured out the TYPE column and have edited and changed the query above. – arvind Aug 17 '11 at 10:40
  • You have made *no* effort to reduce the question and code down to the minimum required to demonstrate the issue. This is not a "Do my job for me" site. You are unlikely to get any help until you show some effort yourself. – Bohemian Aug 17 '11 at 12:19
  • No that is not the case, as mentioned above i have improved the query to add Type column. The only problem left now is running balance. thanx – arvind Aug 19 '11 at 05:57

2 Answers2

1

Since your data is not normalized for this, you pay the price in query complexity, as shown below:

SELECT    ccode, date, time, type, amount, 
          (SELECT SUM(amount) AS balance 
          FROM numbered AS n 
          WHERE numbered.rownum <= n.rownum AND numbered.ccode = n.ccode) AS balance 
FROM      (SELECT sorted.*, @rownum := @rownum + 1 AS rownum 
          FROM      (SELECT * 
                    FROM      (SELECT ccode, date, time, 'B' AS type, amount 
                              FROM booking
                              UNION
                              SELECT ccode, date, "0000" AS time, 'A' AS type, -amount 
                              FROM account) AS unsorted
                    ORDER BY ccode, date, time, type) AS sorted) AS numbered

The idea here is that you first need to get your booking (debits) and account (credits) lined up as in the "unsorted" statement above. Then, you need to sort them by date and time as in the "sorted" statement. Next, add row numbers to the results as in the "numbered" statement. Finally, select all that data along with a sum of amounts with row number less than or equal to the current row number that matches your ccode.

In the future, please consider using a transaction table of some sort which holds all account balance changes in a single table.

hoodaticus
  • 3,772
  • 1
  • 18
  • 28
  • Tried the above statement, produces an error "Table numbered does not Exist". However the last paragraph of your answer is a gracefull solution, to use triggers and hold balance information. It will improve performance for generating weekly statements. The only problem is with the existing records. Time is present only in booking table and can be ignored. thanx – arvind Aug 20 '11 at 14:13
  • We are transfering data to Postgresql. will it make any difference to the query. – arvind Aug 20 '11 at 14:20
  • Hmm, let me look into it and get back to you by tomorrow. – hoodaticus Aug 21 '11 at 16:39
  • Really busy here - I'll try to get back to this soon. – hoodaticus Aug 22 '11 at 14:52
  • thanx, we have transfered master tables to postgres on Quadcore server with 10gb ram and 1 tb diskspace. so we are definetly working with postgres – arvind Aug 22 '11 at 15:10
  • I have modified th query, and changed the report format. Solution to report A is better since i can have a comprehensive statement. else i have to develop and print the second report for all weeks applicable on the data, which is more than 100 – arvind Aug 22 '11 at 15:23
0

I have found the answer.

It is to use cummulative sql statement to find a running balance

arvind
  • 1,385
  • 1
  • 13
  • 21