One of my Developers needed my help to write a query.
Here is the requirement.
Table has two columns COL_A (DATETIME) and COL_B (INT)
Data in the table looks like this.
COL_A COL_B
01/01/2012 04:30:000 5
01/04/2012 05:30:000 9
02/05/2012 06:50:000 10
02/14/2012 07:50:000 15
05/14/2012 08:50:000 17
05/22/2012 09:45:000 1
12/12/2012 07:45:000 2
We had to generate a report that would get the maximum value of COL_B for every month and the result should not have any date value for COL_A as the requirement was for month. The result should also be ordered by MONTH.
Here is a query that would work for this scenario.
SELECT substring(convert(varchar(24), dateadd(mm,datediff(mm,0,COL_A),0), 113),4,8),
max(COL_B)
FROM
TABLE
GROUP BY
dateadd(mm,datediff(mm,0,COL_A),0)
ORDER BY
dateadd(mm,datediff(mm,0,COL_A),0)
So, the result should look like this.
Result:
Month Max(COL_B)
Jan-2012 9
Feb-2012 15
May-2012 17
Dec-2012 2