Hey guys I thought I had this issue sorted but turns out I dont. Ok the issue is when I run the below query in management studio it runs in 4 seconds but when I run the report in reporting services it just hangs. I reall can't get to the bottom of this. Here's the query
DECLARE @FromDate smalldatetime = '01-Oct-2010'
DECLARE @ToDate smalldatetime = '30-Nov-2012'
Declare @CarrierId smallint = 0
Declare @CarrierClassId smallint = 0
SELECT dg.DestinationGroupName AS Destination, c.CarrierShortName AS Carrier,
SUBSTRING(CONVERT(VARCHAR(11), ba.CDRDate, 106), 4, LEN(CONVERT(VARCHAR(11),ba.CDRDate, 106))) AS Month,
CASE WHEN DatePart(Month, ba.CDRDate) = DatePart(Month, getdate()) and DatePart(year, ba.CDRDate) = DatePart(year, getdate())
THEN ((SUM(ba.TotalDuration)/ 60) / DatePart(day, GetDate())) * DAY(DATEADD(mm, DATEDIFF(mm, -1, GetDate()), -1))
ELSE SUM(ba.TotalDuration) / 60 END AS DurationInMinutes,
CASE WHEN DatePart(Month, ba.CDRDate) = DatePart(Month, getdate()) and DatePart(year, ba.CDRDate) = DatePart(year, getdate())
THEN (SUM(ba.TotalCost) / DatePart(day, GetDate())) * DAY(DATEADD(mm, DATEDIFF(mm, -1, GetDate()), -1))
ELSE SUM(ba.TotalCost) END AS TotalRevenue,
CASE WHEN DatePart(Month, ba.CDRDate) = DatePart(Month, getdate()) and DatePart(year, ba.CDRDate) = DatePart(year, getdate())
THEN (SUM(av.AvgCost * ba.TotalDuration) / DatePart(day, GetDate())) * DAY(DATEADD(mm, DATEDIFF(mm, -1, GetDate()), -1))
ELSE SUM(av.AvgCost * ba.TotalDuration) END AS TotalCost,
SUM(ba.TotalCost) - SUM(av.AvgCost * ba.TotalDuration) AS Margin
FROM mvwBillingAggregateCurrencyConverted AS ba INNER JOIN
Destination AS d WITH (nolock) ON d.DestinationId = ba.DestinationId INNER JOIN
DestinationBand AS db WITH (NoLock) ON d.DestinationBandId = db.DestinationBandId INNER JOIN
DestinationGroup AS dg WITH (nolock) ON dg.DestinationGroupId = db.DestinationGroupId INNER JOIN
Carrier AS c ON c.CarrierId = ba.CarrierId LEFT OUTER JOIN
(SELECT dg2.DestinationGroupId, SUM(ba2.TotalCost) / SUM(ba2.TotalDuration) AS AvgCost
FROM mvwBillingAggregateCurrencyConverted AS ba2 INNER JOIN
Destination AS d2 WITH (nolock) ON d2.DestinationId = ba2.DestinationId INNER JOIN
DestinationBand AS db2 WITH (NoLock) ON d2.DestinationBandId = db2.DestinationBandId INNER JOIN
DestinationGroup AS dg2 WITH (nolock) ON dg2.DestinationGroupId = db2.DestinationGroupId INNER JOIN
Carrier AS c2 ON c2.CarrierId = ba2.CarrierId
WHERE (ba2.CDRDate BETWEEN @FromDate AND @ToDate) AND (ba2.BillRecFlag <> 0) and dg2.DestinationGroupId in(1,4,5,6,7)
GROUP BY dg2.DestinationGroupId) AS av ON
dg.DestinationGroupId = av.DestinationGroupId
WHERE (ba.CDRDate BETWEEN @FromDate AND @ToDate) AND (ba.BillRecFlag = 0) AND (@CarrierId = 0 or ba.CarrierId = @CarrierId)
AND (@CarrierClassId = 0 or ba.CarrierId in (Select c.CarrierId From dbo.Carrier c with (nolock) where c.CarrierClassId = @CarrierClassId)) and dg.DestinationGroupId in(1,4,5,6,7)
GROUP BY dg.DestinationGroupName, c.CarrierShortName, SUBSTRING(CONVERT(VARCHAR(11), ba.CDRDate, 106), 4, LEN(CONVERT(VARCHAR(11), ba.CDRDate, 106))), ba.CDRDate
ORDER BY Year(ba.CDRDate) ,Month(ba.CDRDate)
Maybe someone could tell me how to tidy up the query maybe to get it to run faster because im at a loss.
Thanks for the help guys.