Query from Hell
-
After getting much help from Michael and Andy I managed to tweak this thing to meet the requirements....However, I need to group by an addition column..since I have never done it this way I have floundered for a few days before asking for help with this. I can make it work in a different query just not this one. I need to group by an addditonal field named: problem_type_name in the Problem_type table which is tied to the job_ticket.problem_type_id This worked in the other query I used and then added in the "group by" INNER JOIN problem_type p ON (p.problem_type_id = j.problem_type_id) Here is the working query that I cant add p.problem_type_name to "group by" I have the inner join there and it will execute fine....I just need to know how to get it into the group by with location_name...I have been trying with no success... Thanks to all who have helped thus far; I am learning and hopefully I will be able to contribute to the forum @ some point. SELECT Total , [Total Remain Open], (SELECT COUNT(*) FROM dbo.job_ticket WHERE location_id = Source.location_id AND report_date between @startdate and @enddate) AS [Total Opened], (SELECT COUNT(*) FROM dbo.job_ticket WHERE location_id = Source.location_id AND status_type_id like '[3,4,5]' AND report_date between @startdate and @enddate) AS [Total Closed], Client, [Average days open] FROM (SELECT l.location_id, l.location_name AS 'Client', COUNT(*) AS 'Total', SUM(CASE WHEN status_type_id LIKE '[1,2,6,7]' AND report_date between @startdate and @enddate THEN 1 ELSE 0 END) AS 'Total Remain Open', AVG(datediff(d, report_date, close_date)) AS [Average Days Open] FROM job_ticket j INNER JOIN location l ON (l.location_id = j.location_id) INNER JOIN problem_type p ON (p.problem_type_id = j.problem_type_id) WHERE report_date BETWEEN @startdate AND @enddate GROUP BY l.location_id, l.location_name) AS Source
Regards, Hulicat