ok, here it is:

--the easier way: round the position number

CREATE FUNCTION [dbo].[fn_getperentile](@groupname char(20), @percentile int)

RETURNS money

AS

BEGIN

DECLARE @ret money

DECLARE @cnt bigint

...

I already figured it out. Thx!

Jim, you mean the folder path has \ at the end? yes I have.

I didn't see your .rtf attachement.

you can email to jzhou@majesticresearch.com.

Thanks a lot for your help and...

Thank you both for the help!!!

I changed the propery from connectionstring to ExcelFilePath and to use Retrieve file name: Name only in Foreach Loop Editor/Collection, but I still got...

looks like it doesn't work for Excel Connection Manager. Once I set the property of the Excel Connection Manager to use an expression of connectionstring of the variable, then immediately...

Thanks a lot!! I did this and put the data flow task inside this foreach loop container. when i execute this container i got error like:

The AcquireConnection method call to...

sorry I mean i didn't use the datediff function.

I didn't use dateadd function:

---------------------------------------------------------------------

SELECT wl.ItemId [Project ID], F._Features_NAME AS ProjectName, P.NAME as ProjectCategory, wlt.name as HourType, CONVERT(CHAR, wl.WorkLogDateTime, 101) [Date],

case when datename(dw, wl.WorkLogDateTime) = 'Monday' then wl.WorkLogDateTime

...

This is the revealing:

-------------------------------------------------------------------------------------------

Build complete -- 0 errors, 0 warnings

[rsRuntimeErrorInExpression] The Value expression for the textbox ‘textbox8’ contains an error: Operation is not valid due to the current state of...

In the query, the column is like this:

ROUND ( ( (wl.WorkDone * tut.ConversionFactor) / 60), 1) [Time Spent (Hours)]

so it is a numerical field.

Another way to do it in function which is better I think:

CREATE FUNCTION udf_bin_me (@IncomingNumber int)

RETURNS varchar(200)

as

BEGIN

DECLARE @BinNumberVARCHAR(200)

SET @BinNumber = ''

WHILE @IncomingNumber <> 0

BEGIN

SET @BinNumber = SUBSTRING('0123456789', (@IncomingNumber...

Thanks! I got another function which works well:

-----------------------------------------

declare @test-2 table(x decimal(18,2))

insert into @test-2

select 12.22 union all

select 9999999999999999.99 union all

select -0.01 union all

select -1.00;

with bits as

(...

Thanks for the answer! I tried the function it works well, but one problem is that if the decimal number has any number of decimals it returns the same result,...

Got it, thanks all for replying!!

Thanks for all your answers! But just adding a column and changing from not null to null would cause both data and log file to grow?