Hi Everyone,

In the table 'Account', it store when customer inject amount into his account.
In the table 'Investment', it store the transaction when the agent buy/sell bonds for the customer. The amount is always of 100 flat value. Now, the customer request a monthly statement of account to be sent to him. He wants to know how much per amount injected in the account will last before he make the next deposit into the account.

In the query, it'll need to something like this:
1. For a.id=01, select those top N1 records in investment where sum(i.amount) <= 300.
   In this case, the records selected will be from i.id=11 to 15
2. For a.id=02, select those top N2 (excluding those N1) in investment where sum(i.amount) <= 200
   In this case, the records selected will be from i.id=16 to 17.

This query sounds complicated. I hope some expects will shed me some lights on this. Thanks in advance.

Attached below are the DDL and sample data.


Account
*******
id date        amount 
-- ----------  ------
01 2005-01-01  300
02 2005-01-15  200

Investment
**********
id date         amount  type
-- ----------   ------  ----
11 2005-01-01   -100    buy  >>
12 2005-01-01   -100    buy  >>
13 2005-01-04   -100    buy  >>
14 2005-01-10    100    sell >>
15 2005-01-16   -100    buy  >>
16 2005-01-17   -100    buy
17 2005-01-18   -100    buy

Statement
*********
A.id A.date     A.amount I.id I.date     I.amount Balance(A.amount + I.amount)
---- ---------- -------- ---- ---------- -------- ----------------------------
01   2005-01-01 300      11   2005-01-01 -100    (300-100) = 200
                         12   2005-01-01 -100    (200-100) = 100
                         13   2005-01-04 -100    (100-100) = 0
                         14   2005-01-10  100    (  0+100) = 100
                         15   2005-01-16 -100    (100-100) = 0
02   2005-01-15 200      16   2005-01-17 -100    (200-100) = 100
                         17   2005-01-18 -100    (100-100) = 0

Hi

There may be better ways but this may help:

I have added an Investor column. to help.

CREATE TABLE Account ( [id] int, Investor int, [date] datetime, amount int )

CREATE TABLE Investment ( [id] int, Investor int, [date] datetime, amount int, type char(4) )

INSERT INTO Account ( [id], Investor , [date], amount )
SELECT 01, 1, '2005-01-01',  300
UNION ALL SELECT 02, 1, '2005-01-15',  200

INSERT INTO Investment ( [id], Investor, [date], amount, type )
SELECT 11, 1, '2005-01-01',   -100,    'buy'
UNION ALL SELECT 12, 1, '2005-01-01',   -100,    'buy'
UNION ALL SELECT 13, 1, '2005-01-04',   -100,    'buy'
UNION ALL SELECT 14, 1, '2005-01-10',    100,    'sell'
UNION ALL SELECT 15, 1, '2005-01-16',   -100,    'buy'
UNION ALL SELECT 16, 1, '2005-01-17',   -100,    'buy'
UNION ALL SELECT 17, 1, '2005-01-18',   -100,    'buy'

SELECT A.[id], CONVERT(CHAR(11),A.[date],120) AS [A_Date], A.amount,
I.[id], CONVERT(CHAR(11),I.[date],120) AS [I.Date], I.amount,
(SELECT SUM(C.Amount) FROM Account C WHERE C.Investor = A.Investor AND C.[Date] <= A.[date] ) -
(SELECT SUM(-J.Amount) FROM Investment J WHERE J.Investor = I.Investor AND J.[ID] <= I.[ID] ) AS Balance
FROM Account A
JOIN Investment I ON A.Investor = I.Investor
WHERE A.[date] = ( SELECT MAX(B.[date]) FROM Account B WHERE B.[Date] <= I.[date])
ORDER BY A.[date],I.date

John
  Hi Everyone,

  In the table 'Account', it store when customer inject amount into his account.
  In the table 'Investment', it store the transaction when the agent buy/sell bonds for the customer. The amount is always of 100 flat value. Now, the customer request a monthly statement of account to be sent to him. He wants to know how much per amount injected in the account will last before he make the next deposit into the account.

  In the query, it'll need to something like this:
  1. For a.id=01, select those top N1 records in investment where sum(i.amount) <= 300.
     In this case, the records selected will be from i.id=11 to 15
  2. For a.id=02, select those top N2 (excluding those N1) in investment where sum(i.amount) <= 200
     In this case, the records selected will be from i.id=16 to 17.

  This query sounds complicated. I hope some expects will shed me some lights on this. Thanks in advance.

  Attached below are the DDL and sample data.


  Account
  *******
  id date        amount 
  -- ----------  ------
  01 2005-01-01  300
  02 2005-01-15  200

  Investment
  **********
  id date         amount  type
  -- ----------   ------  ----
  11 2005-01-01   -100    buy  >>
  12 2005-01-01   -100    buy  >>
  13 2005-01-04   -100    buy  >>
  14 2005-01-10    100    sell >>
  15 2005-01-16   -100    buy  >>
  16 2005-01-17   -100    buy
  17 2005-01-18   -100    buy

  Statement
  *********
  A.id A.date     A.amount I.id I.date     I.amount Balance(A.amount + I.amount)
  ---- ---------- -------- ---- ---------- -------- ----------------------------
  01   2005-01-01 300      11   2005-01-01 -100    (300-100) = 200
                           12   2005-01-01 -100    (200-100) = 100
                           13   2005-01-04 -100    (100-100) = 0
                           14   2005-01-10  100    (  0+100) = 100
                           15   2005-01-16 -100    (100-100) = 0
  02   2005-01-15 200      16   2005-01-17 -100    (200-100) = 100
                           17   2005-01-18 -100    (100-100) = 0

Retrieving data from multiple tables
add column
ntext and update/insert triggers
byte manipulation for int
Need help with SELECT statement please.