November 4, 2015 at 2:16 am
Hi,
I want to find previous date from selected date. below is the sample data.
DECLARE @StartDate SMALLDATETIME = '1/11/2016'
declare @tempdat table(repdate smalldatetime)
insert into @tempdat values ('10/26/2015')
insert into @tempdat values ('10/29/2015')
insert into @tempdat values ('11/1/2015')
insert into @tempdat values ('11/27/2015')
insert into @tempdat values ('11/25/2015')
insert into @tempdat values ('11/20/2015')
insert into @tempdat values ('11/10/2015')
insert into @tempdat values ('11/10/2015')
insert into @tempdat values ('11/11/2015')
insert into @tempdat values ('11/11/2015')
Now if i pass the date '10/26/2015' then i want select prev date of passed date. in this example no prev date is available, so result set would be nothing.
if i pass the date '11/10/2015' then result should be '11/1/2015' which is prev small date available in table.
please help.
Thanks,
Abhas.
November 4, 2015 at 2:31 am
select max(repdate) from @tempdat where repdate < @StartDate
November 4, 2015 at 3:03 am
Thank you Anthony.
November 4, 2015 at 5:11 am
If you want other columns, from the row that has the next most recent date, then:
select TOP 1 Col1, Col2, ...
FROM @tempdat
where repdate < @StartDate
ORDER BY repdate DESC, OtherTieBreakColumn1, ...
November 4, 2015 at 8:10 pm
Kristen-173977 (11/4/2015)
If you want other columns, from the row that has the next most recent date, then:
select TOP 1 Col1, Col2, ...
FROM @tempdat
where repdate < @StartDate
ORDER BY repdate DESC, OtherTieBreakColumn1, ...
Not only for other columns.
If column [repdate] is indexed then SELECT TOP 1 repdate ... will be faster than SELECT MAX(repdate)
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply