April 23, 2013 at 8:04 am
Hi i hope someone can help mw out
I have the following selct statement that is working fine
Select Substring(Name,9,10) 'Desk'
,Convert(Int,BchCode) As 'bchCode'
,CallDate
,Max(CallTime) 'LastCall'
,Min(CallTime) 'FirstCall'
,Count(SummaryFlag) 'Outbound Attempts'
from tbl_OutboundCallData
Where CallDate between @startdate and @enddate And SummaryFlag In ('W')
Group By bchCode,CallDate,Name
I have been asked to add in the duration off the Last call (exists in tbl_OutboundCallData and call CallDuration) and im struggling if i just add in the duration then i have to add is into the group by element and this is returning all the records of duration and not just the one related to the Last Call
Hope that makes sense
any help would be gratefully appreciated as im going mad trying to get this orted
Thanks
April 23, 2013 at 8:39 am
Can it be as simple as MAX(WhateverTheNewColumnIS)?
The problem is that is nearly impossible to provide much assistance because we can't see what you see and we don't know your data structures. If you need further and more detailed assistance please take a few minutes and read the article at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2013 at 9:03 am
Think I see what you're after. Untested, since you haven't provided data, but you need a separate sub-query to pick out just the latest call for each group. There are a few ways to do this (Cross Apply + Top 1, Two-Stage Join with a MAX or the analytical function Row_Number()). An example of the latter is below:
SELECT SUBSTRING(Name, 9, 10) 'Desk' ,
CONVERT(INT, BchCode) AS 'bchCode' ,
CallDate ,
MAX(CallTime) 'LastCall' ,
MIN(CallTime) 'FirstCall' ,
COUNT(SummaryFlag) 'Outbound Attempts',
o1.Duration 'Last Call Duration'
FROM tbl_OutboundCallData o
INNER JOIN ( SELECT bchCode ,
CallDate ,
NAME ,
Duration
ROW_NUMBER() OVER ( PARTITION BY bchCode, CallDate,
NAME ORDER BY calltime DESC ) rn --rank everything by calltime within its group
FROM tbl_OutboundCallData
WHERE CallDate BETWEEN @startdate AND @enddate
AND SummaryFlag IN ( 'W' )
) o1 ON o1.bchCode = o.bchCode
AND o1.CallDate = o.CallDate
AND o1.NAME = o.NAME
AND rn=1 --only pick the latest row for each group
WHERE CallDate BETWEEN @startdate AND @enddate
AND SummaryFlag IN ( 'W' )
GROUP BY bchCode ,
CallDate ,
NAME,
o1.Duration
Check BOL out for more info on what this is doing under the hood:
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply