Hi guys,
I hope someone can help me, I have very little SQL knowledge at all so I am really at your mercy.
I love the Transaction Filter but I hate having to change it every time I want to use it to produce a set of reports so I would like a set of reports that I can just run when ever I need to.
I have several different values in the Notes field that I would like to filter, 1 value per report, So easy to do with the Transaction Filter but I have to change it 15 times for each value. What I would like is a save a set of reports that have the same data as the Transaction Report but with the appropriate filter already applied. So 1 report where Notes contains ABC another report where Notes contains DEF and a third where Notes contains GHI etc.
I think that if someone could provide the SQL for 1 report I could adapt the WHERE clause (is that right?) for the others.
Thanks for the help.
Saving Filtered Transaction List as Set of Reports
Moderator: Renato
-
- New User
- Posts: 5
- Joined: Tue Nov 10, 2015 10:37 am
- Are you a spam bot?: No
-
- New User
- Posts: 5
- Joined: Tue Nov 10, 2015 10:37 am
- Are you a spam bot?: No
Re: Saving Filtered Transaction List as Set of Reports
Ok so I've got this far
Code: Select allBut I can't find the Type field which shows whether a transaction is a Deposit or a Withdrawal, I can't concatenate Category and Sub Category into a single output field and I seem to be getting Deposit records only.
Can anyone help?
Thanks
Code: Select all
Code: Select all
SELECT
ACC.TRANSDATE AS Date,
LST.ACCOUNTNAME As Account,
PAY.PAYEENAME As Payee,
CAT.CATEGNAME As Category,
SUB.SUBCATEGNAME As [Sub Category],
ACC.NOTES As Notes,
ROUND(ACC.TRANSAMOUNT,2) As Amount
FROM
((((CHECKINGACCOUNT_V1 as ACC
Left Join ACCOUNTLIST_V1 AS LST)
Left Join PAYEE_V1 As PAY)
Left Join CATEGORY_V1 As CAT)
Left Join SUBCATEGORY_V1 AS SUB)
WHERE
ACC.ACCOUNTID = LST.ACCOUNTID
And ACC.PAYEEID = PAY.PAYEEID
And ACC.CATEGID = CAT.CATEGID
And ACC.SUBCATEGID = SUB.SUBCATEGID
And ACC.NOTES LIKE '%3 Mill Lane%';
Can anyone help?
Thanks
-
- New User
- Posts: 5
- Joined: Tue Nov 10, 2015 10:37 am
- Are you a spam bot?: No
Re: Saving Filtered Transaction List as Set of Reports
I've refined my JOINS a bit and now I do get values for both Deposits and Withdrawals. But I still can't find the Type field, I can't concatenate Category and Sub Category and my withdrawal amounts are positive not negative.
Code: Select all
SELECT
ACC.TRANSDATE AS Date,
LST.ACCOUNTNAME As Account,
PAY.PAYEENAME As Payee,
CAT.CATEGNAME As Category,
SUB.SUBCATEGNAME As [Sub Category],
ACC.NOTES As Notes,
ACC.TRANSAMOUNT As Amount
FROM
((((CHECKINGACCOUNT_V1 as ACC
Left Join ACCOUNTLIST_V1 AS LST ON ACC.ACCOUNTID = LST.ACCOUNTID)
Left Join PAYEE_V1 As PAY ON ACC.PAYEEID = PAY.PAYEEID)
Left Join CATEGORY_V1 As CAT ON ACC.CATEGID = CAT.CATEGID)
Left Join SUBCATEGORY_V1 AS SUB ON ACC.SUBCATEGID = SUB.SUBCATEGID)
WHERE
ACC.NOTES LIKE '%3 Mill Lane%';
-
- New User
- Posts: 5
- Joined: Tue Nov 10, 2015 10:37 am
- Are you a spam bot?: No
Re: Saving Filtered Transaction List as Set of Reports
OK, I think I'm almost there but I have a problem with the formatting, The Amount field won't display to 2 decimal places when a value does not have any pennies. i.e. £800.00 shows as £800.0 if I use the ROUND function and only as £800 if I don't.
I think that's as far as I can go, please help.
Code: Select all
SELECT
ACC.TRANSDATE AS Date,
LST.ACCOUNTNAME As Account,
PAY.PAYEENAME As Payee,
CAT.CATEGNAME || Case IfNull (ACC.SUBCATEGID, -1) When -1 Then '' Else ':' || SUB.SUBCATEGNAME End AS Category,
ACC.TRANSCODE AS Type,
ACC.NOTES As Notes,
CUR.PFX_SYMBOL || (Case ACC.TRANSCODE When 'Deposit' then 1 else -1 end * ROUND(ACC.TRANSAMOUNT,2)) As Amount
FROM
ACCOUNTLIST_V1 As LST
Left Join CHECKINGACCOUNT_V1 as ACC ON ACC.ACCOUNTID = LST.ACCOUNTID
Left Join PAYEE_V1 As PAY ON ACC.PAYEEID = PAY.PAYEEID
Left Join CATEGORY_V1 As CAT ON ACC.CATEGID = CAT.CATEGID
Left Join SUBCATEGORY_V1 As SUB ON ACC.SUBCATEGID = SUB.SUBCATEGID
Inner Join CURRENCYFORMATS_V1 AS CUR ON LST.CURRENCYID = CUR.CURRENCYID
WHERE
ACC.NOTES LIKE '%3 Mill Lane%';
-
- Developer
- Posts: 1535
- Joined: Sat Dec 06, 2008 2:27 pm
- Are you a spam bot?: No
- Location: Sankt-Petersburg, Russia
Re: Saving Filtered Transaction List as Set of Reports
Code: Select all
SELECT
ACC.TRANSDATE AS Date,
LST.ACCOUNTNAME As Account,
PAY.PAYEENAME As Payee,
case ifnull(SUB.SUBCATEGID, -1) when -1 then CAT.CATEGNAME else CAT.CATEGNAME||':'||SUB.SUBCATEGNAME end As Category,
ACC.NOTES As Notes,
case acc.TRANSCODE when 'Deposit' then ACC.TRANSAMOUNT else -ACC.TRANSAMOUNT end As Amount
FROM
CHECKINGACCOUNT_V1 as ACC
inner Join ACCOUNTLIST_V1 AS LST ON ACC.ACCOUNTID = LST.ACCOUNTID
inner Join PAYEE_V1 As PAY ON ACC.PAYEEID = PAY.PAYEEID
Left Join CATEGORY_V1 As CAT ON ACC.CATEGID = CAT.CATEGID
Left Join SUBCATEGORY_V1 AS SUB ON ACC.SUBCATEGID = SUB.SUBCATEGID
WHERE
ACC.TRANSCODE !='Transfer'
--and ACC.NOTES LIKE '%3 Mill Lane%';