I am working on a query from our ticketing system, and I am having problems figuring out the best way to join these tables together.
I have the following tables/fields:
SR_service: stores ticket data SR_Service_RecID SR_Status_RecID Last_Update Date_Req company: defines a company Company_RecID Company_Name SR_type: stores ticket type SR_Type_RecID Description SR_Status: stores ticket status SR_Status_RecID Description Schedule: the tickets assigned users, and date/time for work* RecID (This is the ticketID) Member_ID Date_Time_Start Date_Time_End
*The SCHEDULE table also has a Schedule_Type_RecID, and we are pulling for type '4'. If we join this table with SR_Service, only schedules of type '4' are allowed to be related to tickets in the system.
The first sub-query is for the type of ticket I am looking for. The type of ticket is dependent on what 'board' the ticket belongs to. Leaving out @Board will give me the type of ticket I'm looking for, across all boards.
The second sub-query is making sure I only get tickets that are in NOT closed status. Again, the closed SR_Status_RecID is dependent on the board. I return the closed status of all boards here.
I am at a loss how to get the ticket, company info, schedule info, and ticket status all in one query. The following is the list of what I need: Ticket_ID, status, assigned resources, company name, last update, and required date.
So far, I have come up with:
SELECT s.SR_Service_RecID, s.SR_Status_RecID, c.Company_Name, s.Last_Update, s.Date_Req FROM SR_Service s INNER JOIN Company c ON s.Company_RecID = c.Company_RecID WHERE SR_Type_RecID IN (SELECT t.SR_Type_RecID FROM SR_Type t INNER JOIN SR_Board b ON t.SR_Board_RecID = b.SR_Board_RecID WHERE t.description LIKE @Type AND b.Board_Name LIKE @Board) AND s.SR_Status_RecID IN (SELECT SR_Status_RecID FROM SR_Status WHERE Closed_Flag = 0)
This will get me a good amount of data, but there are a few problems:
- I only get the status_ID, not the actual description of the status.
- I am not quite sure how to add the Schedule table into the query to determine when a person is schedule out to perform the ticket tasks.