Selecting last value to be entered each month

Question

I am looking to pull a closing balance from the database for each month.

I have tried

SELECT CloseBal As 'Balance', MONTHNAME(DateTime) as 'Month', DateTime
FROM Table1
WHERE MAX(DateTime)
Group By Month

I am getting an error invalud use of grouping function

What would be the best way to achieve this?

Answer 1

There are a few ways of doing time, depending on some details that were not provided here.

One of then is to use 'ROW_NUMBER' as said in the comment above. As an example:

   SELECT
       Balance,
       Month,
       DateTime
   FROM (
       SELECT 
           CloseBal As 'Balance', 
           MONTHNAME(DateTime) as 'Month', 
           DateTime,
           ROW_NUMBER() OVER (
               PARTITION BY DATE_TRUNC('month', DateTime)
               ORDER BY DateTime DESC) as priority_row
       FROM 
           Table1) t1
   WHERE
       priority_row = 1

There are other possible solutions, but more details need to be provided.

Just an observation: for ANSI SQL, the variable 'Month' doesn't exist until the query runs. Thus, you cannot call it.

Answer 2

The error I think it because you're using the MAX function in the WHERE clause, try the following

SELECT CloseBal As 'Balance', MONTHNAME(DateTime) as 'Month', MAX(DateTime) as 'MaxDate'
FROM Table1
GROUP BY MONTHNAME(DateTime)

You can fine-tune the statement by joining the table with a subquery that returns the MAX DateTime for each MONTHNAME(DateTime):

SELECT t1.CloseBal As 'Balance', MONTHNAME(t1.DateTime) as 'Month', MAX(t1.DateTime) as 'MaxDate'
FROM Table1 t1
JOIN (
SELECT MONTHNAME(DateTime) as Month, MAX(DateTime) as MaxDate
FROM Table1
GROUP BY MONTHNAME(DateTime)
) t2
ON MONTHNAME(t1.DateTime) = t2.Month AND t1.DateTime = t2.MaxDate
GROUP BY MONTHNAME(t1.DateTime);

This should give you the closing balance that matches the MAX(DateTime) for each month.