November 19, 2009 at 10:59 am
Hoping someone here can help me out with a problem I am having.
I am trying to create a query that merges data from 4 tables.
Table 1 Contains the CompanyID, and other Identifying information. This table is used as the primary for grabbing the companies to display.
Table 2 Contains all original Repair Ticket Data this is linked by the CompanyID
Table 3 Contains Modified Repair Ticket Data by Reporting Group A which can be compared to Table 2 or 4 by the Ticket Number
Table 4 Contains Modified Repair Ticket Data by Reporting Group B which can be compared to Table 2 or 3 by the Ticket Number.
The way I envision the query working is like this......
Query Table 1 and get list of CompIDs that are marked as active.
Get all Tickets from Table 2 that match a CompID from Table 1 and have been closed in month X.
Query Table 3 and overwrite HEALTH_TOTAL in the above results if TicketID = TIcketID
Query Table 4 and overwrite HEALTH_TOTAL in above results if TicketID = TicketID
Get Sum of HEALTH_TOTAL grouped by CompID
Now getting the HEALTH_TOTALS from just Table 2 was easy enough.
SELECT A.CompID, A.Company, A.Region, B.HEALTH_TOTAL, B.HEALTH_MTTR, B.HEALTH_OTR, B.HEALTH_REPEAT, B.HEALTH_CHRONIC
FROM TRT_parent_accounts A
LEFT JOIN
(SELECT CompID, SUM(HEALTH_TOTAL) As HEALTH_TOTAL, SUM(HEALTH_MTTR) AS HEALTH_MTTR, SUM(HEALTH_OTR) AS HEALTH_OTR, SUM(HEALTH_REPEAT) AS HEALTH_REPEAT, SUM(HEALTH_CHRONIC) AS HEALTH_CHRONIC
FROM TRT_remedy_tickets
WHERE (CAST(CAST(YEAR([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(4)) + '-' + Right('00' + CAST(MONTH([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(2)), 2) AS VARCHAR(7))) = '2009-10'
GROUP BY CompID) B
ON A.CompID = B.CompID
WHERE A.Status = 'active' AND (B.HEALTH_TOTAL > -1 OR B.HEALTH_TOTAL IS NULL)
I was even able to return only Table 2 results that were not found in either Table 3 or Table 4.
SELECT A.CompID, A.Company, A.Region, B.HEALTH_TOTAL, B.HEALTH_MTTR, B.HEALTH_OTR, B.HEALTH_REPEAT, B.HEALTH_CHRONIC
FROM TRT_parent_accounts A
LEFT JOIN
(SELECT CompID, SUM(HEALTH_TOTAL) As HEALTH_TOTAL, SUM(HEALTH_MTTR) AS HEALTH_MTTR, SUM(HEALTH_OTR) AS HEALTH_OTR, SUM(HEALTH_REPEAT) AS HEALTH_REPEAT, SUM(HEALTH_CHRONIC) AS HEALTH_CHRONIC
FROM TRT_remedy_tickets
WHERE (CAST(CAST(YEAR([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(4)) + '-' + Right('00' + CAST(MONTH([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(2)), 2) AS VARCHAR(7))) = '2009-10'
AND NOT EXISTS (SELECT MAIN_TICKET_ID FROM TRT_hip_reviews C WHERE C.MAIN_TICKET_ID = TRT_remedy_tickets.MAIN_TICKET_ID) AND NOT EXISTS (SELECT MAIN_TICKET_ID FROM TRT_pm_reviews D WHERE D.MAIN_TICKET_ID = TRT_remedy_tickets.MAIN_TICKET_ID)
GROUP BY CompID) B
ON A.CompID = B.CompID
WHERE A.Status = 'active' AND (B.HEALTH_TOTAL > -1 OR B.HEALTH_TOTAL IS NULL)
I just don't know how to pull off what I first listed... Is there any Join that will use data from the join to overwrite data from the top query?
November 19, 2009 at 11:14 am
I'm not sure I completely understand your rules, but you can calculate all the values and then use a CASE that will pull back the value you want in the result set.
CASE
WHEN condition1 THEN table3.Value
WHERE condition2 then table4.value
ELSE xx
end
November 19, 2009 at 12:40 pm
Steve,
Thanks. Ya half the time my trouble is articulating what I am looking for. It's all up in my head.... Just need to smash it out sometime.
I was able to come up with a functional, albeit ugly query that gives me the results I was after.
Below Is what I am using. I am defiantely open to alternatives since this will be used 3 times (different top level WHERE clause value) on 1 page.
Basically I am after the Sum of each of the following tables
HEALTH_TOTAL, HEALTH_MTTR, HEALTH_OTR, HEALTH_REPEAT and HEALTH_CHRONIC
Since the Score for an individual ticket from a PM is more important/reliable than a HIP and HIP is more reliable than the original ticket... What I do is grab all Tickets from the main table not found in the other 2. Then union that with tickets from the HIP table that are not found in the PM table. Then finally union that with tickets in the PM table.
Throw all that inside a Select Statement, group it by CompID, Company, and Region while getting the sum of the other values.
SELECT * FROM (
SELECT R.CompID, R.Company, R.Region, SUM(R.HEALTH_TOTAL) As HEALTH_TOTAL, SUM(R.HEALTH_MTTR) AS HEALTH_MTTR, SUM(R.HEALTH_OTR) AS HEALTH_OTR, SUM(R.HEALTH_REPEAT) AS HEALTH_REPEAT, SUM(R.HEALTH_CHRONIC) AS HEALTH_CHRONIC FROM (
SELECT A.CompID, A.Company, A.Region, B.HEALTH_TOTAL, B.HEALTH_MTTR, B.HEALTH_OTR, B.HEALTH_REPEAT, B.HEALTH_CHRONIC
FROM TRT_parent_accounts A
LEFT JOIN
(SELECT CompID, SUM(HEALTH_TOTAL) As HEALTH_TOTAL, SUM(HEALTH_MTTR) AS HEALTH_MTTR, SUM(HEALTH_OTR) AS HEALTH_OTR, SUM(HEALTH_REPEAT) AS HEALTH_REPEAT, SUM(HEALTH_CHRONIC) AS HEALTH_CHRONIC
FROM TRT_remedy_tickets
WHERE (CAST(CAST(YEAR([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(4)) + '-' + Right('00' + CAST(MONTH([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(2)), 2) AS VARCHAR(7))) = '2009-11'
AND NOT EXISTS (SELECT MAIN_TICKET_ID FROM TRT_hip_reviews C WHERE C.MAIN_TICKET_ID = TRT_remedy_tickets.MAIN_TICKET_ID) AND NOT EXISTS (SELECT MAIN_TICKET_ID FROM TRT_pm_reviews D WHERE D.MAIN_TICKET_ID = TRT_remedy_tickets.MAIN_TICKET_ID)
GROUP BY CompID) B
ON A.CompID = B.CompID
WHERE A.Status = 'active'
UNION
SELECT A.CompID, A.Company, A.Region, B.HEALTH_TOTAL, B.HEALTH_MTTR, B.HEALTH_OTR, B.HEALTH_REPEAT, B.HEALTH_CHRONIC
FROM TRT_parent_accounts A
LEFT JOIN
(SELECT B.CompID, SUM(A.HEALTH_TOTAL) As HEALTH_TOTAL, SUM(A.HEALTH_MTTR) AS HEALTH_MTTR, SUM(A.HEALTH_OTR) AS HEALTH_OTR, SUM(A.HEALTH_REPEAT) AS HEALTH_REPEAT, SUM(A.HEALTH_CHRONIC) AS HEALTH_CHRONIC
FROM TRT_hip_reviews A
INNER JOIN TRT_remedy_tickets B ON B.MAIN_TICKET_ID = A.MAIN_TICKET_ID
WHERE (CAST(CAST(YEAR(B.[DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(4)) + '-' + Right('00' + CAST(MONTH(B.[DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(2)), 2) AS VARCHAR(7))) = '2009-11'
AND NOT EXISTS (SELECT MAIN_TICKET_ID FROM TRT_pm_reviews D WHERE D.MAIN_TICKET_ID = A.MAIN_TICKET_ID)
GROUP BY B.CompID) B
ON A.CompID = B.CompID
WHERE A.Status = 'active'
UNION
SELECT A.CompID, A.Company, A.Region, B.HEALTH_TOTAL, B.HEALTH_MTTR, B.HEALTH_OTR, B.HEALTH_REPEAT, B.HEALTH_CHRONIC
FROM TRT_parent_accounts A
LEFT JOIN
(SELECT B.CompID, SUM(A.HEALTH_TOTAL) As HEALTH_TOTAL, SUM(A.HEALTH_MTTR) AS HEALTH_MTTR, SUM(A.HEALTH_OTR) AS HEALTH_OTR, SUM(A.HEALTH_REPEAT) AS HEALTH_REPEAT, SUM(A.HEALTH_CHRONIC) AS HEALTH_CHRONIC
FROM TRT_pm_reviews A
INNER JOIN TRT_remedy_tickets B ON B.MAIN_TICKET_ID = A.MAIN_TICKET_ID
WHERE (CAST(CAST(YEAR(B.[DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(4)) + '-' + Right('00' + CAST(MONTH(B.[DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(2)), 2) AS VARCHAR(7))) = '2009-11'
GROUP BY B.CompID) B
ON A.CompID = B.CompID
WHERE A.Status = 'active'
) As R
Group By R.CompID, R.Company, R.Region ) As tbl
WHERE tbl.HEALTH_TOTAL > -1 OR tbl.HEALTH_TOTAL IS NULL
Order By tbl.Company
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply