November 14, 2007 at 7:29 am
Hi
I am trying to get some information from an incident system. Every time an incident is edited the activity is saved in a table. This table includes an ID, Incident ID, time. The number of times an incident is edited is different from incident to incident.
Now I need to find out how long time we use on the first edit. I have done this by making a query finding the incident ID and grouping it with the minimum Edit ID. So I have a query with 2 columns and I want to use one column as a sub query. How do I do that?
The query I use to find the Edit ID is as follows:
SELECT jobs.job_id
FROM jobs, person, dept, customers
WHERE jobs.customer_Id = person.id AND person.dept_id = dept.id AND dept.cust_id = customers.Customer_Id AND
customers.Customer_Id = 47 AND jobs.logged_at <= '2007-01-01'
GROUP BY jobs.job_id
Kind regards
Bo
November 14, 2007 at 9:09 am
I think we need more information on this one. I'm not sure I understand what you mean by using 1 column from the query as a sub query? You can write a query like this:
SELECT Id,
(SELECT Column1 FROM Table2 WHERE Table2.Id = Table1.Id)
FROM Table1
Assuming I understood correctly and you want the time between the first edit and the last edit (although I'm making a leap there I know) I would do something like this:
SELECT IncidentId, DateDiff(hh,MIN(EditDate), MAX(EditDate))
FROM Table
GROUP BY IncidentId
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 19, 2007 at 2:51 am
Let me try to make it more clear what I need by an example.
The tabel has the following columns
ID, Incident ID and Time
Incident One
1 1 0,15
2 1 0,23
3 1 0,01
Incident two
4 2 0,10
5 2 0,25
Then I want to find the average time for the initial contact. That means I need the average time for ID = 1 and ID = 4.
I am trying to find this by making a subquery, that finds the ID of the first contact and then I am trying to use this in a query that calculates the time.
My subquery looks like this:
SELECT jobs.job_id, Min(opactivity.id) As 'OptID'
FROM opactivity, jobs, person, dept, customers
WHERE opactivity.jobid = jobs.job_id AND jobs.customer_Id = person.id AND person.dept_id = dept.id AND dept.cust_id = customers.Customer_Id AND
customers.Customer_Id = 47 AND jobs.logged_at <= '2007-01-01'
GROUP BY jobs.job_id
ORDER BY jobs.job_id
This Subquery finds the right ID's. However I don't know how I can use this in the top query because the query returns two rows instead of one.
Kind regards
Bo
November 19, 2007 at 1:50 pm
Simple enough. Just add a reference from your outer query into your subquery. For example:
SELECT OuterQuery.*
FROM OuterQuery
WHERE EXISTS
(
SELECT jobs.job_id, Min(opactivity.id) As 'OptID'
FROM opactivity, jobs, person, dept, customers
WHERE opactivity.jobid = jobs.job_id
AND jobs.customer_Id = person.id
AND person.dept_id = dept.id
AND dept.cust_id = customers.Customer_Id
AND customers.Customer_Id = 47
AND jobs.logged_at <= '2007-01-01'
-- Here is the bit that links the outer query with the inner
AND OuterQuery.Job_Id = jobs.Job_Id
GROUP BY jobs.job_id
-- Here is another bit that links the outer query with the inner
HAVING OuterQuery.OptId = MIN(opactivity.id)
-- You don't need the order by in the subquery
-- ORDER BY jobs.job_id
)
Now you could also do it like this if you need to use the data from the inner query for something:
SELECT OuterQuery.*
FROM OuterQuery
JOIN (
SELECT jobs.job_id, Min(opactivity.id) As 'OptID'
FROM opactivity, jobs, person, dept, customers
WHERE opactivity.jobid = jobs.job_id
AND jobs.customer_Id = person.id
AND person.dept_id = dept.id
AND dept.cust_id = customers.Customer_Id
AND customers.Customer_Id = 47
AND jobs.logged_at <= '2007-01-01'
GROUP BY jobs.job_id
-- You don't need the order by in the subquery
-- ORDER BY jobs.job_id
) InnerQuery
ON
-- Here is the bit that links the outer query with the inner
OuterQuery.Job_Id = InnerQuery.Job_Id
AND OuterQuery.OptId = InnerQuery.OptId
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 20, 2007 at 1:58 am
Thank you it worked 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply