I am trying to write a SQL statement which will give me the First and Last dates with the amounts.
I am using the following SQL to get the total first date for the ref and the dates for first and last entry but I need to get the first entry for that ref and the last entry with the first amount and last amount. I have tried using FIRST and LAST functions but I am not getting the results I want.
SELECT COUNT(DISTINCT ref) AS REF,
MIN (FIRST_DATE) AS FIRST_DATE,
MAX(LAST_DATE) AS LAST_DATE,
SUM(FIRST_AMOUNT) AS FIRST_AMOUNT,
SUM(LAST_AMOUNT) AS LAST_AMOUNT,
GROUP BY REF Select-First-and-Last-Amount.xlsx
The reason for the last date being before the first date is because I created a random set of data. I have amended the data to ensure the ref column is unique. I need to select the first and last date and amount for the Customer column.
Hopefully, this clears up some of the confusion.