Zammy Page Zammy Page - 3 months ago 16
SQL Question

How to get Min Date and Next to min date from the same table in SQL Server

I have a table

create table temp1
(
GroupId int,
DateCol Date,
ValueCol int,
)
insert into temp1 values(1,'2016-1-1',10);
insert into temp1 values(1,'2017-1-1',20);
insert into temp1 values(1,'2018-1-1',30);
insert into temp1 values(2,'2016-5-1',101);
insert into temp1 values(2,'2017-5-1',102);
insert into temp1 values(2,'2018-5-1',103);


I want to get a date range (min to next to min) grouped by GroupId column:

ValueCol DateCol DateColNextToMin
111 (min Date) (next to Min Date)


GroupId ValueCol DateCol DateColNextToMin
1 10 2016-1-1 2017-1-1
2 101 2016-5-1 2017-5-1


How can I do this?

I tried to get thorough windows function , pivot etc, but there seems to be no such elegant solution.

Answer

try this,

DECLARE @table Table
(
   GroupId int,
   DateCol Date,
   ValueCol int
)

INSERT INTO @table
VALUES
     (111,  '01-Aug-2016', 0)
    ,(111,  '02-Aug-2016', 0)
    ,(111,  '03-Aug-2016', 0)
    ,(111,  '04-Aug-2016', 0)
    ,(112,  '05-Aug-2016', 0)
    ,(112,  '06-Aug-2016', 0)
    ,(112,  '07-Aug-2016', 0)
    ,(112,  '08-Aug-2016', 0)

;WITH CTE AS 
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY DateCol ASC) AS RowNo 
    FROM  @table
)
SELECT c1.GroupId, c1.ValueCol, c1.DateCol, c2.DateCol
FROM CTE c1
INNER JOIN CTE c2 ON c1.GroupId = c2.GroupId 
    AND c1.RowNo = 1
    AND c2.RowNo = 2