Thanks Seth.

Phil.

Hello again. I have done a sanity check on the output when averaging the cost and the figures do not quite add up.

I averaged the costs of the last 5...

Thanks very much for your efforts, great work.

I amended to return average for the other costs rather than SUM.

Many Thanks,

Phil.

I thought I would break the problem down and now have the following:

SELECT DISTINCT ProductId,OrderId,

ELEMENT1=

CASE

WHEN ELEMENT1 = 100 THEN 'MAN'

...

I have the following basic query that gives an average build cost without the summation of the individual elements.

SELECT DISTINCT COUNT(ProductId) AS ProductCount,ProductId, AVG(TotalCost) AS 'AVGBuildCost'

FROM dbo.Cost

WHERE TotalCost >'0'

GROUP...

I have successfully imported 164000 rows from .xlsx file (Office 2007) into SQL 2005 database (SP2).

The full article I followed can be read by clicking the link provided below:

http://dougbert.com/blogs/dougbert/archive/2008/06/16/excel-in-integration-services-part-1-of-3-connections-and-components.aspx

The...

Great work and many thanks. I rolled with Vladan example code (last post), worked a treat.

Once again many thanks to all those who posted, I really appreciate your time and...

Hi and thanks for posting. Yes Part Count is to capture how many times the Part has been ordered. I will implement your code and post back (table definition, sample...

Thanks for replying guys.

Seth I tried modifying your code, just working through the errors! 🙂

Steve I have not had much exposure to using HAVING + TOP so will have read...

Would be happy to. I will give it a go at the earliest opportunity and post back.

Kind Regards,

Phil.

To follow on. I have since remigrated the data. I cleaned up in excel first (reduced to 164000 records). I found that the TotalCost column had a - symbol (negative)...

I needed to make a slight modification (add GROUP BY) so code now reads as follows:

UPDATE p

SET p.MaterialCost = x.LabourCost

FROM dbo.Products As p

JOIN (

SELECT DISTINCT COUNT(ProductId)...

Thanks guys, my knowledge is improving but still lots to learn.

Phil.

Hi. I managed to resolve the error SQL returned on CONVERT.

I used WHERE ISNUMERIC(TotalCost) <> 1

I deleted - from end of value and then ran:

SELECT ProductId, AVG(CONVERT(money,TotalCost)) AS 'Total'

FROM...

I have managed to format the month:

="TOTAL FOR MONTH:" & MonthName(MONTH(First(Fields!Date.Value, "dsBidReport")))

Regards,

Phil.