Recurrence dates SQL design and query

I am a little unclear as to what you are trying to do with the code above.
t is db.train, what is ut? what is l.DateEnded.Last year?

Anyway, if you could post the table structures and some examples of what you are doing, I can probably help. Here is some sample code that I think will get you started.

You can use the Datediff function to compare differences.

declare @today datetime
set @today = GETDATE()

declare @train table (
  id int identity,
  DateStarted datetime,
  DateEnded datetime,
  Passed bit
)

declare @trainees table (
  id int identity,
  trainid int,
  name varchar(10)
)
insert into @train
select '2010-07-01', '2010-07-30', 0 union all
select '2010-12-15', '2011-01-15', 0 

insert into @trainees
select 1, 'adam' union all 
select 1, 'bob' union all 
select 1, 'carl' union all  
select 2, 'donna' union all  
select 2, 'ed' union all  
select 2, 'frank' 


select 
DATEDIFF(year, 0, DateStarted), 
DATEDIFF(year, 0, DateEnded), 
DATEDIFF(year, DateStarted, DateEnded), 
* from @train ut
join @trainees l
on ut.id = l.trainid
and Passed = 0

Select allOpen in new window

amenkes,
thanks for posting, what I am trying to do is:
  I have trainig records in different connected tables
train (trainig name, doc...etc)
trainee (start date and end date, user id and others)
userTrain (records here if trainee takes the training)
and other table (the relation is not a problem, I can get that)
this is an assigned training start from certain date and end at certain date and it has an option to be rebeated every year and the problem is:
how do I query to select training from train table where today's date => start date and today's date <= end date regardless what the year is?
and not in (
userTrain table where taken date is not >= start date of this year )
How do I do that?
thanks!
 

GETDATE() BETWEEN StartDate AND EndDate

Or, does your start and end date fields not contain years?

they contain years, but do you see my point here. I am not just geting the date between start date and end date.  but
If I have a record that has training start date Jul 1 2010 and end date Aug 1 2010 then:
how do I display it when Jul 1 2011 and 2012 and so on and when it is not being taken this year.

if there is a record for each year then I am ok, but not. or do you think this should be handled in the design and if so how? thanks!

I am still not clear, but what you can do is take the year out of the equation. Below is some sample code.

declare @today datetime
set @today = GETDATE()

declare @train table (
  id int identity,
  DateStarted datetime,
  DateEnded datetime,
  Passed bit
)

declare @trainees table (
  id int identity,
  trainid int,
  name varchar(10)
)
insert into @train
select '2010-07-01', '2010-07-30', 0 union all
select '2010-12-15', '2011-01-15', 0 

insert into @trainees
select 1, 'adam' union all 
select 1, 'bob' union all 
select 1, 'carl' union all  
select 2, 'donna' union all  
select 2, 'ed' union all  
select 2, 'frank' 



select DATEADD(YEAR, -YEAR(GETDATE()) + 1900, GETDATE()),
 DATEADD(YEAR, -YEAR(GETDATE()) + 1900,DateStarted),
DATEADD(YEAR, -YEAR(GETDATE()) + 1900, DateEnded),
* from @train ut
join @trainees l
on ut.id = l.trainid
and Passed = 0
where  DATEADD(YEAR, -YEAR(GETDATE()) + 1900, GETDATE()) BETWEEN
 DATEADD(YEAR, -YEAR(GETDATE()) + 1900,DateStarted)
 AND
DATEADD(YEAR, -YEAR(GETDATE()) + 1900, DateEnded)

Select allOpen in new window

Which is the thing that you are not clear about it. Note that I was trying to do it in L2E and not good/deep on T-SQL. but it seems you can do calculation in the query!!! so
 
what is this doing:
DATEADD(YEAR, -YEAR(GETDATE()) + 1900,DateStarted)
AND
DATEADD(YEAR, -YEAR(GETDATE()) + 1900, DateEnded)

Here is an example, with a resulting image.

set @today = '2012-12-31'
select *,
	AdjustedToday = DATEADD(YEAR, -YEAR(@today ) + 1900, @today),
	AdjustedStart = DATEADD(YEAR, -YEAR(DateStarted ) + 1900, DateStarted),
	AdjustedEnd = DATEADD(YEAR, -YEAR(DateStarted ) + 1900, DateEnded)
  from @train ut
join @trainees l
on ut.id = l.trainid
and Passed = 0
where  DATEADD(YEAR, -YEAR(@today ) + 1900, @today ) BETWEEN
DATEADD(YEAR, -YEAR(DateStarted ) + 1900, DateStarted)
AND
DATEADD(YEAR, -YEAR(DateStarted ) + 1900, DateEnded)

Select allOpen in new window

Results.PNG

note that if I get red of the year I still have problems where if the training start Dec 15 2010 and end Jan 15 2011 then today's date (when it is Dec 15 and up is then > than Jan 15
or is it based on the logic  that Dec < Jan. thanks!

The DateAdd(YEAR, ...) is adding a number of years to the current date, in this case, minus years.

So, this year is 2010, so I subtract 2010 and add 1900 (I could have done it for any year, 1904 might have been better, since it has a 2/29). I could have also used date formatting and just converted it to YYYYMMDD format, changed the first 4 characters to some year, '1904' + RIGHT(data, 4), but it is just a preference not not convert dates back and forth to strings.

Once I get all the dates to be between 1/1/1900 and 12/31/1901, I can compare the dates between the adjusted years, so that it does not matter what the year is, but the between will work.

I am sure there are better ways to do it, but this gets you in the right direction. For example, instead of BETWEEN, you can use >= and <=  and it should be faster.

Here is another example, with some extreme dates.

Notice this: set @today = '2345-12-21'

It should have 6 records, 3 for
'2233-12-17', '2234-01-19'
and 3 for
'2010-12-15', '2011-01-15'

since the both start before 12/21 and end after 12.21

now change it to
set @today = '3456-12-16'

it should eliminate the records that start 12/17

declare @today datetime
set @today = GETDATE()

declare @train table (
  id int identity,
  DateStarted datetime,
  DateEnded datetime,
  Passed bit
)

declare @trainees table (
  id int identity,
  trainid int,
  name varchar(10)
)
insert into @train
select '2010-07-01', '2010-07-30', 0 union all
select '2233-12-17', '2234-01-19', 0 union all
select '2010-12-15', '2011-01-15', 0 

insert into @trainees
select 1, 'adam' union all 
select 1, 'bob' union all 
select 1, 'carl' union all  
select 2, 'donna' union all  
select 2, 'ed' union all  
select 2, 'frank' union all  
select 3, 'gary' union all  
select 3, 'hanna' union all  
select 3, 'Ike' 


set @today = '2345-12-21'
select *,
	AdjustedToday = DATEADD(YEAR, -YEAR(@today ) + 1900, @today),
	AdjustedStart = DATEADD(YEAR, -YEAR(DateStarted ) + 1900, DateStarted),
	AdjustedEnd = DATEADD(YEAR, -YEAR(DateStarted ) + 1900, DateEnded)
  from @train ut
join @trainees l
on ut.id = l.trainid
and Passed = 0
where  DATEADD(YEAR, -YEAR(@today ) + 1900, @today ) BETWEEN
DATEADD(YEAR, -YEAR(DateStarted ) + 1900, DateStarted)
AND
DATEADD(YEAR, -YEAR(DateStarted ) + 1900, DateEnded)

Select allOpen in new window

It seems we are close!
the traing is open for one month in the year, and is reopen again next same time next year. So I hope that is clear to you. So I hope that the above will work!
now how do I do the date taken in userTraining (which input records if user take the training and pass), so if the date taken is this year, then don't show/don't display the training to the user.
Can I post the actual feilds and you help with that? thanks!