Need help building a query
-
Need help building a query that returns summary data from a database. There should be only one summary row for each method as I traverse the table.
Here's the query I currently have:
select distinct method as [Method], application as [Application], mode as [Mode], computerName as [Computer Name], datetimestarted as [Date/Time EventStarted],
min(Duration) as [Minimum Duration], max(duration) as [Maximum Duration], avg(duration) as [Average Duration]
from events
group by application, method, computername, mode, datetimestarted order by application
However, I'm not getting a distinct method, I'm getting duplicate entries returned. Would someone please suggest a better way of doing this?
Thank you,
Jennifer
-
The datetimestarted seems to be of the DateTime type, nearest 3/1000 of a second, do you want one row per day/month/year
Replace datetimestarted with Convert(Char(11),datetimestarted,130) and see if it the result set is closer to what you expect
Remove the distinct part.
-
OK, I found out how to not get duplicate methods, but now I have another issue.
Here's the current query syntax:
select Number, Method, Application, Computer, MinSubDuration, MaxSubDuration, AvgSubDuration, SubEventCount, s.section as [MaxSection]
from
(select e.eventid, e.method, e.application, e.computername, MinSubDuration, MaxSubDuration, AvgSubDuration, SubEventCount
from Events e left join
(select eventid, min(duration), max(duration), avg(duration), count(eventid) from subevents
where datetimestarted >= '2003-09-30 06:00' and datetimestarted <= '2003-09-30 18:00' group by eventid)
as DSubevents (EventID, MinSubDuration, MaxSubDuration, AvgSubDuration, SubEventCount) on e.eventid = DSubevents.eventid
where datetimestarted >= '2003-09-30 06:00' and datetimestarted <= '2003-09-30 18:00')
as OuterDSumEvents (Number, Method, Application, Computer, MinSubDuration, MaxSubDuration, AvgSubDuration, SubEventCount)
left join Subevents s on (OuterDSumEvents.Number = s.EventID and OuterDSumEvents.MaxSubDuration = s.duration)
where datetimestarted >= '2003-09-30 06:00' and datetimestarted <= '2003-09-30 18:00' and s.section not like 'Creating Connection'
order by s.EventID
I need to add a column called MinSection to the table which will contain the name of the section that has the smallest execution duration. There will never be duplicate max values, but there are several cases where the min values will be the same and I just want 1 of them. I tried doing another left join and was still getting duplicates. Any suggestions? I'm probably making this harder than it should be ....
Jennifer
-
Not the best/efficient solution but how about adding another column to the top/outer select ,(Select top 1 Sectioname from Table t1 where t1.duration=Min(Duration) and t1.col1=event.id) as MinDuration
Not the complete statement but should give you the general idea.
-
Thank you 5409045121009. I'll try that and post the results.
Jennifer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply