|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Transaction amounts by month and account number |
| Author |
Message |
| Sami |
| |
| Group | : Members |
| Posts | : 6 |
| Joined | : 2008-11-13 |
|
| Profile |
|
| Posted : 2008-12-03 00:10:59 |
Hello,
I'm currently writing a query in order to have the sum of all transactions by account number and by month.
select Account.AccounNumber, {fn YEAR(JournalEntryLine.TxnDate)}, {fn MONTH(JournalEntryLine.TxnDate)}, sum(JournalEntryLine.JournalLineAmount) from Account, JournalEntryLine where Account.ListId = JournalEntryLine.JournalLineAccountRefListId and {fn YEAR(JournalEntryLine.TxnDate)} = {fn YEAR({fn CURDATE()})} group by Account.AccounNumber, {fn YEAR(JournalEntryLine.TxnDate)}, {fn MONTH(JournalEntryLine.TxnDate)}
Unfortunatly, the 'group by 'does not support functions like in my example. To avoid bringing in all transactions lines into my local database and then doing the group by locally, how can we do this. Is there any existing stored procedure that have the same output?
Thanks for your help
Sami |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-12-03 16:40:33 |
Try from this direction instead:
sp_report CustomTxnDetail show TxnType, Date, RefNumber, Name, Memo,
Account, ClearedStatus, SplitAccount, Debit, Credit
parameters DateMacro = 'ThisYearToDate'
SummarizeRowsBy = 'Account',
OpenBalanceAsOf = 'ReportEndDate'
or:
sp_report CustomTxnDetail show TxnType, Date, RefNumber, Name, Memo,
Account, ClearedStatus, SplitAccount, Debit, Credit
parameters DateFrom = {d'2008-11-01'}, DateTo= {d'2008-11-30'}
SummarizeRowsBy = 'Account',
OpenBalanceAsOf = 'ReportEndDate'
for a date range. |
|
|
|
|