SQL code

General discussion on all other topics under the sun.

Moderator: Renato

Post Reply
rpbeesley
New MMEX User
Posts: 4
Joined: Wed Aug 01, 2018 2:19 pm
Are you a spam bot?: No

SQL code

Post by rpbeesley »

Hi. I'm new to SQL, and completely lost in trying to get what I want. Is anyone able to point me in the right direction, please?

I have a single account, which has income (donations) from a number of payees, and expenditure (grant payments) to one recipient (payee). What I would like is the similar to the built-in Payee report, but to only show payees where the TRANSCODE is Deposit.

I've managed to use a sample SQL report to get a list of the transactions, but cannot see how to exclude a specific TRANSCODE, and no idea how to get the chart (or use dates selected by the user).

SQL in use at the moment is as follows. Any help very gratefully received.


select PAYEENAME,CATEGNAME,TRANSCODE,TRANSAMOUNT
from
CHECKINGACCOUNT_V1
join
ACCOUNTLIST_V1 on CHECKINGACCOUNT_V1.ACCOUNTID=ACCOUNTLIST_V1.ACCOUNTID
join
PAYEE_V1 on PAYEE_V1.PAYEEID=CHECKINGACCOUNT_V1.PAYEEID
join
CATEGORY_V1 on CATEGORY_V1.CATEGID=CHECKINGACCOUNT_V1.CATEGID;
siowena
Super MMEX User
Posts: 109
Joined: Tue Jul 17, 2018 8:04 am
Are you a spam bot?: No

Re: SQL code

Post by siowena »

rpbeesley wrote:Hi. I'm new to SQL, and completely lost in trying to get what I want. Is anyone able to point me in the right direction, please?...
Did you get this sorted?

You need the WHERE clause.

---
select PAYEENAME,CATEGNAME,TRANSCODE,TRANSAMOUNT
from CHECKINGACCOUNT_V1
join ACCOUNTLIST_V1 on CHECKINGACCOUNT_V1.ACCOUNTID=ACCOUNTLIST_V1.ACCOUNTID
join PAYEE_V1 on PAYEE_V1.PAYEEID=CHECKINGACCOUNT_V1.PAYEEID
join CATEGORY_V1 on CATEGORY_V1.CATEGID=CHECKINGACCOUNT_V1.CATEGID
where CHECKINGACCOUNT_V1.TRANSCODE = 'Deposit';
Post Reply