CODEWITHSUNDEEP
sql-servert-sql
Napstar
Napstar

Reputation: 363

Procedure to get dates in between

I have a SQL Server database that contains a timesheet table. This table is used to store roles for employees. Before I perform an insert I check to see of an employee has any preexisting roles in the selected days.

Here’s a stored procedure that returns the count of pre-existing roles 

set @retVal=(select count(fk_RoleID) from dbo.TimesheetTable where 
(@startdate >=  CAST((
STR( YEAR( StartDate ) ) + '/' +
STR( MONTH( StartDate ) ) + '/' +
STR( DAY( StartDate ) )
)
AS DATE
))-- AND EndDate <= '2012-08-30')
and
(@enddate  <  CAST(
(
STR( YEAR( EndDate ) ) + '/' +
STR( MONTH( EndDate ) ) + '/' +
STR( DAY( EndDate ) )
)
AS DATE
))
and fk_PersonnelID=@personnelID)

return @retVal

The following are the records for one employee..

pk_ID  fk_PersonnelID  fk_RoleID  StartDate   EndDate     dateCreated
62     1               26         2012-10-01  2012-10-02  2012-10-25 15:55:12.940
81     1               20         2012-10-04  2012-10-06  2012-10-30 14:50:28.300

If I try to do an insert of where the start date is 2012-10-05 and end date is 2012-10-11, the query fails to trap the startdate ..and the insert occurs

What am I doing wrong?

Upvotes: 4

Views: 532

Answers (3)

Adam Wenger
Adam Wenger

Reputation: 17540

You shouldn't need to parse the StartDate and EndDate columns to get the results desired.

SELECT @retVal = COUNT(fk_RoleID)
FROM dbo.TimesheetTable AS tt
WHERE @startDate <= tt.EndDate
   AND @endDate >= tt.StartDate
   AND fk_PersonnelId = @personnelID;

return @retVal;

@retVal here would return a number greater than 0 for inputs that already have a schedule in the database.

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

You overlap test is incorrect. The test should be "both starts cometh before the opposite ends". Remember it, it's easy.

SELECT @retVal = CASE WHEN EXISTS (
    select *
    from dbo.TimesheetTable
    where StartDate <= @EndDate
      and @StartDate <= EndDate
      and fk_PersonnelID=@personnelID) THEN 1 ELSE 0 END

To test for existence, switch to using EXISTS which will short-circuit and give you a result as soon as a result is found instead of COUNTing all matches.

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89651

You have a logical error in your date comparison inequalities because the date interval ranges I think you are attempting to match are overlapping - not properly contained - as your code seems to assume.

i.e. 10/4 - 10/6 strictly overlaps 10/5- 10/11 while your code implies that the parameter interval has to lie completely within the data row s interval.

Upvotes: 0

Related Questions