CODEWITHSUNDEEP
sqlsql-serverssis
ArcherBird
ArcherBird

Reputation: 2134

SSIS - Sorted Aggregating

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:

enter image description here

What the output should look like:

enter image description here

Upvotes: 3

Views: 665

Answers (3)

Nick.Mc
Nick.Mc

Reputation: 19184

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.

Upvotes: 2

Srinivasa Rao
Srinivasa Rao

Reputation: 172

Use Derived column transformation to get the week first

DATEPART("wk", Day)

After that use Aggregate using Week Column

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use First_value and Last_Value for this as 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])

Upvotes: 2

Related Questions