I have a table in SQL Server that holds data organised by BusinessDataDate. BusinessDataDate is a Date only (no time component) and is only populated on Business days. e.g. not Weekends or public holidays.
Because there is no specific order to this, I want to find the date before the current max date
This query works, and I can set the values into local variables - but it feels like there must be a cleaner way?
SELECT MAX(BusinessDataDate) FROM myTable
WHERE BusinessDataDate < (SELECT MAX(BusinessDataDate) FROM myTable)
Each date will have multiple rows in the table - the exact number is not predictable.
SELECT TOP (1)
BusinessDataDate
FROM
(
SELECT
BusinessDataDate,
DENSE_RANK() OVER (ORDER BY BusinessDataDate DESC) as rn
FROM
myTable
) x
WHERE
x.rn = 2
ORDER BY
x.BusinessDataDate;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With