Solved

# SUM item between two dates

Posted on 2005-04-15
Medium Priority
295 Views
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.

FROM  calc_usddlydiv daily, co_ann annual

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
Question by:karen_kite
• 3
• 3
• 2
• +1

LVL 14

Accepted Solution

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 :

FROM  calc_usddlydiv daily, co_ann annual
WHERE daily.gvkey = annual.gvkey AND (daily.gvkey='009818')

OR Simply like this :

FROM  calc_usddlydiv daily
INNER JOIN co_ann annual
ON annual.gvkey = daily.gvkey
WHERE daily.gvkey='009818'

Hope this helps you.  Just try it.

Goodluck!
eNTRANCE2002 :-)
0

LVL 17

Expert Comment

ID: 13796723
Not always BETWEEN fullfill requirements. It can be like You've done except

You've got
Should have been opposite
0

LVL 17

Expert Comment

ID: 13796730
remember that some Years have more than 365, so playing with months or a year is better
0

LVL 8

Assisted Solution

anthonywjones66 earned 1000 total points
ID: 13797241
I think eNTRANCE2002 has pretty much nailed it with:-

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:-

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:-

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

Anthony.

0

Author Comment

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

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

ID: 13803243
eNTRANCE2002:

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

Karen
0

LVL 14

Expert Comment

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

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

Karen
0

LVL 8

Expert Comment

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

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
Course of the Month13 days, 23 hours left to enroll