This is similar to the above functions, but it will add additional entries to the output, so that all grouping rows, will be pivoted against all values of childDim, and its joined dimension table, that a where clause with the childDim part only. It will also examine data, from before the minimal value of childDim, that satisfies the where clause. In the example below it would also add entries for months 1 to 11 to the output, because there is prior data in 2014, and there are orders in 2015 for months 1 to 11 for other customers. This adds entry for childDim for values that are in the underlying data, but don't satisfy the where clause.
Ideal for working with snapshot data, e.g. inventory.
Syntax:
SUM ( LastPriceWithZeroes ( aggExp, childDIm ))
Example:
select customerid, year(orderdate) as theyear,
month(orderdate) as theMonth,
sum(lastpricewithzeroes(freight, orderdate )) as [LastPriceWithZeroes]
from orders
where customerid='DRACD' and year(orderdate) = 2015
group by customerid,year(orderdate),month(orderdate)
order by customerid, year(orderdate),month(orderdate)

  • No labels