Match Prior Billing Period Closing Balance to New Billing Period O

[Dave M]

As described above. I would like a query to test if the balance carries
forward correctly.

I have the following fields to match on:

Account Number
Period Open Date
Period End Date
Opening Balance
Closing Balance

If you need anything else, please let me know.

Thanks

Dave

 

[Dave M]

Few other things:

Each Open Date is one day greater then the End Date.

Each Period has multiple iterations of it, ie version 1, 2, 3, etc. I need
the max version number.

There are appx. 100,000 records, with 10,000 accounts, each with about 3
periods. I would like to run the continuity from year to year, so I was
thinking of making a query to identify issues.

 

[Tim Green]

This query will pick out all opening balances that do not match the closing
balance of the prior period:

SELECT A.[Account Number], A.[Period Open Date], A.[Period End Date],
A.[Opening Balance], A.[Closing Balance]
FROM yourTable AS B INNER JOIN yourTable AS A ON B.[Account
Number]=A.[Account Number]
WHERE A.[Period Open Date]=B.[Period End Date]+1 And A.[Opening
Balance]<>B.[Closing Balance];

Is this what you're after?

Tim

 

[Dave M]

I think so, thanks for your help!

This query will pick out all opening balances that do not match the closing
balance of the prior period:

SELECT A.[Account Number], A.[Period Open Date], A.[Period End Date],
A.[Opening Balance], A.[Closing Balance]
FROM yourTable AS B INNER JOIN yourTable AS A ON B.[Account
Number]=A.[Account Number]
WHERE A.[Period Open Date]=B.[Period End Date]+1 And A.[Opening
Balance]<>B.[Closing Balance];

Is this what you're after?

Tim

Few other things:

Each Open Date is one day greater then the End Date.

Each Period has multiple iterations of it, ie version 1, 2, 3, etc. I need
the max version number.

There are appx. 100,000 records, with 10,000 accounts, each with about 3
periods. I would like to run the continuity from year to year, so I was
thinking of making a query to identify issues.