Add newest values until current date with the year and month separated as 2 INT columns

Question

So I have this query to insert data into a table (year and month are when the row was created)

INSERT INTO Table#1 (ID, col2, Year, Month)

SELECT B.ID, B.col2, A.InvoiceYear, A.InvoiceMonth

FROM Table#2 AS A

INNER JOIN Table#3 AS B ON B.ID = A.ID

ORDER BY A.InvoiceYear DESC, A.InvoiceMonth DESC

The thing is that everytime i run that query, it add all the data all over again, and i want only the newest data from the last update. So the thing i wanted to do is adding:

WHERE A.InvoiceYear >= MAX(table#1.Year)
AND A.InvoiceMonth > MAX(table#1.Month)

The problem with it, first of all, it didnt work (error 1111: Invalid use of group function), and second, the maximum value for month its going to be 12, because i have data from 4 years. Is there a way to add data that the year and month is more recent that the oldest value added?

Thanks for the help, if something wasnt clear please ask me, it's not my native language

Answer 1

You could use WHERE NOT EXISTS to achieve what you need:

  INSERT INTO Table#1 (ID, col2, Year, Month)
  SELECT B.ID, B.col2, A.InvoiceYear, A.InvoiceMonth
    FROM Table#2 AS A
    JOIN Table#3 AS B ON B.ID = A.ID
   WHERE NOT EXISTS (SELECT * 
                       FROM Table#1 AS C 
                      WHERE C.Year = A.InvoiceYear
                        AND C.Month = A.InvoiceMonth)
ORDER BY A.InvoiceYear DESC, A.InvoiceMonth DESC;

You can find more information here: EXISTS (Transact-SQL)

Answer 2

You have a couple of options for this. Performance-wise, NOT EXISTS is likely to be quicker for a simple statement such as this, but if your actual requirement is more complex than detailed in your question, MERGE or JOIN may outperform NOT EXISTS. As per normal, test and optimise before deploying to production.

First, you can use NOT EXISTS to exclude the rows in question:

SELECT B.ID, B.col2, A.InvoiceYear, A.InvoiceMonth
FROM Table#2 AS A
INNER JOIN Table#3 AS B ON B.ID = A.ID
WHERE NOT EXISTS ( SELECT *
  FROM Table#1 c
  WHERE C.ID = B.ID AND C.Year = A.InvoiceYear AND C.Month = A.InvoiceMonth
)
ORDER BY A.InvoiceYear DESC, A.InvoiceMonth DESC

Another option is to join Table#1 to your SELECT statement to filter the records:

SELECT B.ID, B.col2, A.InvoiceYear, A.InvoiceMonth
FROM Table#2 AS A
INNER JOIN Table#3 AS B ON B.ID = A.ID
LEFT JOIN Table#1 AS C ON C.ID = A.ID
WHERE C.ID IS NULL
ORDER BY A.InvoiceYear DESC, A.InvoiceMonth DESC

Or you can use a MERGE statement

MERGE Table#1 AS target
USING (
SELECT B.ID, B.col2, A.InvoiceYear, A.InvoiceMonth
FROM Table#2 AS A
INNER JOIN Table#3 AS B ON B.ID = A.ID
WHERE C.ID IS NULL
ORDER BY A.InvoiceYear DESC, A.InvoiceMonth DESC) AS source (ID, Col2, Year, Month)
ON (target.ID = source.ID AND target.Year = source.Year AND target.Month = source.Month)
WHEN NOT MATCHED THEN
INSERT (ID, Col2, Year, Month)
VALUES (source.ID, source.Col2, source.Year, source.Month)

Answer 3

If you just wanted the latest one, then tack on LIMIT 1.

Tip: Don't split dates into separate columns.