LASTPRICE

Takes the parent SQL and adds the childDim to the grouping clause, then returns the last entry ordered by childDim and does an aggregation on aggExp. Can handle cases where the grouping clause involves
expressions of ChildDim or child tables expression derived from it. ChildDim needs to be a database field. It will populate an entry in the resultset once it comes across an entry in the child data, and will add
subsequent rows derived from ChildDim, propagating the LastPrice entry, even if there is no underlying data within the group.

Only values of ChildDim that satisfy the where clause are considered. It uses the distinct values of childDim, that satisfy the where clause, for the pivot basis. If an entry of childDim doesn’t appear at least once in the resultset, it will not add additional entries for that value.

Ideal for working with snapshot data, e.g. inventory.

Syntax:

SUM ( LastPrice ( aggExp, childDIm ))

Example:

select customerid, year(orderdate) as theyear,
month(orderdate) as theMonth,
sum(lastprice(freight, orderdate )) as [LastPrice]
from orders
group by customerid,year(orderdate),month(orderdate)
order by customerid, year(orderdate),month(orderdate)