Getting a Minimum Value and the Date(s) on which it Occurred

Question

I have 3 columns

DATE|END OF DAY BALANCE|ACCOUNT

I want to get the minimum balance where it is less than zero and the date or dates on which this happened.

The below query instead brings back all dates:

SELECT DISTINCT
       END_OF_DAY_BALANCE.Date,
       MIN(END_OF_DAY_BALANCE.[END OF DAY BALANCE]) AS [MinOfEND OF DAY BALANCE],
       END_OF_DAY_BALANCE.[ACCOUNT]
FROM END_OF_DAY_BALANCE
GROUP BY END_OF_DAY_BALANCE.Date,
         END_OF_DAY_BALANCE.[ACCOUNT]
HAVING(((MIN(END_OF_DAY_BALANCE.[END OF DAY BALANCE])) < 0));

Make sure that the date field doesn't contain time information. If so, group by `Round([Date])`. Enclose `Date` in brackets, it's reserved word — Sergey S., May 02 '19 at 15:13
Thanks but that didnt work. What i am looking for is the lowest balance for each account and then the date or dates when the balance was this low — Quigito, May 02 '19 at 17:53
Possible duplicate of [Top n records per group sql in access](https://stackoverflow.com/questions/41220690/top-n-records-per-group-sql-in-access) — June7, May 02 '19 at 18:16
Remove group by date. Only group by account. Then the MIN function can return the minimum for that account. — HackSlash, May 06 '19 at 21:49

Lee Mac · Answer 1 · 2019-05-02T21:08:31.313

There are many ways to achieve this -

Below is one possible method, using an inner join on a subquery that retrieves the value of the minimum balance for each account, such that the join can then yield the corresponding records from the main table whose account & balance fields match those calculated by the subquery.

select t.*
from
    end_of_day_balance t inner join
    (
        select u.account, min(u.[end of day balance]) as mb
        from end_of_day_balance u
        where u.[end of day balance] < 0
        group by u.account
    ) v on
    t.account = v.account and t.[end of day balance] = v.mb

Alternatively, you could use a correlated subquery in the following way:

select t.*
from end_of_day_balance t
where 
    t.[end of day balance] = 
    (
        select min(u.[end of day balance]) 
        from end_of_day_balance u 
        where u.account = t.account and u.[end of day balance] < 0
    )

Here, the subquery is evaluated for each record, with only those records holding the minimum balances validating the where clause and therefore returned for each account.

Getting a Minimum Value and the Date(s) on which it Occurred

1 Answers1