Problem in monthly sum from start date

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 2008 Forums

Transact-SQL (2008)

Problem in monthly sum from start date

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2012-04-29 : 04:59:33

Hi,

I have a table named credit summary which stores credit and I want to know monthly credit from date added. So month start date will be first record to 30 days.

I have data like:


Id   CreditTotal    DateAdded       
1        15         04/25/2012
2         5         04/30/2012
3        20         05/10/2012
4        5          05/27/2012
5        4          06/05/2012

So here first month will be 25 April 2012 to 25 May 2012 and so on.
I want sum of credit total each month duration wise.

can anyone please help to this query?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-29 : 07:50:26

If you know in advance the day of the month on which your period begins, then you can sum and group by like this (where I am assuming that the period begins on the 25th):

SELECT DATEADD(mm,CASE WHEN day(DateAdded) < 25 THEN -1 ELSE 0 END +
			DATEDIFF(mm,'19000101',DateAdded),'19000125') AS PeriodBegin,
	SUM(CreditTotal) AS TotalMonthlyCredits
FROM
	YourTable
GROUP BY
	DATEADD(mm,CASE WHEN day(DateAdded) < 25 THEN -1 ELSE 0 END +
			DATEDIFF(mm,'19000101',DateAdded),'19000125')

keyursoni85
Posting Yak Master

233 Posts

Posted - 2012-04-29 : 09:31:39

yes, that's good..
thank you.. I know date number 25 from to start and can make dynamic query as you suggested.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources