CODEWITHSUNDEEP
sqlsql-server
Roky
Roky

Reputation: 17

Need help on Query

Table_Name : Order_trans_detail

Order_id    Order_date  Order_qty   Item_id  order_amount
100        12-Jan-16    1            1001   20
101        13-Feb-15    4            1001   80
103        14-Mar-16    3            1001   60
104        16-Dec-15    9            1001   180
105        17-Jan-16    1            1001   20
106        18-Feb-16    4            1001   80
107        19-Feb-16    3            1001   60
108        20-Jan-15    9            1001   180
109        21-Mar-15    3            1001   60
110        21-Apr-15    3            1001   60

Need Query to identify how many orders placed in Month of Feb-2016 as to display Month Name and count.

Upvotes: 0

Views: 47

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

You need to use DATENAME and YEAR function to extract Month name and Year from date and use it in Group by to get the count

select DATENAME(MONTH,Order_date ),YEAR(Order_date), Count(*)
From Order_trans_detail
Group by DATENAME(MONTH,Order_date ),YEAR

To filter the records add Where clause 

Where DATENAME(MONTH,Order_date ) = 'february' and YEAR(Order_date) = 2016

To get the result in Mon-year format use this in Select 

DATENAME(MONTH,Order_date )+'-'+cast(YEAR(Order_date) as char(4))

If you are using SQL Server 2012+ to concatenate month and year use CONCAT function

CONCAT(DATENAME(MONTH,Order_date ),'-',YEAR(Order_date))

Advantage of using CONCAT is that you don't need to perform explicit conversion when concatenating Int with Varchar

Upvotes: 2

Related Questions