CODEWITHSUNDEEP
sqlsql-server-2008
Julian
Julian

Reputation: 112

SQL Rolling Total up to a certain date

I have two tables that I'm working with. Let's call them "Customers" and "Points".

The Points table looks like this:

Account Year M01 M02 M03 M04 M05 M06 M07 M08 M09 M10 M11 M12
123     2011  10   0   0   0  10   0  10   0   0   0   0  10
123     2012   0   0   0   0  10   0   0  10  10  10  10  20
123     2013   5   0   0   0   0   0   0   0   0   0   0   0

But these points work on a rolling 12 months. Calculating a current customer's points is simple enough, but the challenge is for customers who are no longer active. Say Customer 123 became inactive on Jan 2013, we would only want to calculate Feb'12-Jan'13. This is where the other table, Customers, comes in, let's simplify and say it looks just like this:

Account   End Date
123       20130105

Now, what I want to do is create a query that calculates the amount of points that each customer has. (Current 12 months for active customers, last 12 months they were active for customers who are no longer active.)

Here's some more information:

This is a pretty complex question. If there's anything I can explain better please let me know. Thank you!

Upvotes: 1

Views: 365

Answers (2)

Taryn
Taryn

Reputation: 247680

Unfortunately with your table structure of points you will have to unpivot the data. An unpivot takes the data from the multiple columns into rows. Once the data is in the rows, it will be much easier to join, filter the data and total the points for each account. The code to unpivot the data will be similar to this:

select account,
  cast(cast(year as varchar(4))+'-'+replace(month_col, 'M', '')+'-01' as date) full_date,
  pts
from points
unpivot
(
  pts
  for month_col in ([M01], [M02], [M03], [M04], [M05], [M06], [M07], [M08], [M09], [M10], [M11], [M12])
) unpiv

See SQL Fiddle with Demo. The query gives a result similar to this:

| ACCOUNT |  FULL_DATE | PTS |
------------------------------
|     123 | 2011-01-01 |  10 |
|     123 | 2011-02-01 |   0 |
|     123 | 2011-03-01 |   0 |
|     123 | 2011-04-01 |   0 |
|     123 | 2011-05-01 |  10 |

Once the data is in this format, you can join the Customers table to get the total points for each account, so the code will be similar to the following:

select 
  c.account, sum(pts) TotalPoints
from customers c
inner join 
(
  select account,
      cast(cast(year as varchar(4))+'-'+replace(month_col, 'M', '')+'-01' as date) full_date,
    pts
  from points
  unpivot
  (
    pts
    for month_col in ([M01], [M02], [M03], [M04], [M05], [M06], [M07], [M08], [M09], [M10], [M11], [M12])
  ) unpiv
) p
  on c.account = p.account
where 
(
  c.enddate = '9999-12-31'
  and full_date >= dateadd(year, -1, getdate()) 
  and full_date <= getdate()  
)
or
(
  c.enddate <> '9999-12-31'
  and dateadd(year, -1, [enddate]) <= full_date
  and full_date <= [enddate]
)
group by c.account

See SQL Fiddle with Demo

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Lousy data structure. The first thing to do is to unpivot it. Then you get a table with year-month-points as the columns.

From here, you can just select the most recent 12 months. In fact, you don't even have to worry about when a customer left, since presumably they have not collected points since then.

Here is an example in SQL:

with points as (
    select 123 as account, 2012 as year,
           10 as m01, 0 as m02, 0 as m03, 0 as m04, 10 as m05, 0 as m06,
           10 as m07, 0 as m08, 0 as m09, 0 as m10, 0 as m11, 10 as m12
   ),
   points_ym as (
    select account, YEAR, mon, cast(right(mon, 2) as int) as monnum, points
    from points
    unpivot (points for mon in (m01, m02, m03, m04, m05, m06, m07, m08, m09, m10, m11, m12)
    ) as unpvt
   )
select account, SUM(points)
from points_ym
where year*12+monnum >= year(getdate())*12+MONTH(getdate()) - 12
group by account

Upvotes: 2

Related Questions