I am trying to get the second lowest date from the diaries when next is clicked

Transact-SQL
1

This gets the earliest date:-

		Select  dT.FK_RefNo, MIN(dT.inDate) As [EarliestDate]
		FROM( Select FK_RefNo, DiaryDate As [inDate] From [tblDiary])
		            As dT Group By FK_RefNo

But how do I ido a button to go to next day, in the array of dates? The form loads the earliest day by default, so would like to climb through the actual days with a diary entry.

Thanks

2

This is one way of accomplish the task:

select fk_refno
      ,max(indate) as secondearliestdate
  from (select top(2) with ties
              ,fk_refno
              ,indate
          from tbldiary
         order by row_number() over(partition by fk_refno
                                        order by indate
                                   )
       ) as a
 group by fk_refno
;
3 Likes
3

Think this is the right syntax.

select a.fk_refno
,max(a.indate) as secondearliestdate
from (select top(2) with ties
fk_refno
, DiaryDate as indate
from tbldiary
order by row_number() over(partition by fk_refno
order by DiaryDate
)
) as a
group by fk_refno
;