Display products details in separate year column in SQL Server

Hi Firuz,

The Data and Output you provided is wrong because of it the actual output was not coming as excepted.

Below is the correct data and actual output you wanted.

Refer below SQL Query.

SQL

DECLARE @ProductTable AS TABLE(Id INT IDENTITY(1,1),IdProduct INT,DateSaller DATETIME,Name VARCHAR(50),DatePlanNext DATETIME,Parametr int)
    
INSERT INTO @ProductTable(IdProduct,Name,DatePlanNext,Parametr) VALUES(1,'Onion','09.09.2018',1)
INSERT INTO @ProductTable(IdProduct,DateSaller,Name,Parametr) VALUES(2,'02.07.2016','Cherry',0)
INSERT INTO @ProductTable(IdProduct,DateSaller,Name,Parametr) VALUES(3,'09.09.2015','Melon',0)
INSERT INTO @ProductTable(IdProduct,DateSaller,Name,Parametr) VALUES(4,'10.09.2016','Potato',0)
INSERT INTO @ProductTable(IdProduct,DateSaller,Name,Parametr) VALUES(5,'10.10.2016','Tomato',0)
INSERT INTO @ProductTable(IdProduct,Name,DatePlanNext,Parametr) VALUES(5,'Tomato','09.09.2017',1)
INSERT INTO @ProductTable(IdProduct,DateSaller,Name,Parametr) VALUES(6,'09.09.2017','Cucumber',0)
INSERT INTO @ProductTable(IdProduct,Name,DatePlanNext,Parametr) VALUES(3,'Melon','09.09.2017',1)
INSERT INTO @ProductTable(IdProduct,DateSaller,Name,Parametr) VALUES(1,'2017-11-12','Onion',0)

DECLARE @ExceptedProductTable AS TABLE (ID INT IDENTITY(1,1),PreDate DATETIME,PreProduct VARCHAR(50),DateSaller DATETIME,Name VARCHAR(50),DatePlanNext DATETIME,NextProduct VARCHAR(50))

DECLARE @Count INT
SET @Count = (SELECT COUNT(*) FROM @ProductTable)

DECLARE @Id INT
SET @Id = 1

DECLARE @CurrentYear INT
SET @CurrentYear = (SELECT DATEPART(YEAR,GETDATE()))	

WHILE @Count > 1
	BEGIN
		DECLARE @DateSaller DATETIME
        SET @DateSaller = (SELECT DateSaller FROM @ProductTable WHERE Id = @Id)
        DECLARE @DatePlanNext DATETIME
        SET @DatePlanNext = (SELECT DatePlanNext FROM @ProductTable WHERE Id = @Id)
        DECLARE @Name VARCHAR(50)
        SET @Name = (SELECT Name FROM @ProductTable WHERE Id = @Id)
        DECLARE @PreviousYear INT
        SET @PreviousYear = (SELECT DATEPART(YEAR,DateSaller) FROM @ProductTable WHERE Id = @Id)
        DECLARE @Parameter INT
        SET @Parameter = (SELECT Parametr FROM @ProductTable WHERE Id = @Id)  
        DECLARE @NextProduct VARCHAR(50)
        SET @NextProduct = (SELECT Name FROM @ProductTable WHERE Id = @Id AND Parametr = 1)        
        
        IF(@PreviousYear < @CurrentYear OR @Parameter = 1)
			BEGIN
				INSERT INTO @ExceptedProductTable(PreDate,PreProduct,DatePlanNext,NextProduct)
				VALUES(@DateSaller,@Name,@DatePlanNext,@NextProduct)
			END
		ELSE
			BEGIN
				INSERT INTO @ExceptedProductTable(DateSaller,Name)
				VALUES(@DateSaller,@Name)
			END  
	SET @Count = @Count - 1
    SET @Id = @Id + 1   		   
	END	
	
SELECT * FROM @ExceptedProductTable

OutPut