SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 sum by month grouping
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cidr
Posting Yak Master

United Kingdom
207 Posts

Posted - 10/27/2011 :  09:54:02  Show Profile  Reply with Quote
Hi there,

I want to sum amounts by months. However, I want to sum values by the 2nd of each month to the 1st of next month for all months converned.

Does anyone know what the easiest way to do this is?

Thanks for your help

X002548
Not Just a Number

15586 Posts

Posted - 10/27/2011 :  10:00:12  Show Profile  Reply with Quote
One of the thing I tell people is to start small...build out the first component you know you need..in this case..sum by month

what have you tried so far?

Can you post table DDL, sample data in DML Form and then expect results???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/



Edited by - X002548 on 10/27/2011 10:00:31
Go to Top of Page

cidr
Posting Yak Master

United Kingdom
207 Posts

Posted - 10/27/2011 :  10:03:52  Show Profile  Reply with Quote
create table dates
( datecol datetime,
Sales decimal(10,4)
)

insert into dates
values('2010-01-01',14.00),
('2010-01-05',14.00),
('2010-01-01',14.00),
('2010-01-10',14.00),
('2010-01-11',14.00),
('2010-02-17',11.00),
('2010-02-01',14.00),
('2010-02-02',15.00),
('2010-02-20',14.00),
('2010-02-27',17.00),
('2010-02-08',14.00),
('2010-02-28',14.00)

select sum(Sales)
from dates
group by MONTH(datecol)



I should really know how to do this but my mind is not working for me on this one
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/27/2011 :  10:05:04  Show Profile  Reply with Quote
I'm sure there must be more to this but...



CREATE TABLE myTable99(Col1 char(10), Col2 datetime, Col3 money)
GO

INSERT INTO myTable99(Col1, Col2, Col3)
SELECT 'A', '09/11/2011', 50.00 UNION ALL
SELECT 'A', '09/12/2011', 50.00 UNION ALL
SELECT 'A', '09/13/2011', 50.00 UNION ALL
SELECT 'B', '10/11/2011', 50.00 UNION ALL
SELECT 'C', '11/11/2011', 50.00
GO

SELECT MONTH(Col2), SUM(Col3)
FROM myTable99
GROUP BY MONTH(Col2)
GO 

DROP TABLE myTable99
GO




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

cidr
Posting Yak Master

United Kingdom
207 Posts

Posted - 10/27/2011 :  10:33:19  Show Profile  Reply with Quote
Thanks Brett. However, what I'm asking for is months grouped together only by the 2nd day of the month to the 1st day of the next month for example:

2/10/2011 to 2/11/2011
So, everything from from the 2nd day up to and including the 1st of the next month... for each month

Is this something you can help with?

Cheers
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/27/2011 :  10:50:00  Show Profile  Reply with Quote
What would you like to call this "Grouping"



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

cidr
Posting Yak Master

United Kingdom
207 Posts

Posted - 10/27/2011 :  11:18:28  Show Profile  Reply with Quote
em... I don't understand the question Brett. the name of the grouping is irrelevant, it's the technique to fetch the sum amount for the given groups of dates.

Let's call it closing balance

Cheers
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/27/2011 :  12:36:01  Show Profile  Reply with Quote
Do you want to worry about the year as well?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/27/2011 :  12:44:16  Show Profile  Reply with Quote

SELECT MONTH(DATEADD(dd,-1,Date)) AS MonthName,SUM(Col3)
FROM table
GROUP BY MONTH(DATEADD(dd,-1,Date))


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

cidr
Posting Yak Master

United Kingdom
207 Posts

Posted - 09/29/2012 :  16:28:37  Show Profile  Reply with Quote
Came across this post again and never did say thanks for the help

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/01/2012 :  10:36:53  Show Profile  Reply with Quote
no problem
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000