Sql-server – Get records between time span

datatypessql server

I would like to retrieve records between time spans,All i have is this snippet of string indicating the time span

9:00 AM - 10:00 AM

below is the table schema

CREATE TABLE [dbo].[Samples](
[RID] [int] IDENTITY(1,1) NOT NULL,
[PersonName[nvarchar](20) NOT NULL,
[NOT_AVL_DATE] [datetime] NULL,
[NOT_AVL_FOR] [int] NULL
)

brief:
Rid is Primary key, PersonName is Employee/Staff/Technician Name, NOT_AVL_Date is the datetime the staff wont be avilable, NOT_AVL_FOR represents time span the staff wont be avilable.
1 – Leave for Full day
2- Leave for First half of the day
3 – Leave for Second half of the day

Requirement:
Given timeSpan i want the Staff who are not on leave for that time span. Could someone construct a Full Statement , my head is whirling thinking about this..thanks for any help

Best Answer

Deeptechtons, we really would like to see your answer, but since you haven't posted it yet, here is how it could be done. This SQL is for Oracle not SQL Server, so it would have to be translated. There are details missing from your post, so some assumptions used are likely invalid.

with AllEmployees As 
   (select distinct PersonName from samples)
select * from AllEmployees 
minus
select PersonName from samples 
where trunc(not_avl_date) = trunc(sysdate) 
and (not_avl_for = 1
   or (not_avl_for = 2 and :timespan LIKE '%AM%')
   or (not_avl_for = 3 and :timespan LIKE '%PM%')
);

Sample data:

create table samples (rid Number(3), PersonName Varchar2(20), 
   not_avl_date date, not_avl_for integer);

insert into samples values (1,'Peter',sysdate-2, 1);
insert into samples values (2,'Peter',sysdate-1, 2);
insert into samples values (3,'Paul',sysdate-1, 3);
insert into samples values (4,'Sue',sysdate, 3);
insert into samples values (5,'Bob',sysdate-2, 2);
insert into samples values (6,'Zach',sysdate+1, 1);
Related Question