group by daterange

[strRusty_gal]

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

[diafol]

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.

[strRusty_gal]

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?

[diafol]

Ah Drat! Sorry _strRusty_gal. I really got to look closer at the subforum name. Apologies:

Maybe this: http://stackoverflow.com/a/446406/4629068

[strRusty_gal]

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