I will dsend the data first thing tomorrow morning. I have just left the office.

At the moment the SSRS and SSAS are not available. I agree that would be the better option but at the moment it is a no no. That will come...

I was just wondering if there was another way of getting the same output with a different query. I have tried to look up multi aggregate pivots on google but...

Here is the new execution plan with the temp table.

I used a temp table but the query took exactly the same time.

I am not really sure on how I to create PIVOT with multiple aggregates. Could you give me...

thanks. that produced my output.

Apologies I just forgot to paste it in. The code is as follows

CREATE TABLE #TradeDetail

(

...

Hi sorry for the late reply. got pulled into something else. i actually got round to doing what you proposed and it worked. Many thanks. the dates issues have been...

Sorry folks I get it now. Although I was seeing/reading dateadd in the quesry for some reason I was thinking 'datepart' which is what was confusing me. The...

oh ok thanks. and the dateadd function. what is it doing?

Managed to solve it with

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@MyToMonth))

THEN TotalGross END) AS PreviousYear1Turnover,

Thanks once again for the initial help

Thanks for the solution. That worked. I just wanted know what i need to do to get the total orders for a year from todays today date. i.e the sum...

hey,

much appreciated. i will workd this and see how it goes. It actaully meant to be a row count for trade orders.

Hey John,

That works fine and is certainly a lot easier to read/decipher. The date is varchar form the original source but kept your conversion in to cover for those mistakes....

My Apologies. I was counting the rows instead of summing them in the excel pivot. My Bad:blush: