SQL Query, complete brain freeze?!?!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Query, complete brain freeze?!?!

Author

Topic

QuattroDave
Starting Member

4 Posts

Posted - 2014-02-05 : 11:08:03

Hi All,

I've just joined the forum, I've been staring at this all morning now and I'm no further on with it so have decided to ask those with far more experience than I. I'll do my best with an explanation but please bare with me.....

Essentially what I want is to display the user with a list of available teachers but show the teachers in the respected availability type (colour) to help make the decision 'who do I contact first' a bit easier...

The query uses data within these 3 tables:

tblTeachers:
T_ID (pri key)
T_Name
T_Address
T_Phone
T_JobGroup
T_Subject
T_Status

tblBookings:
B_ID (pri key)
B_StartDate
B_EndDate
B_Subject
B_JobGroup
B_TID (Once a teacher is assigned to the booking, T_ID is stored here to link the booking to a teacher)

tblAvailability:
A_ID (pri key)
A_TID (teachers T_ID stored here to link teacher to availability)
A_AvailDate
A_Colour (different colours indicate different availability types)

Ok, what I'm trying to achieve is:-
return T_Name, T_ID & A_Colour
when T_Status='Live' and
when B_Startdate & B_Enddate = A_Availdate's

The problems are that
1. If the availability isn't set I still need to return T_Name, it just won't have any colour associated.
2. In the booking you have a startdate and an enddate but in the availability you have every day listed only if it has a colour associated with it...

Hope that all makes sense... I'm really confused about how to deal with this and am beginning to wonder if I am storing the data correctly to be able to query like this......

Any thoughts greatly appreciated....

Dave

Edit:
Just a thought, would a calendar table be a better way to go for availability?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-11 : 01:39:20

can you post how you want output to come?

you can use this as start if you want


SELECT *
FROM tblTeachers t
LEFT JOIN tblAvailability a
ON a.A_TID = t.T_ID
LEFT JOIN tblBookings b
ON b.B_TID = t.T_ID
WHERE t.T_Status='Live'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

QuattroDave
Starting Member

4 Posts

Posted - 2014-02-11 : 03:51:19

Morning thanks for your reply,

This query will run when a user selects a specific booking so it will supply a B_ID.

So for booking 'XXXX' i want to be able to retrieve a list of teachers.
The teachers T_Status must = 'Live'
The teachers T_JobGroup must = the booking B_JobGroup

SELECT Bookings.B_BID, Teachers.T_Name
FROM dbo.Bookings, dbo.Teachers
WHERE Bookings.B_BID = '1001'
AND Teachers.T_Status = 'Live'
AND Bookings.B_JobGroup = Teachers.T_JobGroup

Ok here is where it gets tricky....

For each teacher listed I want to know the most common availability status (colour) for the given date period specified in the booking. This info is contained in the availability table. I will then sort the teacher list by the availability status...

i.e. If the availability is :-
07/03/14 Green
08/03/14 Green
09/03/14 Green
10/03/14 Green
Then for booking 08/03/14 -> 10/03/14 and the specific teacher it should return 'Green'

But if availability is :-
07/03/14 Red
08/03/14 Red
09/03/14 Red
10/03/14 Green
Then for booking 08/03/14 -> 10/03/14 and the specific teacher it should return 'Red'

Now if we change the availability again:-
06/03/14 Red
07/03/14 Red

11/03/14 Green
12/03/14 Green
Then for booking 08/03/14 -> 10/03/14 and the specific teacher it should return 'NULL'

This query now has all the tables added, I just need to work out how to compare the booking dates & availability dates and get a majority...

SELECT Bookings.B_BID, Teachers.T_Name, Availability.A_Colour
FROM dbo.Bookings
INNER JOIN dbo.Teachers ON Bookings.B_JobGroup = Teachers.T_JobGroup
INNER JOIN dbo.Availability ON Teachers.T_TID = Availability.A_TID
WHERE Bookings.B_BID = '1001'
AND Teachers.T_Status = 'Live'

Thanks

Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources