Remove time slot if all employees are busy (assigned to a appointment)

Question

0.00/5 (No votes)

See more:

I have a calendar, which the customers will use to choose a date, when the date is chosen a popup appears with a dropdown menu with the working hours, if all employees are busy at the particular time I want it to be removed from the drop down menu

this is what I have so far

SQL

( select   WorkingHours from WorkingHours
  except
(select  StartTime from Assign_Staff,Schedule1 
 where Assign_Staff.ScheduleId=Schedule1.ScheduleID and
  exists (select ScheduleID,StartTime from Schedule1 where Date='2016-08-16'))
  ) ( select distinct StaffID, WorkingHours from Staff,WorkingHours
  except
(select  StaffID,StartTime from Assign_Staff,Schedule1 
 where Assign_Staff.ScheduleId=Schedule1.ScheduleID and
  exists (select ScheduleID,StartTime from Schedule1 where Date='2016-08-16'))
  )

my tables: schedule table contains (ID, date, start_time,end_time,Customerid) appointment_Staff (id, ScheduleID, staffID,AdminID) working_Hours (id,start_time,end_time)

What I have tried:

I can't figure it out

Posted 17-Aug-16 23:42pm

Member 12669478

Updated 18-Aug-16 17:12pm

v5

Add a Solution

Comments

Maciej Los 18-Aug-16 7:00am

Well... due to the type of objects used to store data, a solution may differ. Is it: Linq To SQL, Linq To Dataset, other ?

Member 12669478 18-Aug-16 7:18am

linq to sql

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2016-08-18T02:41:00

Few notes:

Wrong approach! This set of subqueries may cause efficiency issues...

SQL

(select  StartTime from Assign_Staff,Schedule1 
 where Assign_Staff.ScheduleId=Schedule1.ScheduleID and
  exists (select ScheduleID,StartTime from Schedule1 where Date='2016-08-16'))

This should be converted into single query which uses join and where clause:

SQL

SELECT StartTime
FROM Assign_Staff As sta INNER JOIN Schedule1 AS sche ON sta.ScheduleId=sche.ScheduleID 
WHERE sche.Date='2016-08-16'

C#

var result = from sta in Assign_Staff
        join sche in Schedule1.Where(s=>s.Date==new DateTime(2016,8,16)) on sta.ScheduleID equals sche.ScheduleID
        select sche.StartTime

Use the same logic to improve second query!

C#

var second = (from asta in Assign_Staff
from whr in WorkingHours
select new {asta.StaffID, whr.WorkingHours}).Except(
    from sta in Assign_Staff
    join sche in Schedule1.Where(s=>s.Date==new DateTime(2016,8,16)) on sta.ScheduleID equals sche.ScheduleID
        select new {sta.StaffID, sche.StartTime})

Do not expect that someone will do the job for you!
I'd suggest to start with basics:
LINQ to SQL[^]
LINQ to SQL: .NET Language-Integrated Query for Relational Data[^]
101 LINQ Samples in C#[^]
When you finish reading and you take a try to write proper queries and you get stuck, come back here and post another question. But you should be warned: you have to explain what you have done and why your query doesn't work.
There's one tool, which may help you to convert sql query into linq. It's called Linqer[^]. You can download and use trial version, but you have to keep in mind my first note.

leol2 · Answer 2 · 2016-08-18T05:40:00

Solution 2

how to convert linq query to sql - Stack Overflow[^]

Posted 18-Aug-16 5:40am

leol2

Comments

Maciej Los 18-Aug-16 11:49am

Wrong answer! OP wants to convert T-SQL query into Linq query.
Use Improve solution widget to improve answer or delete it to avoid down-voting.