2

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.

1 Answer 1

3

This will get you the Status Description:

SELECT s.SR_Service_RecID, SR_Status.Description, c.Company_Name, s.Last_Update, s.Date_Req
FROM SR_Service s 
    INNER JOIN Company c ON s.Company_RecID = c.Company_RecID
    INNER JOIN SR_Status ON s.ST_Status_RecID = SR_Status.SR_Status_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)

To add the schedule, you'd need to add a key field from the schedule table to the SR_Service table.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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