Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-10-22 : 23:43:39
|
Rahul writes "I have a problem
I have written a stored procedure which returns the following
recordset
Customer Opening Transaction Serial No Date Type Amount
id Balance Type
-----------------------------------------------------------------
101 10000(C) Receipt 101 01/01/00 C 5000
101 10000(C) Receipt 104 01/03/00 C 6000
101 10000(C) Receipt 112 01/13/00 C 8000
101 10000(C) Debit Note 92 01/15/00 D 2000
Note
1. I have used descriptive and not actual column
names to avoid confusion.
2. The above recordset is ordered on Customer ID
and within Customer ID, Date i.e. Date of transaction.
3. D or C stands for Debit / Credit
4. The above recordset is to be used to
print an account ledger.
4. The opening balance will be the same for all recordsets
for a particular customer ID as it will in turn be used in
Crystal reports as a group. (and therefore the repition will
not matter. Also this opening balance will be printed in
case the ledger is for the full financial year. Closing balance
will be determined by Crystal reports
5. Now, the problem comes when I have to print the
opening balance as on a particular date.
Let us say on 01/13/00
The recordset should appear as follows
Customer Opening Transaction Serial No Date Type Amount
id Balance Type
-----------------------------------------------------------------
101 29000(C) Receipt 112 01/13/00 C 8000
101 29000(C) Debit Note 92 01/15/00 D 2000
Please note that i have not been using SQL for very long
The point is If I use group by and aggregate functions
then how??
" |
|
|
|
|
|
Very Specific Query relating Group By