add a column to your allMonth table that has the month abbrev names in it.
then don’t worry about formatting the t_stamp, just select
CONCAT(year(GET_DATE()),"-",allMonth.shortName) as Year_Month
No coalesce needed
thanks, will try after lunch
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), MONTH(@StartDate), FORMAT(@StartDate,'MMMM yyyy'), FORMAT(@StartDate,'MMM yyyy')
set @StartDate = DATEADD(month, 1, @StartDate)
end
return
end
Sample output
SELECT *
FROM dbo.YearMonths(DATEADD(MONTH, -6, GETDATE()), DATEADD(MONTH, 6, GETDATE()))
returns
One of the tables I have is BOM information for production orders. Using this function with that table:
SELECT MonthFullName, qty
FROM (
SELECT COUNT(b.prod_order) as qty, y.MonthInt, y.YearInt, y.MonthFullName FROM bom b
FULL JOIN dbo.YearMonths(DATEADD(MONTH, -6, GETDATE()), DATEADD(MONTH, 6, GETDATE())) y on MONTH(b.t_stamp) = y.MonthInt
GROUP BY y.MonthInt, y.YearInt, y.MonthFullName, y.MonthFullName
) t
ORDER BY YearInt, monthInt
returns
Note that this will also take care of any rollover between years.
If you need to modify the function, you can use
drop function dbo.YearMonths
modify the text, then execute it again to create it.
4 Likes
I have a few silly questions
Why do I keep seeing " y.xxxxx" or “b.xxxx” ?
are people giving tables single letter names or always using AS single letter?
Is there a second on subquery?
I didn’t find it in the w3schools
I think I understand most of how that function works.
I don’t have awareness how the kind of loading that associated or what kind of complexity Create Function is introducing though.
My gut instinct is to avoid this, but it does look extremely useful to get some returns and while loops working for my queries.
So how often do you use Create Function?
Is it like a common tool, or is it really rare to use?
What kind of loading is associated?
Where does it run?
(I don’t now what runs from the Database Query Browser means)
It makes the query easier to read with less to type. If you want to type out the table name every single time, you can do that.
when you write a query, I can’t use y.something right?
I noticed that in Ignition, sometimes it knew I meant the monthNumbers from table allMonths despite that I had not written allMonths.monthNumbers. So I am asking if SQL is trying to find the table and you can use one letter. Or if you mean just to talk about in a post, it is common to use a letter to imply the table?
You can use any letter or combination of letters, x., y., one., two., etc. You are only required to add the prefix if the table you are joining has columns that have the same name as the table being joined to. Otherwise, without the table alias, it doesn’t know which column to choose.
I always give the table and joined table aliases.
1 Like
You only have to run it once to create the function. After that you would just call it as needed. A tad more processing overhead because you're creating a temp table every time, but I just ran the function by itself for months +/- 3 years with rows fetched in 11ms. My sample query ran in around 25ms.
They are alias names of the table. For example, FROM bom b names an alias called b that points to my table called bom .The y after the function call aliases the temp table.
Yep. Good eye. The subquery is aliased to t. For some odd reason the ORDER BY clause at the end would throw a syntax error unless I aliased the subquery. 
I think it depends on your background. DBAs tend to do things in sql functions and stored procedures. Personally, I would have used a simple query and script the rest of the data into submission. 
1 Like
Datasets and dictionaries are my jam…
1 Like
INSERT INTO allMonths
( monthName , monthShortName, monthNumber )
VALUES
('January','Jan',1),
('February','Feb',2),
('March','Mar',3),
('April','Apr',4),
('May','May',5),
('June','Jun',6),
('July','Jul',7),
('August','Aug',8),
('September','Sep',9),
('October','Oct',10),
('November','Nov',11),
('December','Dec',12)
I am going to try the concat way that dkhayes117 mentioned
I am on the fence about the function.
I don’t really understand how that temporary table thing works.
I think if I need to join and concat though, I can do that easy.
Where do the functions exist?
Are they in the table browser after created?
I think you were saying, go to the named queries, make an update query, run the function once.
then in my table, I would call it how you did in the bomb table. I don’t really know where to create the function.
the concat way hit a snag
the order is jul, dec, aug, oct, …
I had been using a reference to the smallest t_stamp to order by
there is no t_stamp for those months, and I think if I use the order by month number, then on a new year, it will be a jumble again
Going to need to know where the functions go when they are created and if I created them with the named query update query
In this case, a temporary table is created by the function and stays in memory until it's no longer needed. After the query is done, it disappears, as it's no longer needed.
Unfortunately, no.
Nope, you can run it from the Query Browser. Or SQL Server Management Studio.
the functions created are stored on the gateway only visible when you go to the object explorer in the ms sql server management studio?
Created functions are stored in the Database, not the Gateway.
They are not stored on the gateway at all. They are stored in SQL Server.
@lrose beat me to it.
1 Like
realized the concat way is also not going to work when years change as well as the sorting by difficulty
so how do I view the function from the browser once it is created?
You don't, why would you need to?
If you want to make changes to the function then the easiest method is to do so with in SSMS, otherwise you would need to write a modify function.
Once you have created the function, you call it in a query/subquery as @JordanCClark has shown.
well I know what it does and how it works
but later, maybe someone will wonder like how that is working and if they can’t view it easy, I should find another way that is more transparent for understanding
or worse, maybe future me will forget and need to know
You can view the function from within SSMS if needed.
Your best option is to comment the code/query and leave yourself or others a little breadcrumb to how to find that information, not just abandon the method outright.
Otherwise you’ll be scripting this into oblivion which will not be nearly as performant. You’ll just need to decide if the trade off is worth it.
Consider adding comments to your query to document them.
SELECT MonthFullName, qty
FROM (
SELECT COUNT(b.prod_order) as qty, y.MonthInt, y.YearInt, y.MonthFullName FROM bom b
-- JOIN to temp table created by custom function dbo.YearMonths
FULL JOIN dbo.YearMonths(DATEADD(MONTH, -6, GETDATE()), DATEADD(MONTH, 6, GETDATE())) y on MONTH(b.t_stamp) = y.MonthInt
GROUP BY y.MonthInt, y.YearInt, y.MonthFullName
) t
ORDER BY YearInt, monthInt
2 Likes