Does this devide TransDate by 100? Not sure why?

Interesting. I use Sage 300 ERP 2014.

I guess what they mean is that only a date can be entered.

In the table it's as a decimal.

Hi, I'm doing inventory snapshots for each of the last 12 months.

For this I need to sum up all transactions up to the month I'm looking at.

The function would help...

Not sure why dates are saved as decimals. Might be a legacy from the early days of the application still.

The table is part of SAGE 300 ERP (also known as...

Yes, @CutOffDate is in the format 'YYYYMM'

Past12Months is a view.

SELECTLEFT(CAST(CONVERT(varchar(8), DATEADD(MONTH, number, LEFT(CAST(CONVERT(varchar(8), DATEADD(MONTH, - 11, GETDATE()), 112) AS INT), 6) + '01'), 112) AS INT), 6) AS YearMonth,

...

Sure.

ALTER FUNCTION [dbo].[ICAgeingFuncT]

(

@CutOffDate varchar(6)

)

RETURNS TABLE

AS

RETURN

(

SELECT SUM(TRANSCOST) AS ItemValue, SUM(Quantity) AS Qty

FROM ICIVAL

WHERE LEFT(TRANSDATE,6) <= @CutOffDate

);

SELECT a.ItemValue, a.Qty, p.YearMonth, p.Period FROM Past12Months as p

CROSS APPLY dbo.ICAgeingFuncT(p.YearMonth) As...

Thanks. I've got it now with iTVF and CROSS APPLY

Sorry, too quick.

How would I return multiple columns from the fucntion, i.e. not only quantity, but value also?

Can I specify what columns the function should return?

Thanks. Got it to work now.

Thank you for your prompt reply.

The issue I have is that instead of using a fixed value for @CutOffDate I need to run my query for all the 'YearMonth' from...

Have it sorted now with the example from the above link.

Thanks.

I saw this post as well.

It is similiar as it demonstrates what the value of stock is using FIFO.

I need to know the ageing of my remaining stock balance.

With a...

Thanks David.

Have worked it out now.

Appreciate the help.

I'm sorry to continue this.

I thought I understood but trying to achieve the same results as previously with other tables I end up again with no NULL values.

The only difference...

Very nice. Working as well.

Thank you very much!