Sql pivot query with complex aggregation

Question

I've got some problems with a pivot query (SQL Server). The task is quite simple: for a person I have to collect it's income stats for every month in a year but every new month income is based on the previuos income plus the current month income

Just for example. Let a person have a 3k salary per month (for simplicity it's a constant) then a query result should be something like this:

Year | Jan | Feb | ... | Dec
2016 | 3k  | 6k  | ... | 36k
2015 | 3k  | 6k  | ... | 36k
...

A pseudo SQL query is:

select * from (
    select 
        year(date) as year,
        month(date) as month
        salary,
    from income
    where personId = 'some id'
) as tmp
pivot (
    sum(salary),
    for month in ([1], [1..2], [1..3], ...)
) as pvt

The problem is there's no [1..2] expression in SQL. What's the way to perform such query using a standard SQL?

Just some off the top of my head hints:inner query to partition and sum using a suitable window (between rows unbounded preceding and current?), with a label for your period 1to2, 1to3, 1to4 etc Then pivot on your labels. If I've time later tonight I'll have a look at a full solution. — Liesel
– Liesel, Commented May 31, 2016 at 11:03

mxix · Accepted Answer · 2016-05-31 11:24:53Z

2

Maybe something like this? (this OVER will work for version 2008 R2 and after)

create table #income (
    personid int,
    salary int,
    [date] date
)

insert into #income 
(personid,salary,[date])
values
(1,3000,'2016-01-31'),
(1,3000,'2016-02-29'),
(1,3000,'2016-03-31'),
(1,3000,'2016-04-30'),
(1,3000,'2016-05-31');

select * from (
    select 
        year(date) as year,
        month(date) as month,
        SUM(salary) OVER ( PARTITION BY personid ORDER BY [date]) salary
    from income
    where personId = 1
) as tmp
pivot (
    sum(salary)
    for month in ([1], [2], [3],[4],[5])
) as pvt;

drop table #income;

answered May 31, 2016 at 11:24

mxix

3,6591 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shadeglare Over a year ago

If there's a previous year values it affects next year results.

Akanksha Singh Over a year ago

I think PARTITION BY personid, year(date) ORDER BY [date] should suffice your requirement.

Collectives™ on Stack Overflow

Sql pivot query with complex aggregation

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related