set of dates

[phancey]

I've got a table (TYPE, DAYMTH) where it is a compound key and the
DAYMTH is a datetime but all years are 1900 ONLY THE day and month are
significant.

What I want to do is for each type, find the next n dates - so for
example:
TYPE DAYMTH
1 1900-02-20
1 1900-05-05
1 1900-09-14
1 1900-11-11
2 1900-03-03
2 1900-07-07

if I want to get the next 3 dates after 22 June 2005 it should give me:
1 2005-09-14
1 2005-11-11
1 2006-02-20
2 2005-07-07
2 2006-03-03
2 2006-07-07

so, anyone got any bright ideas?

I've started down this route:
create table #year (yr int)
insert into #year values(2005)
insert into #year values(2006)
insert into #year values(2007)
insert into #year values(2008)
select *,convert(datetime,convert(varchar,yr) +
substring(convert(varchar,daymth,20),5,20),20)
from DAYMTHtable,#year
where convert(datetime,convert(varchar,yr) +
substring(convert(varchar,daymth,20),5,20),20) > getdate()
order by type,convert(datetime,convert(varchar,yr) +
substring(convert(varchar,daymth,20),5,20),20)

and although this seems to be a good start I wonder if there are better
solutions. Also any quick ideas on how to finish if I decide to follow
my existing thought process?

thanks
Phil

[David Portas]

Why not just populate your calendar table with all the years you'll ever
need instead of just one year? 200 years would be less than 1MB of data and
would make all your queries much simpler.

--
David Portas
SQL Server MVP
--

[David Portas]

Here's a different attempt:

DECLARE @dt DATETIME
SET @dt = '20050622'

SELECT type,
DATEADD(YEAR,YEAR(@dt)-1900+
CASE WHEN MONTH(daymth)*100+DAY(daymth)
<MONTH(@dt)*100+DAY(@dt) THEN 1 ELSE 0
END,daymth) AS dt
FROM daymth
ORDER BY type, dt ;

--
David Portas
SQL Server MVP
--

[phancey]

this doesn't work as it will give me 4 dates for 1 and 2 dates for 2.

I decided to carry on with my initial thoughts and followed the first
select (which I changed to select into #tmp) with this:

select t.* from #tmp t where 3 > (select count(*) from #tmp t2
where t.type = t2.type and t2.fdate < t.fdate)
order by type
ALSO, although you're right about my year table it would require a
database change which as you probably know sometimes meets resistance
from DBAs/Project Managers etc and generally is a whole lot more hassle.