CODEWITHSUNDEEP
sqlsql-servert-sqldatesum
Andrey
Andrey

Reputation: 1759

Calculate SUM by date with week and month trick SQL

What I am trying to achive is calculateing some statistic howewer I have dat in mail table by week< but i need to show statistic as for month/

for example in table i have this 

 '20130226',    312.00
 '20130305',    833.00

Week which ends on 20130305 has 2 days in Feb and 5 in March. I am trying to achieve:

2013-02-28  550.000000
2013-03-31  2285.000000

I have this query:

    CREATE TABLE #Old
    (
    Weeks DATE,
    summ NUMERIC (12,6)    
)
        INSERT INTO #Old VALUES  ( '20130226',  312.00)
        INSERT INTO #Old VALUES  ( '20130305',  833.00)
        INSERT INTO #Old VALUES  ( '20130312',  225.00)
        INSERT INTO #Old VALUES  ( '20130319',  453.00)
        INSERT INTO #Old VALUES  ( '20130326',  774.00) 



 SELECT  
    EOMONTH ( Weeks  )
    , sum (summ)  as cumesum
    FROM  #Old
    GROUP BY  EOMONTH ( Weeks  )

I know its not right because its not splitting that week in two months, but have no ideas how to achieve it.

Upvotes: 1

Views: 1861

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

If I'm understanding correctly, you want to allocate the weekly numbers by month. One approach is to split the data between the current month and the previous month -- doing the allocation as arithmetic on the value.

The rest is just aggregation:

select EOMonth(weeks), sum(month_summ)
from ((select weeks, year(weeks) as yy, month(weeks) as mm,
              (case when day(weeks) >= 7 then summ
                    else summ * day(weeks) / 7.0
               end) as month_summ
       from #old o
      ) union all
      (select dateadd(month, -1, weeks) as weeks,
              year(dateadd(month, -1, weeks)) as yy,
              month(dateadd(month, -1, weeks)) as mm,
              summ * (1 - day(weeks) / 7.0)
       from #old o
       where day(weeks) < 7
      )
     )
group by EOMonth(weeks);

Upvotes: 1

Related Questions