Q: Exponential decay in sql Consider the following table:
Person | 1/1/13 | 1/2/13  | 1/3/13 | 1/4/13 | 1/5/13
Bill   | 4      | 2       | 1      | .5     | .25
Jane   | 0      | 0       | 2      | 1      | .5
Mary   | 0      | 8       | 4      | 2      | 1
-------------------------------------------------
Total  | 4      | 10      | 7      | 3.5    | 1.75

This is derived from the following table:
Bill | 1/1/13 | 4
Jane | 1/3/13 | 2
Mary | 1/2/13 | 8

Basically, we know the first day, and then we assume the value decreases by half every subsequent day. I want to get the "total" row from the first table.
Is there a way to do this in (T-)SQL? I've made it in R, but I'm completely stumped about how to do it in SQL. (The dates are actual dates, not just the day of the week.)

A: If your table looks like this:
CREATE TABLE t (person VARCHAR(7), day_of_week_name VARCHAR(7), value NUMERIC);
INSERT INTO t VALUES ('Bill', 'Monday', 4);
INSERT INTO t values ('Jane', 'Weds', 2);
INSERT INTO t VALUES ('Mary', 'Tuesday', 8);

and you have some sort of day_of_week table with the relative positions of days:
CREATE TABLE day_of_week (name VARCHAR(7), position INT);
INSERT INTO day_of_week VALUES ('Monday', 1);
INSERT INTO day_of_week values ('Tuesday', 2);
INSERT INTO day_of_week VALUES ('Weds', 3);
INSERT INTO day_of_week VALUES ('Thurs', 4);
INSERT INTO day_of_week VALUES ('Friday', 5);

then it's not too ugly to do this with a PIVOT:
SELECT Monday, Tuesday, Weds, Thurs, Friday
  FROM ( SELECT dow2.name AS day_of_week_name,
                t.value / power(2, dow2.position - dow1.position) AS decayed_value
           FROM t
           JOIN day_of_week AS dow1
             ON t.day_of_week_name = dow1.name
           JOIN day_of_week AS dow2
             ON dow1.position <= dow2.position
       ) AS b
 PIVOT ( SUM(decayed_value)
           FOR day_of_week_name
             IN (Monday, Tuesday, Weds, Thurs, Friday)
       ) AS pvt
;

(SQL Fiddle here.)

A: with dow as (
     select 'Monday' as dow, 1 as num union all
     select 'Tuesday', 2 union all
     select 'Wedneday', 3 union all
     select 'Thursday', 4 union all
     select 'Friday', 5 union all
     select 'Saturday', 6 union all
     select 'Sunday', 7
    )
select dow.dow,
       t.num * power(cast(0.5 as float), dow.dow - t.dow))
from (select t.*, dow.dow as dow
      from t join
           dow
           on t.dow = dow.dow
     ) t join
     dow
     on t.dow >= dow.dow
group by t.name, dow.dow

This gets the data in a normalized format . . . , , .
If you really need it pivoted (that is, going across in columns), you can use the pivot keyword, or do conditional aggregation sums.

A: I've created a monster, but it works.
It's mysql, but I'm sure it easily translates to tsql
select sum(mon)
     , sum(tue)
     , sum(wed)
     , sum(thur)
     , sum(fri)
  from (
            select person
                 , if (1 < field(day, 'mon', 'tue', 'wed', 'thur', 'fri'), 0, val / pow(2, 1 - field(day, 'mon', 'tue', 'wed', 'thur', 'fri'))) as mon
                 , if (2 < field(day, 'mon', 'tue', 'wed', 'thur', 'fri'), 0, val / pow(2, 2 - field(day, 'mon', 'tue', 'wed', 'thur', 'fri'))) as tue
                 , if (3 < field(day, 'mon', 'tue', 'wed', 'thur', 'fri'), 0, val / pow(2, 3 - field(day, 'mon', 'tue', 'wed', 'thur', 'fri'))) as wed
                 , if (4 < field(day, 'mon', 'tue', 'wed', 'thur', 'fri'), 0, val / pow(2, 4 - field(day, 'mon', 'tue', 'wed', 'thur', 'fri'))) as thur
                 , if (5 < field(day, 'mon', 'tue', 'wed', 'thur', 'fri'), 0, val / pow(2, 5 - field(day, 'mon', 'tue', 'wed', 'thur', 'fri'))) as fri
            from (
                (select 'bill' as person, 'mon' as day, 4 as val from dual)
                union
                (select 'jane', 'wed', 2 from dual)
                union
                (select 'mary', 'tue', 8 from dual)
            ) t

) tbl

http://sqlfiddle.com/#!2/d41d8/5249

A: like this should work in sql server:
with data 
     as (select ppl.name, 
                case 
                  when ppl.day_of_week = 'Monday' then val 
                  when 1 < day_val then 0 
                end monday, 
                case 
                  when ppl.day_of_week = 'Tuesday' then val 
                  when 2 < day_val then 0 
                  else val * Power(Cast(0.5 as FLOAT), ( 2 - day_val )) 
                end Tuesday, 
                case 
                  when ppl.day_of_week = 'Weds' then val 
                  when 3 < day_val then 0 
                  else val * Power(Cast(0.5 as FLOAT), ( 3 - day_val )) 
                end Weds, 
                case 
                  when ppl.day_of_week = 'Thurs' then val 
                  when 4 < day_val then 0 
                  else val * Power(Cast(0.5 as FLOAT), ( 4 - day_val )) 
                end thurs, 
                case 
                  when ppl.day_of_week = 'Friday' then val 
                  when 5 < day_val then 0 
                  else val * Power(Cast(0.5 as FLOAT), ( 5 - day_val )) 
                end friday 
           from (select name, 
                        day_of_week, 
                        val, 
                        case day_of_week 
                          when 'Monday' then 1 
                          when 'Tuesday' then 2 
                          when 'Weds' then 3 
                          when 'Thurs' then 4 
                          when 'Friday' then 5 
                        end day_val 
                   from person) ppl) 
select coalesce(name, 'Total') as "Name", 
       Sum(monday) as "Monday", 
       Sum(tuesday) as "Tuesday", 
       Sum(weds) as "Weds", 
       Sum(thurs) as "Thursday", 
       Sum(friday) as "Friday" 
  from data 
 group by name with rollup 

http://sqlfiddle.com/#!6/c4c6e/1

A: The following queries assume this table variable is present:
DECLARE @data TABLE (
    Person varchar(50),
    Date date,
    Value int
);

INSERT @data VALUES 
('Bill', '2013-01-01', 4),
('Jane', '2013-01-03', 2),
('Mary', '2013-01-02', 8);

Here's a recursive CTE approach to get the raw decay data:
WITH decay AS (
    SELECT Person, Date, CAST(Value as float) AS Value
    FROM @data
    UNION ALL
    SELECT Person, DATEADD(day, 1, Date) AS Date, Value / 2 AS Value
    FROM decay
    WHERE Value / 2 >= 0.25
)
SELECT Person, Date, SUM(Value) AS Value
FROM decay
GROUP BY Person, Date;

I'd imagine that would probably solve most people's needs, but if you need it pivoted you can specify the dates for the columns:
WITH decay AS (
    SELECT Person, Date, CAST(Value as float) AS Value
    FROM @data
    UNION ALL
    SELECT Person, DATEADD(day, 1, Date) AS Date, Value / 2 AS Value
    FROM decay
    WHERE Value / 2 >= 0.25
)
SELECT pvt.Person, 
    ISNULL(pvt.[2013-01-01], 0) AS [2013-01-01], 
    ISNULL(pvt.[2013-01-02], 0) AS [2013-01-02], 
    ISNULL(pvt.[2013-01-03], 0) AS [2013-01-03], 
    ISNULL(pvt.[2013-01-04], 0) AS [2013-01-04], 
    ISNULL(pvt.[2013-01-05], 0) AS [2013-01-05]
FROM (
    SELECT Person, Date, SUM(Value) AS Value
    FROM decay
    GROUP BY Person, Date
) AS q
PIVOT (
    SUM(Value)
    FOR Date
    IN ([2013-01-01], [2013-01-02], [2013-01-03], [2013-01-04], [2013-01-05])
) AS pvt;

This gives you the result set:
Person  2013-01-01  2013-01-02  2013-01-03  2013-01-04  2013-01-05
------------------------------------------------------------------
Bill    4           2           1           0.5         0.25
Jane    0           0           2           1           0.5
Mary    0           8           4           2           1

Unfortunately, PIVOT requires you to specify the row/column values/names you're pivoting on, so that query would have to be dynamically generated.
