This is an open-ended "how should I best pull this off" question. I have a table (dbo.session_dates) that will receive dates (no timestamps) and where each date will be unique. There is no particular order in which these dates will be entered. 2013-11-25 might be inserted, followed by 2011-06-07, followed by 2012-03-22, etc - but eventually it will hold most weekday dates consecutively, maybe missing a date here or there. So you can picture a table of roughly consecutive dates, not including weekend dates, and perhaps missing a few dates here and there.
I want to be able, at the end, to be able to select X of "sessions" in time, e.g. "show me the last 20 sessions" which may extend over 200 days, 100 days, or 20 days (or usually ~25 to 30 days, excluding weekends) depending on how the dates have been entered.
I've played around with Row_number function, and I think I can do this with a temp table (code below), but I seems to still involve selecting the entire session_dates table into a temp table.:
IF OBJECT_ID('tempdb..#NumsAndDates') IS NOT NULL DROP TABLE #NumsAndDates
IF OBJECT_ID('tempdb..#NumsAndDates') IS NULL
CREATE TABLE #NumsAndDates(session_num int,session_date date);
insert into #NumsAndDates
select
ROW_NUMBER() over (order by session_date desc),
session_date
from dbo.session_dates
order by 2 desc;
select * from #NumsAndDates
where session_num <= 5
order by session_num asc;
This gives me ALL the dates in the temp table from which I can select the # of sessions I want.
But I'm sure there's a more elegant solution... any ideas would be appreciated
You absolutely do not need a #temp table for this.
;WITH x AS
(
SELECT session_date, rn = ROW_NUMBER() OVER (ORDER BY session_date DESC)
FROM dbo.session_date
)
SELECT session_date FROM x
WHERE rn <= 5
ORDER BY rn;