SUM item between two dates

Hello:

I am having a hard time trying to get what seems like the EASIEST query in the world to work.  

The situation... I have two tables:
1. has daily dates with a daily dividend value
2. has an annual date

I want to create a query that will addup the daily dividends for each daily date that falls in a particalur annual date.  So, in other words, I want to add-up daily dividends to get the total amount paid in each year.  (I cannot hard code the dates because I have companies in my database with odd fiscal years...  the annual datadate is fiscal based)

Here is what I have (slightly edited) and it doesn't work... I have tried so many iterations and I am going nuts.  

SELECT annual.datadate, Sum(daily.dailydividend) AS 'totalusdlydiv'
FROM  calc_usddlydiv daily, co_ann annual
WHERE daily.gvkey = annual.gvkey AND (daily.gvkey='009818') AND (daily.datadate >= annual.datadate AND daily.datadate < (annual.datadate-365))
GROUP BY annual.datadate

Any insights would be GREATLY appreciated!!!  (I assigned a lot of points to this one because I was supposed to have this figured out before the end of the week and it is already Friday night... eek!!)

Thanks,
Karen
karen_kiteAsked:
Who is Participating?
 
Renante EnteraSenior PHP DeveloperCommented:
Hi karen_kite!

I am assuming that the datatype of "annual.datadate" is "date" not "datetime".  And "annual.datadate" is the end of fiscal period.

So, this might be what you are looking for :

SELECT annual.datadate, Sum(daily.dailydividend) AS 'totalusdlydiv'
FROM  calc_usddlydiv daily, co_ann annual
WHERE daily.gvkey = annual.gvkey AND (daily.gvkey='009818')
AND daily.datadate BETWEEN DateAdd(dd,1,Dateadd(mm,-12,annual.datadate)) AND annual.datadate
GROUP BY annual.datadate

OR Simply like this :

SELECT annual.datadate, Sum(daily.dailydividend) AS 'totalusdlydiv'
FROM  calc_usddlydiv daily
INNER JOIN co_ann annual
  ON annual.gvkey = daily.gvkey
WHERE daily.gvkey='009818'
AND daily.datadate BETWEEN DateAdd(dd,1,Dateadd(mm,-12,annual.datadate)) AND annual.datadate
GROUP BY annual.datadate

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
 
mokuleCommented:
Not always BETWEEN fullfill requirements. It can be like You've done except

You've got
(daily.datadate >= annual.datadate AND daily.datadate < (annual.datadate-365)
Should have been opposite
(daily.datadate <= annual.datadate AND daily.datadate > (annual.datadate-365)
0
 
mokuleCommented:
remember that some Years have more than 365, so playing with months or a year is better
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
anthonywjones66Commented:
I think eNTRANCE2002 has pretty much nailed it with:-

BETWEEN DateAdd(dd,1,Dateadd(mm,-12,annual.datadate)) AND annual.datadate

but:-

There is no 'date' type in SQL server.  smalldatetime is sufficient if you only want to store contemporary dates but it still has the potential to store hours and minutes.

It's a fair bet that annual.datadate is meant only as a date and therefore it's time element is zero but does it represent the date the year begins or ends?  From your intial attempt at the query where you were subtracting 365 days from it, I would hazard the guess that is stores the year end date.  For example it may be 31 March 2005 with the next fiscal year starting th 01 April 2005.

Problem then is that the Between above would exclude any transactions that actually occured on the 31 March 2005 (unless that just happen to be recorded bang on midnight).

hence this is better:-

BETWEEN DateAdd(dd,1,Dateadd(yy,-1,annual.datadate)) AND DateAdd(dd, 1, annual.datadate)

this has the slight danger that any transactions recorded at 1 Apr 2005 00:00:00 would be included when that shouldn't as mokule intamated.

hence belts and braces is:-

daily.datadate >= DateAdd(dd,1,Dateadd(yy,-1,annual.datadate)) AND daily.datadate < DateAdd(dd, 1, annual.datadate)

of course if you only store whole dates with no time elements then eNTRANCE2002 solution is spot on.

Anthony.




0
 
karen_kiteAuthor Commented:
THANK YOU SOOOOOO MUCH!!!  Yes, the dates were inclusive, Anthony... I am trying to break the query and so far I am good to go even with fiscal year changes.  You guys are the BEST!!!!

Thanks bunches,
Karen
0
 
Renante EnteraSenior PHP DeveloperCommented:
Hi karen_kite!

Yah ...  Anthony is right.  But I think this deserves to be splitted #-o 'coz I'm guiding you on the right track.


Regards!
eNTRANCE2002 :-)
0
 
karen_kiteAuthor Commented:
eNTRANCE2002:

really??  Can I do that??  I am new to all of this.

Karen
0
 
Renante EnteraSenior PHP DeveloperCommented:
Of course, you can do that.

Here's a reference :
   More than one Expert helped solve my problem. What do I do? - http://www.experts-exchange.com/help.jsp#hi69

But before that, since you have already close this one.  You are going to post a question on the Community Support - http://www.experts-exchange.com/Community_Support/ which is a request to re-open your question 'coz you are going to split the points.  The question is worth no points.

Hope this is clear for you.


Goodluck!
eNTRANCE2002 :-)
0
 
karen_kiteAuthor Commented:
I have requested for the question Q_21391059 to be reopened.

Karen
0
 
anthonywjones66Commented:
eNTRANCE2002,

I agree.  Didn't mean to steal the points. As I said I thought you had nailed it allready I just though there were a few details Karen ought to know. :)

Anthony.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.