adjust my code

Hi,
I've created this code to determine the dates for an exam when a
certification must be met within a X number of months or years. The last
deadline date for an exam is the enddate. The marge where one can
schedule his time to study depends on how many exams he must take. So
based on the enddate i calculate backwards to the startdate to get
intervals of when the next examdate should be.
In my testscenario i used 12 months and the number of exams are 4. The
code gave me the results i needed, but when i tried e.g. 18 months the
results are not what i expected. The dates are somewhat correct but
there were too many dates.
Can someone see the error in my code?
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int,
set @.begindate = '2005-11-23'
set @.enddate = dateadd(mm,12,@.begindate)
set @.exams = 4
set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
create table #examdates(id int identity(1,1) , examdate datetime)
--i inserted the enddate as startingpoint, but this could be done
--better, i think
insert into #examdates(examdate)
values(@.enddate)
while (select count(*) from #examdates) <= @.intervals
begin
set @.examdate = (select examdate from #examdates where id in
(select max(id) from #examdates))
insert into #examdates(examdate)
select dateadd(mm,-@.exams,@.examdate) as examdate
end
select examdate from #examdates order by examdateJason
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
>
What do you want to return if you put 18 months in?
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:eonG0vfEGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I've created this code to determine the dates for an exam when a
> certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can schedule
> his time to study depends on how many exams he must take. So based on the
> enddate i calculate backwards to the startdate to get intervals of when
> the next examdate should be.
> In my testscenario i used 12 months and the number of exams are 4. The
> code gave me the results i needed, but when i tried e.g. 18 months the
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
> Can someone see the error in my code?
> declare @.exams int,
> @.begindate datetime,
> @.enddate datetime,
> @.examdate datetime,
> @.intervals int,
> set @.begindate = '2005-11-23'
> set @.enddate = dateadd(mm,12,@.begindate)
> set @.exams = 4
> set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
> create table #examdates(id int identity(1,1) , examdate datetime)
> --i inserted the enddate as startingpoint, but this could be
> done --better, i think
> insert into #examdates(examdate)
> values(@.enddate)
> while (select count(*) from #examdates) <= @.intervals
> begin
> set @.examdate = (select examdate from #examdates where id in
> (select max(id) from #examdates))
> insert into #examdates(examdate)
> select dateadd(mm,-@.exams,@.examdate) as examdate
> end
> select examdate from #examdates order by examdate|||You are creating a variable called intervals which is really the number of
months that you have to take each exam based on the way that you set it.
datediff(mm,@.begindate,@.enddate)/@.exams
So for 12 months this would be 3 but for 18 months it would be 4.5.
You then use this as the loop constraint for adding in your exam dates.
It seems to me like your look constraint should just be the number of exams
that you need to take since you are trying to find a date when to take each
of the exams.
This is the core of your problem. I didn't put any thought into the rest of
the algorithm and how you might be able to accomplish your goal easier.
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Jason" wrote:

> Hi,
> I've created this code to determine the dates for an exam when a
> certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can
> schedule his time to study depends on how many exams he must take. So
> based on the enddate i calculate backwards to the startdate to get
> intervals of when the next examdate should be.
> In my testscenario i used 12 months and the number of exams are 4. The
> code gave me the results i needed, but when i tried e.g. 18 months the
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
> Can someone see the error in my code?
> declare @.exams int,
> @.begindate datetime,
> @.enddate datetime,
> @.examdate datetime,
> @.intervals int,
> set @.begindate = '2005-11-23'
> set @.enddate = dateadd(mm,12,@.begindate)
> set @.exams = 4
> set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
> create table #examdates(id int identity(1,1) , examdate datetime)
> --i inserted the enddate as startingpoint, but this could be done
> --better, i think
> insert into #examdates(examdate)
> values(@.enddate)
> while (select count(*) from #examdates) <= @.intervals
> begin
> set @.examdate = (select examdate from #examdates where id in
> (select max(id) from #examdates))
> insert into #examdates(examdate)
> select dateadd(mm,-@.exams,@.examdate) as examdate
> end
> select examdate from #examdates order by examdate
>|||Ryan Powers wrote:
> You are creating a variable called intervals which is really the number of
> months that you have to take each exam based on the way that you set it.
> datediff(mm,@.begindate,@.enddate)/@.exams
> So for 12 months this would be 3 but for 18 months it would be 4.5.
> You then use this as the loop constraint for adding in your exam dates.
> It seems to me like your look constraint should just be the number of exam
s
> that you need to take since you are trying to find a date when to take eac
h
> of the exams.
>
> This is the core of your problem. I didn't put any thought into the rest
of
> the algorithm and how you might be able to accomplish your goal easier.
> HTH
>
Hi Ryan,
I want to calculate a date for taking an exam. The intervals are just
the number of months when the next exam must be taken.
Could you point out to me where i should better my code?|||Uri Dimant wrote:
> Jason
>
>
> What do you want to return if you put 18 months in?
>
> "Jason" <jasonlewis@.hotmail.com> wrote in message
> news:eonG0vfEGHA.2040@.TK2MSFTNGP14.phx.gbl...
>
>
>
Hi Uri,
I want to return 4 examdates because the number of exams to be taken are
4, the higher the months the longer someone may study until the next
date occurs.
In case of adding 18 months to the begindate, divide that with the
number of exams, you'll get 4.5 months. So knowing the enddate (is also
the last examdate deadline) i substract the 4.5 months from the enddate
which will give me the examdate before that and so on.
Can you find the mistake i have made in the code?|||Try the following script. I have given some hint about changes made in your
script. Inside the While loop, I have removed the select statement from the
#examdates table. This will enhance performance.
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int
-- Added script
declare @.NextExamDate datetime, @.LastExamDate datetime
--
set @.begindate = '20051101'
set @.enddate = dateadd(mm,12,@.begindate)
-- Added script
set @.LastExamDate = @.enddate
--
set @.exams = 4
--[replaced with below line] set @.intervals =
datediff(mm,@.begindate,@.enddate)/@.exams
set @.intervals = datediff(dd,@.begindate,@.enddate)/@.exams
--create table #examdates(id int identity(1,1) , examdate datetime)
--i inserted the enddate as startingpoint, but this could be done
--better, i think
insert into #examdates(examdate)
values(@.enddate)
-- While loop script with lots of changes
while @.exams > 1
begin
set @.NextExamDate = dateadd(dd,-@.intervals,@.LastExamDate)
insert into #examdates(examdate) values(@.NextExamDate)
set @.LastExamDate = @.NextExamDate
set @.exams = @.exams -1
end
--
select examdate from #examdates order by examdate
"Jason" wrote:

> Ryan Powers wrote:
> Hi Ryan,
> I want to calculate a date for taking an exam. The intervals are just
> the number of months when the next exam must be taken.
> Could you point out to me where i should better my code?
>|||Sure. You don't need the intervals variable, since you are actually
recalcing it on the fly within the loop. I am going to just keep your base
logic, and show you how you can correct it so it works.
I changed it slightly to find days between exams because I'm thinking that
you can evenly divide the months by the number of exams is not correct. Wha
t
I did will not necessarily give you your last exam on your end date due to
integer math. But, it should be close. We could put in a condition that
checks if we are setting the last date and just set it as the enddate. Let
me know if you need help with that.-
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int,
@.months int,
@.daysBetweenExams
set @.begindate = '2005-11-23'
set @.exams = 4
set @.months = 12
set @.enddate = dateadd(mm,@.months,@.begindate)
set @.daysBetweenExams = datediff(dd, @.begindate, @.enddate)/@.exams
create table #examdates(id int identity(1,1) , examdate datetime)
set @.examdate = @.begindate
while (select count(*) from #examdates) <= @.exams
begin
SELECT @.examdate = dateadd(dd, @.daysBetweenExams, @.examdate)
insert into #examdates(examdate)
values(@.examdate)
end
select examdate from #examdates order by examdate
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Jason" wrote:

> Ryan Powers wrote:
> Hi Ryan,
> I want to calculate a date for taking an exam. The intervals are just
> the number of months when the next exam must be taken.
> Could you point out to me where i should better my code?
>|||On Thu, 05 Jan 2006 14:28:42 +0100, Jason wrote:

>Hi,
>I've created this code to determine the dates for an exam when a
>certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can
>schedule his time to study depends on how many exams he must take. So
>based on the enddate i calculate backwards to the startdate to get
>intervals of when the next examdate should be.
>In my testscenario i used 12 months and the number of exams are 4. The
>code gave me the results i needed, but when i tried e.g. 18 months the
>results are not what i expected. The dates are somewhat correct but
>there were too many dates.
>Can someone see the error in my code?
Hi Jason,
Why not use a set-based query instead of looping?
-- inputs:
DECLARE @.begindate datetime,
@.enddate datetime,
@.exams int
SET @.begindate = '2005-11-23'
SET @.enddate = '2007-05-23'
SET @.exams = 4
-- generate exam dates
--INSERT INTO @.examdate(examdate)
SELECT DATEADD(month,
Number * DATEDIFF(month, @.begindate, @.enddate) / @.exams,
@.begindate)
FROM dbo.Numbers
WHERE Number BETWEEN 1 AND @.exams
Note: this requires the use of a numbers table. See www.aspfaq.com/2516.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> On Thu, 05 Jan 2006 14:28:42 +0100, Jason wrote:
>
>
> Hi Jason,
> Why not use a set-based query instead of looping?
> -- inputs:
> DECLARE @.begindate datetime,
> @.enddate datetime,
> @.exams int
> SET @.begindate = '2005-11-23'
> SET @.enddate = '2007-05-23'
> SET @.exams = 4
> -- generate exam dates
> --INSERT INTO @.examdate(examdate)
> SELECT DATEADD(month,
> Number * DATEDIFF(month, @.begindate, @.enddate) / @.exams,
> @.begindate)
> FROM dbo.Numbers
> WHERE Number BETWEEN 1 AND @.exams
> Note: this requires the use of a numbers table. See www.aspfaq.com/2516.
>
Hi hugo,
your solution did the job. Thnx!