Month in a query without month text

[zacharyw.larson]

how do you get a column of your table to say the name of the month and the year for which some events happened?

I want to display data in rows by the last six months
so for the first column June 2021, July 2021...November 2021

SQL function Month returns the number for the month given a date.
I don't see a function for naming the month.

Then the second part is, does each row need to be a separate query?
because I want to get totals, but each of them per month.

I am using transaction group, and it is early so I can change my group too.
I have a tstamp, I have a column for the number of the month of an event, but I need it for the ranges

ahhh edit
I think I need case statement

still confused about how I would get the data for the ranges into different rows though

Another solution:

Month in a query without month text

I may be about to toss some mud in the water. You can create a temporary table using a custom function. This will run from the Database Query Browser. create function dbo.YearMonths(@StartDate DateTime, @EndDate DateTime) returns @YearMonths table ([YearInt] int, [MonthInt] int, [MonthFullName] varchar(20), [MonthShortName] varchar(10)) as begin set @EndDate = DATEADD(month, 1, @EndDate) while (@StartDate < @EndDate) begin insert into @YearMonths select YEAR(@StartDate), …

[Matrix_Engineering]

Can you post your code?

Are you familiar with the AS keyword?

SQL AS

I think you may need to do your Sum or Count or whatever you’re doing, then group by month, and use AS to change the column name

[josborn]

I don’t use transaction groups, but you can get get the date formatted the way you want like this
MSSQL:

format(dateadd(S,t_stamp/1000, '1970-01-01'), 'MMMM yyyy') as tstamp

It’s easier if you use MYSQL:

from_unixtime(t_stamp/1000, '%M %Y') as tstamp

I didnt test the MSSQL code, the conversion for unixtime specifically. While not an answer to your question, it may get you started.

[pturmel]

I think you are describing a PIVOT operation, in addition to wanting the name of the month. In MS SQL Server, you’d use FORMAT(someDate, ‘MMMM’) for that part.

[zacharyw.larson]

I am using sql. I forgot to specify.

if my format uses MMMM, then it will display text for the month? this works in sql?
I will test this. Thanks

thanks for the tip @Matrix_Engineering about the AS. Just a couple days ago I had learned about it for renaming columns.

[pturmel]

@zacharyw.larson

I am using sql.

That tells me nothing. SQL is a generic name. Specific DB brands would be something like "MS SQL Server", "MySQL", "MariaDB", "PostgreSQL", or "Oracle". Or random others. Syntax and available functions do vary.

[dkhayes117]

@zacharyw.larson

Then the second part is, does each row need to be a separate query?
because I want to get totals, but each of them per month.

You will use a group by statement for that

select sum(someColumn) as Total, FORMAT(someDate, 'MMMM') as month
where ...
group by month(someDate)

[zacharyw.larson]

MS Sql
I have to tendency to think of MS as regular

the first column shows “2021-Oct” for example
if I understand, I think I will try this

select  FORMAT(t_stamp, 'yyyy-MMMM') as Year-Month, sum(someColumn) as Total
where (t_stamp>DATEADD(month, -6, GETDATE()))
group by Year-Month(t_stamp)

[dkhayes117]

@zacharyw.larson

group by Year-Month(t_stamp)

I don't think that will work, Year-Month is not a built-in MS-SQL function. Also, I don't think you can group by an aliased column. You would probably need

group by FORMAT(t_stamp, 'yyyy-MMMM')

[zacharyw.larson]

LOL

I can’t tell if it worked because I only have data in November lol

My formatting came out as 2021-November
it did not like Year-Month, used Year_Month

select  FORMAT(t_stamp, 'yyyy-MMMM') as Year_Month, sum(someColumn) as Total
from  tableName 
where (t_stamp>DATEADD(month, -6, GETDATE()))
group by FORMAT(t_stamp, 'yyyy-MMMM')

going to have to add some data to the table to test it

select  FORMAT(t_stamp, 'yyyy-MMM') as Year_Month, sum(someColumn) as Total
from  tableName 
where (t_stamp>DATEADD(month, -6, GETDATE()))
group by FORMAT(t_stamp, 'yyyy-MMM')

MMM gave me 3 letters for the month

[zacharyw.larson]

well I have a new issue
it is ordering the months alphabetically
jun
nov
sep

instead of by the sequential date
However, it is counting and grouping them correctly.
I tried adding desc to the end of an order by FORMAT(t_stamp, 'yyyy-MMM') desc
sep
nov
jun

edit
oh I think I get it, I don’t use format on orderby

[pturmel]

Push your query into a subquery that exposes a t_stamp for each month, then order by that in the outer SELECT. Something like this:

SELECT Year_Month, Total
FROM (
	select min(t_stamp) AS earliest, FORMAT(t_stamp, 'yyyy-MMM') as Year_Month, sum(someColumn) as Total
	from  tableName 
	where (t_stamp>DATEADD(month, -6, GETDATE()))
	group by FORMAT(t_stamp, 'yyyy-MMM')
) subq
ORDER BY earliest

[dkhayes117]

I don’t think you need a subquery to order by t_stamp. You can just add

...
order by t_stamp

[zacharyw.larson]

I got an error for when adding t_stamp.
Said it was not in my group by or my select.

The subquery worked.
It seems very useful that the subquery allows organizing by data that is not shown in the final display.
Thanks for showing me this.

[dkhayes117]

interesting, it works in MariaDB/MySQL

[pturmel]

@zacharyw.larson

subquery allows organizing by data that is not shown in the final display

You don't need a subquery just for that. Any data exposed by a FROM clause when not grouping is available for use in ORDER BY, whether in the SELECT list or not. When using a subquery, only items specified in the inner SELECT list are available to the outer FROM.

[zacharyw.larson]

So I noticed that I only get values for the months that have data.

I found an old post that used coalesce(datathatisnull,0) and this should help me fill in the zeros.

SELECT Year_Month, coalesce(Total,0)
FROM (
	select min(t_stamp) AS earliest, FORMAT(t_stamp, 'yyyy-MMM') as Year_Month, sum(someColumn) as Total
	from  tableName 
	where (t_stamp>DATEADD(month, -6, GETDATE()))
	group by FORMAT(t_stamp, 'yyyy-MMM')
) subq
ORDER BY earliest

this did not work

also tried the coalesce on the sum(someColumn)
did not work

edit
as I think about it, I don’t have values of timestatmps for those months to be null to set them to 0
I just have a lack of any data in those months at all
so I need to somehow check each month, see the null, and then coalesce that value to 0 I think

looks like the internet says create a table of all months, left join it

[josborn]

I don’t know if its the best way, but i have tackled this by using a calendar table and outer join to your actual data.

[pturmel]

@zacharyw.larson

looks like the internet says create a table of all months, left join it

Yes, in MS SQL Server, that is probably the easiest. In more capable databases, I'd use a table generating function to dynamically supply a range of months. In PostgreSQL, it would be the generate_series() function.

[zacharyw.larson]

Is postgresql free?
there is a way to make a table in that and import it to ignition?

Or you mean that it would be nice, but have to make the table in Ignition?

I am not sure how I make this month table.
I think one column is the month, and has the formal name, then the other is a column of numbers 0-11.