Home » SQL & PL/SQL » SQL & PL/SQL » Using views to summarise payment info (Oracle SQL 12)
Using views to summarise payment info [message #666806] Sun, 26 November 2017 16:22 Go to next message
Rugad
Messages: 1
Registered: November 2017
Junior Member
Hi,

So I have a tables that contain monetary transactions against accounts, the type of transaction and the date that it takes place. Also the charges and the balances left.

I needed to summarise this up into a single view to export out to a SSRS tactical database to use in a web portal. I built a view to summarise the data and its something like

Account no - week_date - week_charge - trans_amount - trans_date - trans_type - balance

This is used to populate a transaction summary view on a user portal, this only does a simple dumb-read of the TD to give a summary view of transactions against each account by week. This works fine as for each account I get the weekly charge and then any payments, the amount and the type with the date of transaction and I've built some sql to find the week of the transaction (the Monday date of the week the transaction occurs in). Finally it shows what the balance of the account is at the end of that week - that looks something like

Account no: 12345

Week|| Charges|| Amount|| Type|| Date|| BALANCE
20th November|| £100|| £100|| DIRECT DEBIT|| 26th November|| £0
13th November|| £100|| £100|| DIRECT DEBIT|| 15th November|| £0
6th November|| £100|| £100|| DIRECT DEBIT|| 6th November|| £0


Account no: 98765

Week|| Charges|| Amount|| Type|| Date|| BALANCE
20th November|| £70|| £67.50|| CASH|| 18th November|| -£2.50
6th November|| £70|| £10|| CASH|| 8th November|| -£62.50


and so on


However the issue I have is that initially it was just customers payments onto the account I had to show, now I have been requested to include system payments on the account. This means that I now have multiple rows showing up under the week. This makes it look like

Account no: 12345

Week|| Charges|| Amount|| Type|| Date|| BALANCE
20th November|| £100|| £100|| DIRECT DEBIT|| 26th November|| -£15
20th November|| £100|| -£15|| ADJUSTMENT|| 24th November|| -£15
13th November|| £100|| £100|| DIRECT DEBIT|| 15th November|| £0
6th November|| £100|| £100|| DIRECT DEBIT|| 6th November|| £0


Totally confusing to the end-user at it looks like in the week of the 20th, they were charged £200.

I cannot change how the portal end works and the company has advised me that my best option where there are multiple rows per week is to zero out the charges on all but one of the weeks. I have written a simple count() script to find the combination of account_no and week_date where there are multiple payments...
select account_no, week_date,count(account_no) from v_rbc_myaccount_pymt_split
group by account_no, week_date

But its then how to write a statement that shows all details where the count is 1 but where the count is >1 to show all details on the first row and remove the payments details from subsequent rows.

My initial thoughts were to try and script something that adds a count number to each set of account_no/week_date rows...so a week where theres one row would just have a count of 1 and then weeks with multiple rows would count up 1, 2, 3 etc...that way I could say where rows have a count of 1 then show all and where rows have a count >1 don't show payments.

Is that feasible?

TIA

Re: Using views to summarise payment info [message #666807 is a reply to message #666806] Sun, 26 November 2017 16:44 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Previous Topic: help convert mac address string to hex
Next Topic: Procedure
Goto Forum:
  


Current Time: Fri Mar 29 07:20:50 CDT 2024