How to Separate Months by Their Years in SQL

Question

I am trying to see the profit by each month in SQL. However, I have multiple "Julys" and can't seem to separate them into different years. This is the query I am trying.

select 
    concat(datename(year, o.orderdate), ' ', datename(month, o.OrderDate)) [Month], 
    sum((t.StandardPrice - t.TotMaterialCost) * ol.OrderedQuantity) [Total Profit]
from 
    Temp_V t, OrderLine_T ol, Order_T o
where 
    t.ProductID = ol.ProductID 
    and ol.OrderID = o.OrderID
group by 
    o.OrderDate

I am getting the correct output, but for instance, it says July 2017 in two different rows instead of July 2017 and then July 2018. (Which is what I want it to say). I think it is an error with Concat().

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, Nov 11 '20 at 17:37

score 2 · Answer 1 · answered Nov 11 '20 at 17:41

2

You want, I believe, this use of EOMONTH():

SELECT EOMONTH(o.orderDate) month_ending,
       sum(<whatever>) [Total Profit]
  FROM <whatever>
 GROUP BY  EOMONTH(o.orderDate)

The EOMONTH function takes any date or date/time value and truncates it to the start of the last day of the month. So, it's an ideal way to take a whole mess of orderDate values and group them into the months when they happened.

answered Nov 11 '20 at 17:41

O. Jones

103,626
17
118
172

I didn't know EOMONTH existed until now. After reading, it came as a surprise that it had been there since sql 2012 and I didn't come across it. I had been using my very own user defined function for such operations. Glad I visited SO today. – Codeek Nov 11 '20 at 17:58
Yes. I make a point of reviewing, once every couple of months, the date/time functions of whatever DBMS I'm working with. It's worth every second of the time I spend on it. Less bugs, clearer code. Let a vast company with lots of testers worry about calendars, time zones, day rollovers, and all that stuff. Better them than me! – O. Jones Nov 11 '20 at 21:34
Well it will be my new habit from now on too. Would be grateful if you could share some insights on your methods of doing so or some useful links that are updated as per new standards (other than w3school). Won't be able to post responses here in comments to avoid spamming so thank you in advance. oh yes learning is fun... – Codeek Nov 11 '20 at 22:01

Codeek · Accepted Answer · 2020-11-11T17:53:35.373

1

I believe what you are missing is a correct "group by" clause which should be :

group by concat(datename(year, o.orderdate), datename(month, o.OrderDate))

edited Nov 11 '20 at 17:53

answered Nov 11 '20 at 17:44

Codeek

1,624
1
11
20

How to Separate Months by Their Years in SQL

2 Answers2