how to summarize data in SQL query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to summarize data in SQL query

Author

Topic

scotthoop
Starting Member

3 Posts

Posted - 2009-04-09 : 12:44:13

Hi,

Below is an example of how my data is organized and how I would like to extract it with an SQL query.

Start Date End Date Amount
Apr 13 2009 April 19 2009 $100
April 20 3009 April 26 2009 $500
April 27 2009 May 3 2009 $700
May 4 2009 May 10 2009 $500

I would like to extract it like this:
Month Total Amount
April $1,000
May $800

An example of a script would be greatly appreciated!

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-09 : 12:48:22

The "Script Library" forum is for working scripts, so I am moving your topic to a different forum.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

asgast
Posting Yak Master

149 Posts

Posted - 2009-04-10 : 04:35:36

SELECT END SUN(total)
FROM .....
GROUP BY END

doing your homework?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-10 : 06:55:33

quote:
Originally posted by scotthoop

Hi,

Below is an example of how my data is organized and how I would like to extract it with an SQL query.

Start Date End Date Amount
Apr 13 2009 April 19 2009 $100
April 20 3009 April 26 2009 $500
April 27 2009 May 3 2009 $700
May 4 2009 May 10 2009 $500

I would like to extract it like this:
Month Total Amount
April $1,000
May $800

An example of a script would be greatly appreciated!

Thanks

did nt understand how you split up 700 that ranged over from April 27 2009 May 3 2009 among months April & May

asgast
Posting Yak Master

149 Posts

Posted - 2009-04-10 : 07:55:11

well its magic :) i think he just typed examples without checking them, because my degree in math says there is no way to do it

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-10 : 09:38:02

Your degree in math from where?

split the 700 into 300 and 400 and bingo, you have what he wants.

How he decides to split it is the mystery....

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

scotthoop
Starting Member

3 Posts

Posted - 2009-04-13 : 10:48:25

I guess I should have further explained.

When a record begins in one month and ends in the next month, the amount should be split between the two months proportional to the number of days in each month (between the record's start and end dates).

So, for Apr 27 - May 3...There are 4 days in April and 3 days in May. So, the $700 is split such that 4/7 is in April and 3/7 is in May. Or...$400 in April and $300 in May.

Your help is greatly appreciated!

scotthoop
Starting Member

3 Posts

Posted - 2009-04-15 : 12:33:47

Hi All,

I provided some clarification with my previous post. Any ideas?....

Thanks in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources