Hi! I used below sql query it’s show this result:
DECLARE @ProductTable AS TABLE(Id INT IDENTITY(1,1),IdProduct INT,DateSaller DATE,Name VARCHAR(50))
INSERT INTO @ProductTable VALUES(1,'2017-11-12','Onion')
INSERT INTO @ProductTable VALUES(2,'02.07.2017','Cherry')
INSERT INTO @ProductTable VALUES(3,'09.09.2015','Melon')
INSERT INTO @ProductTable VALUES(4,'10.09.2017','Potato')
INSERT INTO @ProductTable VALUES(5,'10.10.2017','Tomato')
INSERT INTO @ProductTable VALUES(4,'10.09.2016','Potato')
INSERT INTO @ProductTable VALUES(5,'10.10.2016','Tomato')
INSERT INTO @ProductTable VALUES(6,'09.09.2017','Cucumber')
INSERT INTO @ProductTable VALUES(3,'09.09.2017','Melon')
INSERT INTO @ProductTable VALUES(6,'09.09.2016','Cucumber')
INSERT INTO @ProductTable VALUES(1,'2016-11-12','Onion')
INSERT INTO @ProductTable VALUES(2,'02.07.2016','Cherry')
;WITH CTE AS
(
SELECT IdProduct, CONVERT(VARCHAR(25),DateSaller,104) AS PREDATE, Name AS PREPRODUCT, '' AS DateSaller, '' AS Name FROM @ProductTable WHERE (DATEPART(YEAR,DateSaller)) < (DATEPART(YEAR,GETDATE()))
UNION ALL
SELECT IdProduct, '' AS PREDATE, '' AS PREPRODUCT, CONVERT(VARCHAR(25),DateSaller,104)DateSaller, Name FROM @ProductTable WHERE (DATEPART(YEAR,DateSaller)) >= (DATEPART(YEAR,GETDATE()))
)
SELECT ROW_NUMBER() OVER(ORDER BY(SELECT 0)) AS Id, PREDATE, PREPRODUCT, DateSaller, Name FROM CTE
Id
|
PREDATE
|
PREPRODUCT
|
DateSaller
|
Name
|
1
|
09.09.2015
|
Melon
|
|
|
2
|
09.10.2016
|
Potato
|
|
|
3
|
10.10.2016
|
Tomato
|
|
|
4
|
09.09.2016
|
Cucumber
|
|
|
5
|
12.11.2016
|
Onion
|
|
|
6
|
07.02.2016
|
Cherry
|
|
|
7
|
|
|
12.11.2017
|
Onion
|
8
|
|
|
07.02.2017
|
Cherry
|
9
|
|
|
09.10.2017
|
Potato
|
10
|
|
|
10.10.2017
|
Tomato
|
11
|
|
|
09.09.2017
|
Cucumber
|
12
|
|
|
09.09.2017
|
Melon
|
But I want this result: output result in below
Id
|
PREDATE
|
PREPRODUCT
|
DateSaller
|
Name
|
1
|
09.09.2015
|
Melon
|
09.09.2017
|
Melon
|
2
|
09.10.2016
|
Potato
|
09.10.2017
|
Potato
|
3
|
10.10.2016
|
Tomato
|
10.10.2017
|
Tomato
|
4
|
09.09.2016
|
Cucumber
|
09.09.2017
|
Cucumber
|
5
|
12.11.2016
|
Onion
|
12.11.2017
|
Onion
|
6
|
07.02.2016
|
Cherry
|
07.02.2017
|
Cherry
|
Who is you can help me?