SSIS - Sorted Aggregation
I have raw data on grain day and need to aggregate it for weekly grain. Most of the fields are light aggregates of amounts. But I have one field in which I need to accept the value of Sunday (sort of like the "first" aggregation) and another field that I need to accept as Saturday.
The road I'm going using SSIS is multiracial submitting my raw data three times, doing a regular Aggregate for simple fields, and then using lookup joins in the calendar table to match the other two on Saturday and Sunday respectively, take those values .. .. then merge all connections back.
Is there a better way to do this?
Examples of source data:
What should be the result:
source to share
Is there a better way to do this? Yes. Don't use a complex SSIS solution for 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 may need to tweak settings 1 and 7 depending on your server settings, but hopefully you get the idea.
source to share
You can use First_value and Last_Value for this as shown below:
select top 1 with ties datepart(week, [day]) as [Week],
sum(sales) over(partition by datepart(week, [day])) as Sales,
FIRST_VALUE(BOP) over(partition by datepart(week, [day]) order by [day]) as BOP
, EOP = LAST_VALUE(EOP) over(partition by datepart(week, [day]) order by [day] RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )
from #youraggregate
Order by Row_number() over(partition by datepart(week, [day]) order by [day])
source to share