Need SQL Query to find Min Row where year is Year Getdate()) compared to min Row where Year is Year(-CodePudding

I have below audit table. Need to find Min Row where year is Year Getdate()) compared to min Row where Year is Year(getdate())-1

Audit

Expected Result

Date        ID   Name 

18-01-2022  101  test1

10-02-2021  101  test12

CodePudding user response：

Below should help you to find right solution, use group by, edit where condition to suit your needs

SELECT MIN(YOUR_COLUMN)
FROM YOUR_TABLE
WHERE YEAR(YOUR_COLUMN) > (2020) -- ADD YOU CONDITION HERE
GROUP BY YEAR(YOUR_COLUMN)

CodePudding user response：

You can do this with a standard "Top 1" solution such as ROW_NUMBER.

Whatever you do, don't use functions in the WHERE clause. Use date ranges instead:

SELECT
  t.date,
  t.id,
  t.name
FROM (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY YEAR(date) ORDER BY date) rn
    FROM YourTable t
    WHERE t.date >= DATEFROMPARTS(YEAR(GETDATE()) - 1, 1, 1)
      AND t.date <  DATEFROMPARTS(YEAR(GETDATE())   1, 1, 1)
) t
WHERE t.rn = 1;

CodePudding user response：

I would use a window function to order the dates within each year:

DECLARE @T TABLE([DATE] DATE, ID INT, [NAME] NVARCHAR(20))

INSERT INTO @T VALUES
  ('2022-01-18', 101, 'Test1' )
, ('2022-01-19', 101, 'Test2' )
, ('2022-01-20', 101, 'Test3' )
, ('2022-01-21', 101, 'Test4' )
, ('2021-02-10', 101, 'Test12')
, ('2021-02-11', 101, 'Test13')
, ('2021-02-12', 101, 'Test14')
, ('2021-02-13', 101, 'Test15')


;WITH A AS (
    SELECT [DATE], ID, [NAME], ROW_NUMBER() OVER (PARTITION BY YEAR([DATE]) ORDER BY DATE) RN
    FROM @T)
SELECT YEAR([DATE]), [DATE], ID, [NAME] FROM A WHERE RN = 1 ORDER BY YEAR([DATE]) DESC