Sum Revenue for each day between 2 given dates in SQL

building reports desktopintelligence
1

Hi-

I am attempting to sum the revenue for each day between 2 given dates in SQL. Does anyone have any suggestions? Thanks in advance!

bgriggs

bgriggs (BOB member since 2010-08-11)

2

Unless I’m misunderstanding what you’re asking for, it’s just:

select revenue from table where mydate between '01-nov-2010' and '30-nov-2010'

Joe

joepeters :us: (BOB member since 2002-08-29)

3

Hi Joe-

Thanks for replying quickly. I’m not trying to sum the revenue between the two dates, but trying to sum the revenue for each day between those two dates.

Example:

sum revenue for each day from table
where date between 2010-11-1 and 2010-11-30

results

day revenue
2010-11-1 35637
2010-11-2 290020
2010-11-3 45067
…

bgriggs (BOB member since 2010-08-11)

4

OH! Then:

select day,sum(revenue) from table where day between '01-nov-2010' and '30-nov-2010' group by day

joepeters :us: (BOB member since 2002-08-29)

5

Thanks! I was just thinking about it and realized the answer. Wow. Talk about a blonde moment. Thanks for your help I really appreciate it!

bgriggs

bgriggs (BOB member since 2010-08-11)

6

my pleasure :slight_smile:

joepeters :us: (BOB member since 2002-08-29)

7

And doing this in a WEBI report where you want to do multiple variations(60, 90 day variables) would be…???

Joealyche (BOB member since 2012-02-29)