CODEWITHSUNDEEP
sqlsql-serverdate
Selrac
Selrac

Reputation: 2293

SQL spread month value into weeks

I have a table where I have values by month and I want to spread these values by week, taking into account that weeks that spread into two month need to take part of the value of each of the month and weight on the number of days that correspond to each month.

For example I have the table with a different price of steel by month

Product         Month       Price
------------------------------------
Steel   1/Jan/2014    100
Steel   1/Feb/2014    200
Steel   1/Mar/2014    300

I need to convert it into weeks as follows

Product Week        Price
-------------------------------------------
Steel   06-Jan-14   100
Steel   13-Jan-14   100
Steel   20-Jan-14   100
Steel   27-Jan-14   128.57
Steel   03-Feb-14   200
Steel   10-Feb-14   200
Steel   17-Feb-14   200

As you see above, the week that overlaps between Jan and Feb needs to be calculated as follows

(100*5/7)+(200*2/7)

This takes into account tha the week of the 27th has 5 days that fall into Jan and 2 into Feb.

Is there any possible way to create a query in SQL that would achieve this?

I tried the following

First attempt:

select
WD.week,
PM.PRICE,
DATEADD(m,1,PM.Month),
SUM(PM.PRICE/7) * COUNT(*)
from
(   select '2014-1-1' as Month, 100 as PRICE
    union
    select '2014-2-1' as Month, 200 as PRICE
)PM
join
(   select '2014-1-20' as week
    union
    select '2014-1-27' as week
    union
    select '2014-2-3' as week
)WD
ON WD.week>=PM.Month
AND WD.week < DATEADD(m,1,PM.Month)
group by
WD.week,PM.PRICE, DATEADD(m,1,PM.Month)

This gives me the following

week    PRICE   
2014-1-20   100 2014-02-01 00:00:00.000 14
2014-1-27   100 2014-02-01 00:00:00.000 14
2014-2-3    200 2014-03-01 00:00:00.000 28

I tried also the following

;with x as (
    select price, 
        datepart(week,dateadd(day, n.n-2, t1.month)) wk, 
        dateadd(day, n.n-1, t1.month) dt
    from 
    (select '2014-1-1' as Month, 100 as PRICE
    union
    select '2014-2-1' as Month, 200 as PRICE) t1
    cross apply (
        select datediff(day, t.month, dateadd(month, 1, t.month)) nd
        from 
        (select '2014-1-1' as Month, 100 as PRICE
        union
        select '2014-2-1' as Month, 200 as PRICE)
        t
        where t1.month = t.month) ndm
    inner join 
        (SELECT (a.Number * 256) + b.Number AS N FROM
        (SELECT number FROM master..spt_values WHERE type = 'P' AND number <= 255) a (Number), 
        (SELECT number FROM master..spt_values WHERE type = 'P' AND number <= 255) b (Number)) n --numbers 
    on n.n <= ndm.nd
)
select min(dt) as week, cast(sum(price)/count(*) as decimal(9,2)) as price
from x
group by wk
having count(*) = 7
order by wk

This gimes me the following

week                        price
2014-01-07 00:00:00.000 100.00
2014-01-14 00:00:00.000 100.00
2014-01-21 00:00:00.000 100.00
2014-02-04 00:00:00.000 200.00
2014-02-11 00:00:00.000 200.00
2014-02-18 00:00:00.000 200.00

Thanks

Upvotes: 0

Views: 2007

Answers (2)

dnoeth
dnoeth

Reputation: 60462

If you have a calendar table it's a simple join:

SELECT 
   product, 
   calendar_date - (day_of_week-1) AS week,
   SUM(price/7) * COUNT(*)
FROM prices AS p
JOIN calendar AS c
  ON c.calendar_date >= month 
 AND c.calendar_date < DATEADD(m,1,month)
GROUP BY product,
   calendar_date - (day_of_week-1)

This could be further simplified to join only to mondays and then do some more date arithmetic in a CASE to get 7 or less days.

Edit:

Your last query returned jan 31st two times, you need to remove the =from on n.n < ndm.nd. And as you seem to work with ISO weeks you better change the DATEPART to avoid problems with different DATEFIRST settings.

Based on your last query I created a fiddle. 

;with x as (
    select price, 
        datepart(isowk,dateadd(day, n.n, t1.month)) wk, 
        dateadd(day, n.n-1, t1.month) dt
    from 
    (select '2014-1-1' as Month, 100.00 as PRICE
    union
    select '2014-2-1' as Month, 200.00 as PRICE) t1
    cross apply (
        select datediff(day, t.month, dateadd(month, 1, t.month)) nd
        from 
        (select '2014-1-1' as Month, 100.00 as PRICE
        union
        select '2014-2-1' as Month, 200.00 as PRICE)
        t
        where t1.month = t.month) ndm
    inner join 
        (SELECT (a.Number * 256) + b.Number AS N FROM
        (SELECT number FROM master..spt_values WHERE type = 'P' AND number <= 255) a (Number), 
        (SELECT number FROM master..spt_values WHERE type = 'P' AND number <= 255) b (Number)) n --numbers 
    on n.n < ndm.nd
) select min(dt) as week, cast(sum(price)/count(*) as decimal(9,2)) as price
from x
group by wk
having count(*) = 7
order by wk

Of course the dates might be from multiple years, so you need to GROUP BY by the year, too.

Upvotes: 1

dean
dean

Reputation: 10098

Actually, you need to spred it over days, and then get the averages by week. To get the days we'll use the Numbers table.

;with x as (
    select product, price, 
        datepart(week,dateadd(day, n.n-2, t1.month)) wk, 
        dateadd(day, n.n-1, t1.month) dt
    from #t t1
    cross apply (
        select datediff(day, t.month, dateadd(month, 1, t.month)) nd
        from #t t
        where t1.month = t.month and t1.product = t.product) ndm
    inner join numbers n on n.n <= ndm.nd
)
select product, min(dt) as week, cast(sum(price)/count(*) as decimal(9,2)) as price
from x
group by product, wk
having count(*) = 7
order by product, wk

The result of datepart(week,dateadd(day, n.n-2, t1.month)) expression depends on SET DATEFIRST so you might need to adjust accordingly.

Upvotes: 1

Related Questions