471,073 Members | 1,361 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 software developers and data experts.

set of dates

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

Jul 23 '05 #1
3 1191
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
--
Jul 23 '05 #2
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
--
Jul 23 '05 #3
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.

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Riley | last post: by
7 posts views Thread by Alistair | last post: by
5 posts views Thread by PW | last post: by
10 posts views Thread by Colin Steadman | last post: by
1 post views Thread by Don Sealer | last post: by
2 posts views Thread by Rachel Suddeth | last post: by
1 post views Thread by pitfour.ferguson | last post: by
7 posts views Thread by evilcowstare via AccessMonster.com | last post: by
2 posts views Thread by Jim Carlock | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.