0

Can someone please help to advise below question?

I need to do a SUM of the amount column based on the CreateDate date range From 10th of previous month to 10th of this month

Below is the database table

Amount  CreateDate
151.00  5 July 2015
567.50  19 July 2015
558.00  25 August 2015
550.05  4 August 2015

The SQL result need to display as per the table below:

SumAmount   Period
718.5   July 2015
1108.05 August 2015
2
Contributors
4
Replies
26
Views
2 Years
Discussion Span
Last Post by strRusty_gal
1

You could try this:

SELECT SUM(`Amount`) AS `SumAmount`, DATE_FORMAT(STR_TO_DATE(`CreateDate`,'%d %M %Y'), '%M %Y') AS `Period` FROM TableName GROUP BY YEAR(STR_TO_DATE(`CreateDate`,'%d %M %Y')), MONTH(STR_TO_DATE(`CreateDate`,'%d %M %Y')) ORDER BY `Period`

However, storing dates as in the format CreateDate is a bit daft. Store as YYYY-MM-DD.

0

Hi diafol, thanks for the reply.

I guess the query that you gave is for MySQL? please correct me if I am wrong.
I actually need query that could be execute in mssql.
Will it be possible to actually include the date range also?

1

Hi _diafol,
Thanks so much for sharing the link, I have applied the reference and managed to come out with a solution.

Votes + Comments
Great! Mind sharing the solution?
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.