| Author |
 Topic  |
|
|
cidr
Posting Yak Master
United Kingdom
192 Posts |
 Posted - 10/27/2011 : 09:54:02
|
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 |
|
|
cidr
Posting Yak Master
United Kingdom
192 Posts |
Posted - 10/27/2011 : 10:03:52
|
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 |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 10/27/2011 : 10:05:04
|
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/
|
|
|
|
cidr
Posting Yak Master
United Kingdom
192 Posts |
Posted - 10/27/2011 : 10:33:19
|
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 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
cidr
Posting Yak Master
United Kingdom
192 Posts |
Posted - 10/27/2011 : 11:18:28
|
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
|
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 10/27/2011 : 12:44:16
|
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/
|
|
|
|
cidr
Posting Yak Master
United Kingdom
192 Posts |
Posted - 09/29/2012 : 16:28:37
|
Came across this post again and never did say thanks for the help
Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 10/01/2012 : 10:36:53
|
no problem
you're welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
sum by month grouping
