Hi,I thought this would be simple, but I can't quite figure it out (I'm sure it is simple for someone a little more talented than me in this area)....Basically I'm trying to run a query for multiple days. I first want to group the query by day. Then, for each day, I want to extract four 'call types [db field]', and create 1 row for each call type where the 'LocalQ [db field]' time is the largest for that call type for that day....Sample output (that I'd like to see)...Aug 6, 2007calltype1 600calltype2 660calltype3 300calltype4 250Aug 7, 2007calltype1 350calltype2 300...Here is the query I currently have...select (TCD.LocalQTime), substring(convert (varchar(20),TCD.DateTime),1,11), CT.CallTypeID from Termination_Call_Detail TCDjoin Call_Type CT on TCD.CallTypeID = CT.CallTypeIDwhere DateTime > '08/03/2007'and DateTime < '08/06/2007'and CT.CallTypeID in (5017,5018,5019,5020)group by substring(convert (varchar(20),TCD.DateTime),1,11), TCD.LocalQTime, CT.CallTypeIDorder by substring(convert (varchar(20),TCD.DateTime),1,11),CT.CallTypeID, TCD.LocalQTime desc
This sort of gives me what I want....289 Aug 4 2007 501829 Aug 4 2007 50180 Aug 4 2007 5018575 Aug 4 2007 5019533 Aug 4 2007 5019312 Aug 4 2007 5019280 Aug 4 2007 5019222 Aug 4 2007 501931 Aug 4 2007 50190 Aug 4 2007 50190 Aug 5 2007 5018173 Aug 5 2007 501937 Aug 5 2007 50193 Aug 5 2007 50190 Aug 5 2007 50190 Aug 5 2007 5020but it gives me all the records and I only want the largest record for each call type.Any help would be appreciated.Thanx,Ryan.
query woe's
Posted - 2007-08-06 : 15:34:47
