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
Jump to PostYou 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.
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.
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?
Ah Drat! Sorry _strRusty_gal. I really got to look closer at the subforum name. Apologies:
Maybe this: http://stackoverflow.com/a/446406/4629068
Hi _diafol,
Thanks so much for sharing the link, I have applied the reference and managed to come out with a solution.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.