?
Solved

SUM item between two dates

Posted on 2005-04-15
11
Medium Priority
?
295 Views
Last Modified: 2008-02-20
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
0
Comment
Question by:karen_kite
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 14

Accepted Solution

by:
Renante Entera earned 1000 total points
ID: 13796294
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
 
LVL 17

Expert Comment

by:mokule
ID: 13796723
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
 
LVL 17

Expert Comment

by:mokule
ID: 13796730
remember that some Years have more than 365, so playing with months or a year is better
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 8

Assisted Solution

by:anthonywjones66
anthonywjones66 earned 1000 total points
ID: 13797241
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
 

Author Comment

by:karen_kite
ID: 13798839
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
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13803220
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
 

Author Comment

by:karen_kite
ID: 13803243
eNTRANCE2002:

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

Karen
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13803310
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
 

Author Comment

by:karen_kite
ID: 13803453
I have requested for the question Q_21391059 to be reopened.

Karen
0
 
LVL 8

Expert Comment

by:anthonywjones66
ID: 13804266
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question