Friday, April 13, 2012

Select MON-YYYY from DATETIME and GROUP and ORDER BY MON-YYYY

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