Recurrence dates SQL design and query

fm250
fm250 used Ask the Experts™
on
I have a need for showing recurrence dates on training table based on true/false field for everyYear field.  And want to know how to handle that in SQL.

Here is the portion of the query that I need help with:
(this in Linq to Entities but I need it in T-SQL as L2E 3.5 won't handle it)
 

So I need to query the table and see
-      If it is every year (I got that) then next year and the years after I show the records based on the month for example
If the training start date july 1 to end date july 30 then the record is displayed no matter what the year is based on today’s date?
-      And also if the date taken is  within this year then records is not displayed
-      how do I handle a date from Dec 15 to Jan 15 (this is going to be the next year)
DateTime today = DateTime.Today.Date;
From  t in  db.train
 from l in t.Trainees

Where today >= l.DateStarted && today <= l.DateEnded 

Where not in ( ut.Passed == true && ut.DateTaken  > l. DateEnded.Last year )

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
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

Open in new window

Author

Commented:
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!
 
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
GETDATE() BETWEEN StartDate AND EndDate
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Adam MenkesC# ASP.NET Developer
Top Expert 2010

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

Author

Commented:
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.

Author

Commented:
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!
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
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)

Open in new window

Author

Commented:
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)
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
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)

Open in new window

Results.PNG

Author

Commented:
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!
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
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.

Adam MenkesC# ASP.NET Developer
Top Expert 2010
Commented:
Actually, if you look closely at my sample, you will see that the last DATEADD for DateEnded still uses the DateStart for the - Years.

This is how it handles the year rollover.

So, as long as the enddate is later than the startdate, it should work, even if the startdate is 12/15/2010 and the enddate is 3/4/2015.
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
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)

Open in new window

Author

Commented:
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!
C# ASP.NET Developer
Top Expert 2010
Commented:
Yes, based on the way you have your data, it should work if you don't semi-duplicate classes (for example,
startdate 7/1/10 enddate 7/30/10
and have a 2nd entry for the same class
startdate 7/2/11 enddate 7/30/11

You might need to update the existing record, rather than add a new one. It depends on your design.

Yes, if you want to post the fields and some sample data, I think I can help get you the rest of the way.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial