insert into Table (select All Mondays between StartDate and EndDate, numbered)

hi, i need to make events for each Monday (or any day) between StartDate and EndDate, i am clueless


the user will enter startdate and enddate, and i will number each one of them, (not according to week, there will be no events in school holidays, so week10 of the normal calender could be Monday8, if 2 weeks are skipped)

INSERT INTO EVENT(Title,Date,EventNr)
e.g.
Monday 1, 200x/01/01, 1
Monday 2, 200x/01/08, 2
Monday 3, 200x/01/15, 3

please help.
i have looked at this project
http://www.codeproject.com/KB/database/sqlscheduleselector.aspx
but it does not enter any events into databases.

jxhardingAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
declare @date1 datetime, @date2 datetime

select @date1 = '1/1/2009', @date2 = '4/6/2009'

set datefirst 1
;with dates(firstdate, lastdate, mondaycount)
as
(
select @date1, @date1, case when datepart(dw, @date1) = 1 then 1 else 0 end
union all
select firstdate, dateadd(d, 1, lastdate), case when datepart(dw,dateadd(d, 1, lastdate) ) = 1 then 1 else 0 end
from dates d
where lastdate <= @date2
)
insert into events(title, date, eventnmbr)
select 'Monday', lastdate, row_number() over(order by lastdate asc) from dates
where mondaycount > 0
0
 
jxhardingAuthor Commented:
awesome stuff!! i am trying to make head or tails here still.
i am obviously awarding the points, and i see you are nr3 on the hall of fame, so you are busy, but if possible , could you pls save a few seconds on some short questions

1.
set datefirst 1
-is this a declaration and instatiation all in one?
-is it/is it not the same as declare datefirst; set datefirst =1

2.
;with dates
-im trying to google "SQL SERVER WITH" but not getting good results.
am i correct in observing that this is a table declaration and the items in brackets are the column names?
- is this some sort of new way to not work with #tables? (temp tables)

3.
which line of code actually generates the list of dates?

thanks!
0
 
chapmandewCommented:
Hi, no problem...glad it worked for you.  
1.  The set date first made sure that we are both using the same date for the first day of the week. That way when I do this:  datepart(dw,dateadd(d, 1, lastdate) ) = 1, it evaluates to Monday.

2.  This is a common table expression.  Here is an article I wrote about them.  
http://blogs.techrepublic.com.com/datacenter/?p=275

3.  They're all a part of it.  If you look at this:
--this generates the very first line
select @date1, @date1, case when datepart(dw, @date1) = 1 then 1 else 0 end
--this generates all lines after it.
union all
select firstdate, dateadd(d, 1, lastdate), case when datepart(dw,dateadd(d, 1, lastdate) ) = 1 then 1 else 0 end
from dates d
where lastdate <= @date2
--this very last line limits the number so that it only loops up to the date you want.  

Try reading the article I wrote...it might make more sense afterwards.

HTH,
Tim
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
jxhardingAuthor Commented:
sometimes great posts like these come along and pinpoint exactly what one needs to learn, thanks a lot!!!
0
 
chapmandewCommented:
You are very welcome!  Glad to help.
Tim
0
 
chapmandewCommented:
BTW..you can check out my new website at www.sqlservernation.com.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.