February 19, 2009 at 11:37 pm
hi,
i have a proble please help me
i hav a table which consists of userid and date
table1
userid startdate enddate
100 04/2/2009 04/30/2009
user 100 is under work from 2nd april to 30th april
if i try to give work from 04/10/2009 to 04/20/2009 to the user 100
now i should show that the user is already in work for given dates
how can I??
thanks in advance...
February 20, 2009 at 12:08 am
Let us assume from date = 04/10/2009 and to date = 04/20/2009
then
SELECT * FROM Table t WHERE Userid = 100
AND (@FromDate between t.FromDate and t.ToDate OR @ToDate between t.FromDate and t.ToDate)
The query returns the records if the work allocated to the user 100.
Either one of the date lies in the record already present then the above query works. if you want to check both the dates in the range then change OR to AND.
Eg:
Existing Record : 100, 04/02/2009 04/30/2009
Inputs :
From : 04/10/2009(true) To: 04/20/2009(true) Returns record
From : 04/15/2009(true) To: 05/01/2009 Returns record
From : 03/10/2009 To: 04/20/2009(true) Returns record
FROM : 03/10/2009 To: 05/01/2009 Returns no record
SELECT * FROM Table t WHERE Userid = 100
AND ( @FromDate between t.FromDate and t.ToDate OR @ToDate between t.FromDate and t.ToDate
OR (@FromDate t.ToDate)
)
FROM : 03/10/2009 To: 05/01/2009 Returns record
February 20, 2009 at 12:14 am
Please ignore my previous post some of the character are not displayed
Let us assume from date = 04/10/2009 and to date = 04/20/2009
then
SELECT * FROM Table t WHERE Userid = 100
AND (@FromDate between t.FromDate and t.ToDate OR @ToDate between t.FromDate and t.ToDate)
The query returns the records if the work allocated to the user 100.
Either one of the date lies in the record already present then the above query works. if you want to check both the dates in the range then change OR to AND.
Eg:
Existing Record : 100, 04/02/2009 04/30/2009
Inputs :
From : 04/10/2009(true) To: 04/20/2009(true) Returns record
From : 04/15/2009(true) To: 05/01/2009 Returns record
From : 03/10/2009 To: 04/20/2009(true) Returns record
FROM : 03/10/2009 To: 05/01/2009 Returns no record
SELECT * FROM Table t WHERE Userid = 100
AND ( @FromDate between t.FromDate and t.ToDate OR @ToDate between t.FromDate and t.ToDate
OR (@FromDate <= t.FromDate AND @ToDate >=t.ToDate)
)
FROM : 03/10/2009 To: 05/01/2009 Returns record
February 20, 2009 at 12:16 am
thank you very much for the detailed explanation
i got it
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply