Announcement

Collapse
No announcement yet.

SQL Query, complete brain freeze ?!?!

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Query, complete brain freeze ?!?!

    I've been staring at this all morning now and I'm no further on with it.
    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...

    I have 3 tables with data in :

    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 teacher assigned T_ID stored in here to link booking to 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 but 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
    Last edited by QuattroDave; 5th February 2014, 15:44. Reason: extra detail to clear up possable misunderstanding...

  • #2
    Re: SQL Query, complete brain freeze ?!?!

    Can you give some dummy data - a couple of records per table, perhaps one to show a result you would want and another one you dont?

    Just away out but will look later tonight or tomorrow

    With start/end dates and avail_date - are you wanting all 3 to be the same date, or avail_date to be between the start and end dates?
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Re: SQL Query, complete brain freeze ?!?!

      Thanks for your reply, sure thing, not sure how you want it but here goes...


      tblTeachers:
      T_ID (pri key)
      1001
      T_Name
      John Doe
      T_Address
      1 The Crescent
      T_Phone
      01234 567891
      T_JobGroup
      Secondary Teacher
      T_Subject
      Maths
      T_Status
      Live

      T_ID (pri key)
      1002
      T_Name
      Jane Doe
      T_Address
      1 High Street
      T_Phone
      09876 543210
      T_JobGroup
      Secondary Teacher
      T_Subject
      Maths
      T_Status
      Live
      ****************************

      tblBookings:
      B_ID (pri key)
      1101
      B_StartDate
      25/02/2014
      B_EndDate
      25/02/2014
      B_Subject
      Maths
      B_JobGroup
      Secondary Teacher
      B_TID
      NULL

      B_ID (pri key)
      1102
      B_StartDate
      05/03/2015
      B_EndDate
      10/03/2014
      B_Subject
      Maths
      B_JobGroup
      Secondary Teacher
      B_TID
      NULL

      ***************************
      tblAvailability:
      A_ID (pri key)
      1201
      A_TID
      1001
      A_AvailDate
      25/02/2014
      A_Colour
      Green

      A_ID (pri key)
      1202
      A_TID
      1002
      A_AvailDate
      07/03/2014
      A_Colour
      Red

      The result for booking 1101 should be:
      1001, John Doe, Green
      1002, Jane Doe, NULL (no colour)

      The result for booking 1102 should be:
      1001, John Doe, NULL (no colour)
      1002, Jane Doe, Red

      Its a bit awkward to show with 2 entries on the availability table... ah the availability table is created from here :
      http://forums.petri.com/showthread.php?t=65295
      Hope that makes a bit more sense...

      Thanks

      Dave
      Last edited by QuattroDave; 5th February 2014, 22:34. Reason: Layout adjustment

      Comment


      • #4
        Re: SQL Query, complete brain freeze ?!?!

        Hi Dave
        OK, rough thought is:
        SELECT T_ID, T_Name, A_Colour
        FROM tblTeachers LEFT JOIN tblAvailibility ON T_ID = A_TID
        JOIN tblBookings ON T_ID = B_TID
        WHERE T_Status= 'LIVE'
        AND A_AvailDate BETWEEN B_StartDate AND B_EndDate

        The LEFT JOIN should include all rows from the teacher table with nulls if there are not matching record in other tables

        Let me know how you get on
        Tom Jones
        MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
        PhD, MSc, FIAP, MIITT
        IT Trainer / Consultant
        Ossian Ltd
        Scotland

        ** Remember to give credit where credit is due and leave reputation points where appropriate **

        Comment


        • #5
          Re: SQL Query, complete brain freeze ?!?!

          Hiya,

          Thanks for your reply, the query executes but unfortunately gives the wrong information... Looking at it I think my explanation wasn't very good. Let me try again

          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 they're availability status (colour) for the 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...

          Apologies for the sudo code, it was the only way I could think it through....

          Thanks

          Dave

          EDIT:
          This query now has all the tables added, I think i just need to work out how to compare the booking dates & availability dates...

          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'
          Last edited by QuattroDave; 7th February 2014, 14:53. Reason: added working sql statement

          Comment

          Working...
          X