Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
The time span that should be searched for missing guys.. for what day is available (current day, doesn't matter...)? –  Marian Jan 21 '11 at 13:31
    
I really think this would be solved faster if you would provide us a DDL and some basic data (hint: INSERT INTO) and some expected results –  jcolebrand Jan 24 '11 at 22:09
    
I Solved my own question with a simple solution. I would post a neat pictured solution later when i am at home. thanks to everyone who viewed this –  Deeptechtons Jan 25 '11 at 10:39
3  
And the solution is...? :-) –  Marian Feb 2 '11 at 19:52
2  
@Deeptechtons ~ Did you ever get a moment to post the solution that you came up with? I would love to see it. –  jcolebrand Feb 17 '11 at 0:19
show 1 more comment

2 Answers 2

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);
share|improve this answer
    
Oops sorry guys i was on touring glad that many had updated the post. Simply said, i would filter the time span if it is Am / Pm at the front end [Presentation Layer] which would deduce to 0,1 or 2. Then on the dataaccess layer i do this NOT_AVL_FOR <> @FrontendParameter. Hence i get the staff who are available for the time slot to be assigned work. This might not fit your exact needs but most of times , frontend could answer complex queries if otherwise –  Deeptechtons Feb 21 '11 at 12:01
    
Sounds like you are essentially doing the same thing my query does just doing it in the front end rather than the SQL. –  Leigh Riffel Feb 21 '11 at 12:46
1  
@Deeptechtons ~ So are you willing to accept this answer or would you prefer to post your own and mark it as accepted? –  jcolebrand Feb 21 '11 at 15:25
    
i marked my answer as the "Answer" thanks for anyone and everyone who posted their views here. –  Deeptechtons Mar 4 '11 at 9:12
add comment
up vote 1 down vote accepted

Oops sorry guys i was on touring glad that many had updated the post. Simply said, i would filter the time span if it is Am / Pm at the front end [Presentation Layer] which would deduce to 0,1 or 2. Then on the dataaccess layer i do this NOT_AVL_FOR <> @FrontendParameter. Hence i get the staff who are available for the time slot to be assigned work. This might not fit your exact needs but most of times , frontend could answer complex queries if otherwise

share|improve this answer
2  
Thanks for accepting an answer. I agree that "This might not fit your exact needs but most of time". –  Leigh Riffel Mar 4 '11 at 15:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.