SQL Query Solution - Day By Day Balance

Question

Hello, Every one.

first of all, let me explain the table structure.

It is as follows.

• Main Table - CategoryGroup
• Fields - CatGroupID, GroupName, GroupType

• Sample Data

  CatGroupID | GroupName | GroupType
  1 | Utility | Expense
  2 | Auto | Expense
  3 | Misc. | Expense
  4 | Income | Income
  5 | HouseHold | Expense
  

• Child Table - TransactionCategory

• Fields - CategoryID, CatGroupID, CatName,CatImgName

• Sample Data

  CategoryID | CatGroupID | CatName | CatImgName
  1 | 1 | Water | Water.png
  2 | 1 | Phone | Phone.png
  3 | 1 | Electricity | Ele.png
  4 | 2 | Auto | Auto.png
  5 | 2 | Gas | Gas.png
  

• Child Child Table - TransactionTbl

• Fields - rowid, UserID, ProfileID, CategoryID,TraID, TraDate,TraAmt

• Sample Data

  rowid(AutoInc) | UserID | ProfileID | CategoryID | TraID | TraDate | TraAmt
  1 | 1 | 1 | 1 | 1 | 2010-1-1 | 1000.00
  2 | 1 | 1 | 1 | 2 | 2010-1-2 | 250.00
  

I have tried to many queries - and one of them is as follows,

select A.TraDate,A.TraAmt,C.GroupType,B.CatName from transactionTbl A, TransactionCategory B,CategoryGroup C where A.TraDate='2010-01-11' and A.CAtegoryID=B.CategoryID and B.CatGroupID=C.CatGroupID

I am an iPhone developer & using SQLite. I have very little ideas regarding database management & queries & joins. just because of this I have to ask here.

Oke. My Required output is as follows.

TraID | TraDate | CatName | Amount | Balance |
1. | 2010-1-1 | Income | 5000.00 | 5000.00 |
2. | 2010-1-2 | Auto | 250.00 | 4750.00 |
3. | 2010-1-3 | Gas | 200.00 | 4550.00 |
4. | 2010-1-4 | Income | 500 | 5050.00 |

Please observe each row above.

• If I have income transaction, it should add to balance &
• If I have expense transaction, it should subtract from balance.

I am trying to have this kind of output from last two days. But I am not able to get it. So, finally I asked to SO.

Thanks in your advance for your great help.

Sagar.

Edit :

I have found some solution - a little. But it's yet not as I want.

select A.traID,B.CatName,C.GroupType,A.TraDate,A.TraAmt from transactionTbl A,TransactionCategory B,CategoryGroup C 
where A.CategoryID=B.CategoryID and B.CatGroupID=C.CatGroupID
order by A.TraDate,A.TraID

It gives output as follows.

TraID CatName GroupType TraDate TraAmt
----- ------- --------- ------- ------
1 Income Income 2010-01-01 5000
2 Income Income 2010-01-02 4500
3 Water Expense 2010-01-03 450
4 Phone Expense 2010-01-03 450
5 Electricity Expense 2010-01-03 650
6 Auto Expense 2010-01-03 750
7 Auto Expense 2010-01-03 450
8 Gas Expense 2010-01-05 750
9 Misc Expense 2010-01-06 800
10 Health Expense 2010-01-13 250
11 Tax Expense 2010-01-21 450
12 Investments Expense 2010-01-22 450
13 Credit Cards Expense 2010-01-27 550
14 Eat Out Expense 2010-01-29 450

The last thing is remaining to implement that is balance managing. like here - balance should be as follows

Balance 1. 5000 2. 9500 3. 9050 and so on.

Answer 1

While this gives the right results, I'm not sure if it is the optimal way to do it in MySQL in terms of performance.

SELECT
  A.traID,
  B.CatName,
  C.GroupType,
  A.TraDate,
  A.TraAmt,
  SUM(D.TraAmt) as Balance
FROM transactionTbl A
INNER JOIN TransactionCategory B
    ON A.CategoryID=B.CategoryID
INNER JOIN CategoryGroup C
    ON B.CatGroupID=C.CatGroupID
INNER JOIN (
        SELECT
          A.TraDate,
          CASE WHEN C.GroupType = 'Income' THEN A.TraAmt ELSE -A.TraAmt END AS TraAmt
        FROM transactionTbl A
        INNER JOIN TransactionCategory B
            ON A.CategoryID=B.CategoryID
        INNER JOIN CategoryGroup C
            ON B.CatGroupID=C.CatGroupID) D
    ON A.TraDate >= D.TraDate
GROUP BY
  A.traID,
  B.CatName,
  C.GroupType,
  A.TraDate,
  A.TraAmt
ORDER BY
  A.TraDate,
  A.TraID;

Answer 2

Yes, I found the solution in my way. ( Actually my project manager told me the query to implement - when I asked to him. )

select A1.traID,B1.CatName,C1.GroupType,A1.TraDate,A1.TraAmt,            
    (select total(A.TraAmt)              
      from transactionTbl A,TransactionCategory B,CategoryGroup C 
      where A.CategoryID=B.CategoryID and B.CatGroupID=C.CatGroupID
      AND A.traID <= A1.traID AND C.GroupType = 'Income' and A.userID=1 and A.profileID=1
      order by A.TraDate,A.TraID) 
      -
      (select total(A.TraAmt) 
      from transactionTbl A,TransactionCategory B,CategoryGroup C 
      where A.CategoryID=B.CategoryID and B.CatGroupID=C.CatGroupID
      AND A.traID <= A1.traID AND C.GroupType = 'Expense' and A.userID=1 and A.profileID=1
      order by A.TraDate,A.TraID) 
     as BalanceAmt
  from transactionTbl A1,TransactionCategory B1,CategoryGroup C1 
where A1.CategoryID=B1.CategoryID and B1.CatGroupID=C1.CatGroupID 
and A1.userID=1 and A1.profileID=1
order by A1.TraDate,A1.TraID