SSIS - Sorted Aggregating

Question

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:

Answer 1

Is there a better way to do this? Yes. Don't use a complicated SSIS solution for something that is a simple SQL statement

SELECT 
Day, 
SUM(Sales) Sales, 
MAX(
  CASE WHEN DATEPART(dw,Day) = 1 THEN BOP ELSE NULL END
) As BOP,
MAX(
  CASE WHEN DATEPART(dw,Day) = 7 THEN EOP ELSE NULL END
) As EOP
FROM Table
GROUP BY Table

You might need to tweak the 1 and 7 depending on your server settings but hopefully you get the idea.