I used this query
DECLARE @ProductTable AS TABLE(Id INT IDENTITY(1,1),IdProduct INT,DateSaller DATETIME,Name VARCHAR(50),DatePlanNext DATETIME,Parametr int)
INSERT INTO @ProductTable VALUES(1,'','Onion','09.09.2018',1)
INSERT INTO @ProductTable VALUES(2,'02.07.2016','Cherry','',0)
INSERT INTO @ProductTable VALUES(3,'09.09.2015','Melon','',0)
INSERT INTO @ProductTable VALUES(4,'10.09.2016','Potato','',0)
INSERT INTO @ProductTable VALUES(5,'10.10.2016','Tomato','',0)
INSERT INTO @ProductTable VALUES(5,'','Tomato','09.09.2017',1)
INSERT INTO @ProductTable VALUES(6,'09.09.2017','Cucumber','',0)
INSERT INTO @ProductTable VALUES(3,'','Melon','09.09.2017',1)
INSERT INTO @ProductTable VALUES(1,'2017-11-12','Onion','',0)
SELECT (ROW_NUMBER() OVER (Order By p1.Id)) Id
,CONVERT(VARCHAR(25),p1.DateSaller,104) as PreDate
,p1.Name as PreProduct
,CONVERT(VARCHAR(25),p2.DateSaller,104) as DateSaller
,p2.Name
,CONVERT(VARCHAR(25),p3.DatePlanNext,104) as DatePlanNext
,p3.Name as NextProduct
FROM @ProductTable p1
INNER JOIN @ProductTable p2
ON p1.IdProduct = p2.IdProduct
JOIN @ProductTable p3 ON p3.IdProduct = p1.IdProduct
WHERE (DATEPART(YEAR,p1.DateSaller)) < (DATEPART(YEAR,GETDATE()))
OR (DATEPART(YEAR,p2.DateSaller)) >= (DATEPART(YEAR,GETDATE()))
AND p1.Parametr = 0 and p2.Parametr = 0
But I want below result:
Id
|
PREDATE
|
PREPRODUCT
|
DateSaller
|
Name
|
DatePlanNext
|
NextProduct
|
1
|
09.09.2015
|
Melon
|
|
|
09.09.2017
|
Melon
|
2
|
09.10.2016
|
Potato
|
|
|
|
|
3
|
10.10.2016
|
Tomato
|
10.10.2017
|
Tomato
|
|
|
4
|
|
|
09.09.2017
|
Cucumber
|
|
|
5
|
|
|
12.11.2017
|
Onion
|
12.11.2018
|
Onion
|
6
|
07.02.2016
|
Cherry
|
|
|
|
|