How to get the last non null value when an account is now currently closed?

Transact-SQL
1

I am working with checking accounts and I want to see what the owners last balance was before closing their account. Right now, all the accounts show 0 as their balance because they are in closed status. Does anyone know a query that can be written to connect the closed account today back to when their account was active and non-zero?

2

We will need the data definition (DDL) of the tables you're querying, plus some sample data and some expected results.

Assuming you have an Activity table, and that credits and debits are stored correctly as positive and negative numbers, a simple SUM() of the amount column for that account would probably do the trick. Again, without DDL for your tables we can't even guess.

1 Like
3

Accounts Table: ProdDate,AccountNumber,AccountStatusCode,ContractDate,ClosedDate,PersonNumber,OrganizationNumber,Balance

Organizations Table:
ProdDate,OrganizationNumber,OrganizationName

Persons Table:
ProdDate,PersonNumber,PersonFullName

These are the three tables I am using. Prod Date is the date that the data was updated on. Account number is the unique ID given to each banking customer. Balance is the amount in their account they have opened. AccountStatus Code is the status of their account I.E. (Closed,Active). PersonNumber is the unique ID for each person with an account and organization number is unique ID for every organization with an account. Contract Date is when account was opened and closed date is when account was closed.

Again, I am trying to find the last value the account had before closing and going into closed status.

4

Also, just for accounts closed in the past two years.

5

Taking a common-sense interpretation of the field names you've listed, your tables don't seem to contain any record of activity like @robert_volk referred to. So I don't see how they could give the information you want.

6

the activity would be the prod date, and the balance of the account over time.

7 

SELECT acct_last.*
FROM Accounts a
CROSS APPLY (
    SELECT TOP (1) *
    FROM Accounts a2
    WHERE a2.AccountNumber = a.AccountNumber AND a2.ProdDate < a.ProdDate
    ORDER BY a2.ProdDate DESC
) AS acct_last
WHERE a.ClosedDate IS NOT NULL
2 Likes
8

The problem with this is I don't know if the account was closed or not.

9

Nevermind I was able to use the query and then transform it inside PowerBI, thank you!

10

hi

hope this helps

create sample data

drop table if exists #Accounts
create table #Accounts(ProdDate date ,AccountNumber int ,AccountStatusCode varchar(20),
ContractDate date,ClosedDate date,PersonNumber int ,OrganizationNumber int ,Balance Int)
insert into #Accounts select '2022-12-03',8888,'Closed','2022-09-08','2022-11-23',3421,114567,5000
insert into #Accounts select '2022-12-01',8888,'Active', '2022-09-08',null,3421,114567,10000
insert into #Accounts select '2022-11-29',8888,'Active', '2022-09-08',null,3421,114567,20000

insert into #Accounts select '2021-07-05',9090,'Closed', '2020-01-01','2021-06-15',4534,378632,5000
insert into #Accounts select '2021-06-02',9090,'Active', '2020-01-01',null,4534,378632,10000
insert into #Accounts select '2021-04-28',9090,'Active', '2020-01-01',null,4534,378632,20000

drop table if exists #Organizations
create table #Organizations(ProdDate date,OrganizationNumber int,OrganizationName varchar(20))
insert into #Organizations select '2022-10-03',114567,'BP Oil'
insert into #Organizations select '2021-07-05',378632,'American Energy'

drop table if exists #Persons
create table #Persons(ProdDate date ,PersonNumber int ,PersonFullName varchar(20))
insert into #Persons select '2022-10-03',3421,'Harry Brown'
insert into #Persons select '2021-07-05',4534,'Pam Silly'

one way to do it

; with cte as 
	( select 
			ROW_NUMBER() over(partition by accountnumber order by contractdate desc ) as rn 
		  , * 
	  from 
		  #Accounts 
	  where 
		  AccountStatusCode = 'Active')
select 
     * 
from 
    cte 
where 
    rn =1

second way to do it

select 
      top 1 with ties
      *
from 
   #Accounts
where 
     AccountStatusCode = 'Active'
order by 
       row_number() over (partition by AccountNumber order by ProdDate desc)

thrid way to do it

select 
     * 
from 
    #Accounts a 
	   join 
   ( select accountnumber,max(proddate)  as maxproddate from #Accounts where AccountStatusCode = 'Active' group by AccountNumber ) b 
         on 
		   a.ProdDate = b.maxproddate

123
1231325×572 31.7 KB

456
4561277×185 10.4 KB

11

I retrieve all nulls using any of these queries.

12

not sure what you mean ? please explain

if you provide DDL and sample data then the SQL i write will be correct

i created the tables
i created dummy data
i wrote the queries

Something is not right
But can be easily fixed

13

TBH, this post scares the hell out of me. The OP is working with people's checking accounts... I hope this is for a training course and not for an actual bank.