I have source data at the day granularity and I need to aggregate it to week granularity. Most fields are easy sum aggregations. But, I have one field that I need to take Sunday's value (kinda like a "first" aggregation) and another field that I need to take Saturday's value.
The road I'm going down using SSIS is to Multicast my source data three times, doing a regular Aggregate for the easy fields, and then using lookup joins to a calendar table to match the other two to Saturday and Sunday respectively to grab those values.... then merge joining everything back together.
Is there a better way to do this?
example source data:
What the output should look like:
