CODEWITHSUNDEEP
sqlsql-server
Madost
Madost

Reputation: 13

SQL Server Sum data between two dates group by date

I have following data in my table:

eb          |anz
05.03.2020  | 2
06.03.2020  | 3
07.03.2020  | 1
08.03.2020  | 9
09.03.2020  | 10
10.03.2020  | 2
11.03.2020  | 20
12.03.2020  | 25

Now I need to sum the values in specific range for each date. For example "12.03.2020": I want to sum the value of the 12th, 11th, 10th and 9th of march for the date "12.03.2020". Additionally I want to sum the other four values before 9th of march and divide the summary 1 by summary 2 by select.

So my calculation would be: (25+20+2+10)/(9+1+3+2) = 3.8 I would like to output the date and the calculated value for each date in table.

I tried to sum the first group for each date (in example 9th to 12th march) but the output is the same as the data in the table.

select
eb,
sum(anz)
from (select eb, count(*) as anz from myTable where eb != '' group by eb) tmp
where 
convert(date, eb, 104) >= dateadd(day,-3,convert(datetime, eb, 104)) 
and convert(date, eb, 104) <= convert(date, eb, 104)
group by eb
order by convert(date, eb, 104)

It looks like the condition is being ignored. Do you have any advice for me?

Thanks a lot

Upvotes: 1

Views: 688

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Let me assume that data is stored correctly as a date then you can use window functions:

select t.*,
       (sum(anz) over (order by eb rows between 3 preceding and current row) /
        sum(anz) over (order by eb rows between 8 preceding and 4 preceding)
       )
from t;

Note that if value is an integer, then use * 1.0 / to avoid integer division.

Also, this assumes that you have data on each date.

Upvotes: 1

Related Questions