April 20, 2012 at 4:24 am
Good Day All
First of all am new here not really familiar with IFCode Shortcuts but will try my best to make it neat
Im facing difficulties on how to obtain this objective done.
First of all I have 2 tables namely:
Accounts -- contains profiles, one account per user
History -- contains multiple records per account
Secondly the tools im using
Microsoft SQL server management studio 2005
Microsoft visual studio - Business intelligence project for sql projects
Thirdly the Objective
1. I would like to return the sum of all transactions per account number per month (Income)
2. I would also like to return the AVG of all transactions per account number per month (Income Average)
3. I would like to return the highest cash deposited in excess of the average per account only (excess deposit)
Take note that here the flag should only be raised once there is a record in excess to the AVG and that transaction should appear in the result but not other transactions.
Fourth, I have managed to get the sum avg and excess cash deposited for a single account.
Here is the code that returns a single account, please take note I filtered by account which is what i want to eliminate in order to do it for all accounts.
Tran code 119 is all the cash deposits
tran code 101,117,103,100 is your other incomes
select a.amt 'Excess Deposit', a.description,Name_Customer=b.title_1, b.rim_no 'RSM', a.acct_no as 'Account No', b.acct_type as 'Type', b.branch_no,
a.tran_code, (select sum(a.amt)
from history as a
where a.acct_no IN ('8000611719')
and a.effective_dt between '20090101' and '20090130'
and a.tran_code in (101,117,103,100)) 'Total Income',
(select avg(a.amt)
from history as a
where a.acct_no = '8000611719'
and a.effective_dt between '20090101' and '20090130'
and a.tran_code in (101,117,103,100)) 'Average Income'
from history as a, account as b
where a.acct_no = b.acct_no
and a.acct_type = b.acct_type
and a.acct_no = '8000611719'
and a.tran_code in (119)
and a.effective_dt between '20090101' and '20090130'
and a.amt > (select avg(a.amt)
from history as a
where a.acct_no = '8000611719'
and a.effective_dt between '20090101' and '20090130'
and a.tran_code in (101,117,103,100))
Excess Deposit,description,Name_Customer, RSM, Account No, Type ,branch_no, tran_code, Total Income, Average Income
44157.857142 ,CASH DEP, Academy , 695 ,8000611719, CHK, 70, 119, 338210.000000, 24157.857142
The next query returns all transactions for that one account including the cash deposit wich is tran code 119
select a.amt , a.description,Name_Customer=b.title_1, b.rim_no 'RSM', a.acct_no as 'Account No', b.acct_type as 'Type', b.branch_no,
a.tran_code
from history as a, account as b
where a.acct_no = b.acct_no
and a.acct_type = b.acct_type
and a.acct_no = '8000611719'
and a.tran_code in (119,101,117,103,100)
and a.effective_dt between '20090101' and '20090130'
the next two queries retrieve the sum of a multiple accounts per account and avg, please take note that this is what i want to achieve in the first query i provided above.
select a.acct_no, sum(a.amt) 'Income'
from history as a, acct as b
where a.acct_no = b.acct_no
and a.acct_type = b.acct_type
and a.tran_code in (101,117,103,100)
and a.effective_dt between '20090101' and '20090103'
and a.acct_no in ('8000606895','8000617870','8000624128','8000628343','8000634086','8000638428','8000641247','8000641277')
group by(a.acct_no)
select a.acct_no, avg(a.amt) 'Average Income'
from history as a, account as b
where a.acct_no = b.acct_no
and a.acct_type = b.acct_type
and a.tran_code in (101,117,103,100)
and a.effective_dt between '20090101' and '20090103'
and a.acct_no in ('8000606895','8000617870','8000624128','8000628343','8000634086','8000638428','8000641247','8000641277')
group by(a.acct_no)
acct_no, Average Income
8000606895, 220087.000000
8000617870 , 4500.000000
8000624128 ,1156.425000
8000628343 ,7150.000000
8000634086 ,99753.660000
8000638428 ,2000.000000
8000641247 ,665.000000
8000641277 ,1140.000000
After saying all that, i would like to write a report that will identify all transactions made in excess to the average for every account.
I cant seem to do it on a multiple of account which is what i want to get, not a single account.
How do i get the sum, avg per account in the history table?
In addition i could attach the result pictures but don't know how to.
Thanks you for your help.
April 20, 2012 at 4:52 am
This was removed by the editor as SPAM
April 20, 2012 at 5:06 am
Thanks Stewart "Arturius" Campbell
My brain is frozen at the moment and cant think any further but will have a look at the code and see if does guide, nevertheless thanks for the prompt help much appreciated.
I don't see how you are populating the temp tables just created.
One last thing, is my explanation clear and the objective defined well?
Regards,
April 20, 2012 at 5:10 am
This was removed by the editor as SPAM
April 20, 2012 at 5:16 am
Ok im busy modifying it to accomandate the actual tables, i see you have missed the filter in terms of tran codes but i will add it and get back at you..
You are almost a star but not quite, 😛 , 😉
There is a problem with the AVG function, either its not computing the data well or something is weird.
Herewith a few test I did on a few accounts. Please pay attention to the sum and the average
if you look closely you can see that some sums are computed for avg, not so sure why as its only the 1st row and the third row that the avg is computed, or is it that cause its the only transaction in the table? this puzzles me
SELECT acct_no, SUM(amt) AS SumTranAmt
FROM dp_history_BG
where effective_dt between '20090101' and '20090103'
and tran_code in (101,117,103,100)
and acct_no in ('8000606895','8000617870','8000624128','8000628343','8000634086','8000638428','8000641247','8000641277')
group by acct_no
SELECT acct_no, AVG(amt) AS AvgTranAmt
FROM dp_history_BG
WHERE effective_dt BETWEEN '20090101' AND '20090103'
and tran_code in (101,117,103,100)
and acct_no in ('8000606895','8000617870','8000624128','8000628343','8000634086','8000638428','8000641247','8000641277')
GROUP BY acct_no
acct_noSumTranAmt
8000606895 440174.000000
8000617870 4500.000000
8000624128 2312.850000
8000628343 7150.000000
8000634086 99753.660000
8000638428 2000.000000
8000641247 665.000000
8000641277 1140.000000
acct_noAvgTranAmt
8000606895 220087.000000
8000617870 4500.000000
8000624128 1156.425000
8000628343 7150.000000
8000634086 99753.660000
8000638428 2000.000000
8000641247 665.000000
8000641277 1140.000000
April 20, 2012 at 6:21 am
Wow Wow,
I have made a few changes and added few things to your code and the results are amazing, cant ask for better than this.
Thank you so much and wish i could pay you.. 🙂
Here is final code in its test environment. And all i have to do is activate the date variable in the report and im good to go.
--create table #Accounts (AccountNumber int, AccountName VARCHAR(75));
--create table #History (AccountNumber int, TransactionAmount money, TransactionDate DATE);
--DECLARE @StartDate DATETIME, @EndDate DATETIME;
WITH SumOfTran AS
(SELECT acct_no, SUM(amt) AS SumTranAmt
FROM history
WHERE effective_dt BETWEEN '20090101' AND '20090130'
and tran_code in (101,117,103,100)
GROUP BY acct_no),
AvgOfTran AS
(SELECT acct_no, AVG(amt) AS AvgTranAmt
FROM history
WHERE effective_dt BETWEEN '20090101' AND '20090130'
and tran_code in (101,117,103,100)
GROUP BY acct_no),
ExcessDeposit AS
(SELECT dp_history_BG.acct_no, MAX(amt) AS ExcessDeposit
FROM history
INNER JOIN AvgOfTran ON AvgOfTran.acct_no = history.acct_no
WHERE effective_dt BETWEEN '20090101' AND '20090130'
and tran_code in (119)
GROUP BY history.acct_no, history.amt,AvgOfTran.AvgTranAmt
HAVING amt > AvgTranAmt)
SELECT acct.acct_no, --acct.title_1,
SumOfTran.SumTranAmt,
AvgOfTran.AvgTranAmt,
ExcessDeposit.ExcessDeposit
FROM acct
INNER JOIN SumOfTran ON SumOfTran.acct_no = acct.acct_no
INNER JOIN AvgOfTran ON AvgOfTran.acct_no = acct.acct_no
LEFT OUTER JOIN ExcessDeposit ON ExcessDeposit.acct_no = acct.acct_no
where ExcessDeposit is not NULL
and SumTranAmt > 0
and AvgTranAmt > 0
order by dp_acct_bg.title_1 asc
acct_no------------SumTranAmt--------AvgTranAmt--------ExcessDeposit
8000619727--------14.130000----------14.130000---------2100.000000
8000633648--------13068.000000-------3267.000000-------33385.090000
8000633648--------13068.000000-------3267.000000-------37801.620000
8000657521--------3168.050000--------1056.016666-------5788.640000
8000611719--------338210.0000--------24157.857142------44157.857142
2000133335--------100.000000---------100.000000--------400.000000
8000655330--------55.000000----------55.000000---------3000.000000
8000655194--------255.000000---------127.500000--------1100.000000
8000642392--------10.190000----------5.095000----------22.000000
8000642921--------66.000000----------22.000000---------180.000000
8000609368--------185.750000---------185.750000--------500.000000
8000609368--------185.750000---------185.750000--------1500.000000
8000625910--------5102.880000--------2551.440000-------3000.000000
8000631938--------10924.800000-------5462.400000-------17000.000000
8000649383--------18360.450000-------3672.090000-------4000.000000
8000649383--------18360.450000-------3672.090000-------6000.000000
8000625195--------29504.000000-------3688.000000-------3800.000000
8000625195--------29504.000000-------3688.000000-------4200.000000
8000654411--------18252.170000-------2607.452857-------31000.000000
2000133227--------100.000000---------100.000000--------300.000000
8000614261--------295.000000---------295.000000--------3000.000000
April 20, 2012 at 6:32 am
This was removed by the editor as SPAM
April 20, 2012 at 6:43 am
Stewart "Arturius" Campbell (4/20/2012)
shani19831 (4/20/2012)
if you look closely you can see that some sums are computed for avg,...Yes, this would be a case of a single transaction meeting the given predicates.
You are a star... thank you bro.. god bless you
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply