Good day ....
I am trying to get to the last amount for the last day of the previous month. In this snippet it would be the BeforeBalance field where the AccountSequenceNumber is the highest:
So for this example, I only want the row with the $3,432.87 when I run the query.
I was trying to use dateadd(month, datediff(month,0, getdate()) - 1,0). However, that provides me all of the transactions for the first day of the previous month. I am looking for the last transaction of the last day of the month.
Here are the fields I am working with:
Thank you.
There is a funtion for that EOMONTH()
EOMONTH (Transact-SQL) - SQL Server | Microsoft Docs
You can also use a subquery to select the max accountSequenceNumber. Als you provided a picture I cannot test the code but it should like something like this:
SELECT BeforeBalance
FROM AccountHistory ah
WHERE AccountSequenceNumber=(SELECT
MAX(AccountSequenceNumber) FROM AccountHistory ah_ WHERE ah.EntryDate= EOMONTH(dateadd(month, datediff(month,-1, getdate()),0))) AND ah_MemberNumber=ah.MemberNumber AND ah.AccountNumber=ah_.AccountNumber)
You can also use a CTE to select the max AccountSequenceNumber for each member/account and use that. I would prefer the last optie as the table is large but above code looks like the most you tried yourself.
Two ways to get the end of the previous month:
Select eomonth(getdate(), -1)
, dateadd(month, datediff(month, -1, getdate()) - 1, -1)
If the data type of the column EntryDate is a date data type - then EOMONTH would be a better option as it returns a date. If the columns data type is datetime, then the second option would be better as it returns a datetime.
With that said - are you certain you will always have a row on the last day of the month for every member? If not, and you want the latest row available in that month for each member - then it becomes a bit more challenging. If that is the case, let us know and we can provide a different solution.
Note: no reason to use DISTINCT - unless you know there could be duplicate values in the results.
Final question: do you want the row with the highest value returned - or just the highest value in that column? If you want the row - then you need to look at row_number() function, something like rn = ROW_NUMBER() OVER(Partition By MemberNumber Order By AccountSequenceNumber DESC).
Put the query in a CTE or derived table and select from the CTE/derived table where rn = 1.
1 Like
I know what the Op said and your code does that (visual check only) but what happens if the transaction they're looking for doesn't exist in the last day of the month?
And what if a given Account doesn't have an entry on the last day of the month?
