Comparing orders by months in SQL Server

Question

I have a sales table with 3 columns

OrderId, CustomersId, OrderDateTime

How to write a T-SQL select query to find number of orderId, January2015orders and April2015orders in the results? Thanks!

Should I use union or a case statement or ???

You should edit your question and provide sample data and desired results. As written (originally) it is somewhere between too vague and too broad to answer. — Gordon Linoff, Oct 10 '15 at 17:10
This is a great question someone asked me and I am seeking advise by someone more experienced here. — Joe, Oct 10 '15 at 17:14

score 1 · Answer 1 · answered Oct 10 '15 at 17:16

1

If I understand you correctly:

select month(OrderDateTime),count(OrderId) from your data group by month(OrderDateTime)

It would be good to know If you mean:

number of orderId as a count ?

answered Oct 10 '15 at 17:16

mql4beginner

2,193
5
34
73

Yes count(orderId).... It's the 2months Jan and April columns that I feel is challenging part. – Joe Oct 10 '15 at 17:19
I ran your query but it does not solve the problem of only producing result for count/number of orderId, January2015orders and April2015orders in the results. – Joe Oct 10 '15 at 17:50

score 1 · Accepted Answer · edited May 23 '17 at 11:44

What you are looking for is the datepart function

If you want orders from the January, April of the year 2015 you could write your query as follows:

SELECT 
    count(t.OrderId) as Orders, 
    DatePart(month, t.OrderDateTime) as Month
FROM 
    SalesTable t
WHERE 
    datepart(year, t.OrderDateTime) = 2015 
    AND (datepart(month, t.OrderDateTime) = 1 
    OR datepart(month, t.OrderDateTime) = 4)
GROUP BY
    datepart(month, t.OrderDateTime)

See this fiddle for a working example

EDIT:

If you want to full month name, instead of the number, you could apply one of these solutions from here. The query would then look like this:

SELECT count(t.OrderId) as Orders, DateName(month , DateAdd( month , DatePart(month, t.OrderDateTime), -1 ))
FROM SalesTable t
WHERE datepart(year, t.OrderDateTime) = 2015 and 
      (datepart(month, t.OrderDateTime) = 1 or datepart(month, t.OrderDateTime) = 4)
group by datepart(month,t.OrderDateTime)

EDIT2: As per your comment, the columns January2015Orders and April2015Orders are mandatory. In this case, you could go with this solution:

SELECT count(t.OrderId) as Orders, 
       DatePart(month, t.OrderDateTime) as January2015Orders,
       null as April2015Orders
FROM SalesTable t
WHERE datepart(year, t.OrderDateTime) = 2015 and 
      datepart(month, t.OrderDateTime) = 1
group by datepart(month,t.OrderDateTime)

UNION

SELECT count(t.OrderId) as Orders, 
       null as January2015Orders,
       DatePart(month, t.OrderDateTime) as April2015Orders
FROM SalesTable t
WHERE datepart(year, t.OrderDateTime) = 2015 and 
      datepart(month, t.OrderDateTime) = 4
group by datepart(month,t.OrderDateTime)

The first query selects January2015Orders with its value and April as null. This is followed by a second query, which selects January as null and April2015Orders with its value.

Not pretty, but it (hopefully) renders the correct results. Here's the fiddle to play around with

This helps, and partially works - however need only count(OrderId), January2015orders and April2015orders in the SELECT statement. It cant be just DatePart(month, t.OrderDateTime) as Month. - It has to be January2015orders and April2015orders. — Joe, Oct 10 '15 at 18:02
Thanks it got me closest. The only thing missing in yours was COUNT(DatePart(month, t.OrderDateTime)) as January2015Orders — Joe, Oct 11 '15 at 15:27

score 0 · Answer 3 · answered Oct 10 '15 at 18:57

You can try this using DATENAME in Sql Server.

like,Fiddle Demo Here

 SELECT 
    Cast(DATENAME(MONTH, t.OrderDateTime) as Varchar(20))+
    cast(DATENAME(YEAR, t.OrderDateTime) as varchar(4)) as YearMonth,
    count(t.OrderId) as Orders
    FROM SalesTable t
    group by DATENAME(MONTH,t.OrderDateTime),DATENAME(YEAR,t.OrderDateTime)

Comparing orders by months in SQL Server

3 Answers3