I have a stock movement report where I want to show opening stock on hand, movements & closing SOH in different colums (by cost centre and item). Stock counts are submitted on a adhoc basis on random dates and I need to allow users to define a date range.
So far I can select the correct data for a date range and sum the movement items but I dont want to sum all the opening and closing stock values. Instead I need to find the first SOH value for each cost centre/item within the date range selected. (also another colum for colsing stck being the last date in the date range' value)
I'm thinking its a calulated field along the lines of: IF(MIN(DATE)=DATE,[OPENING STOCK ON HAND],0) but i cant mix aggregate and non aggregate.