Month in a query without month text

Ignition
21

Yes, it is free, however note that as it has more advanced features it takes more experience to achieve what you want.

22 
INSERT INTO allMonths 
	(  monthName , monthNumber   )
VALUES
	('January',1),
	('February',2),
	('March',3),
	('April',4),
	('May',5),
	('June',6),
	('July',7),
	('August',8),
	('September',9),
	('October',10),
	('November',11),
	('December',12)

I think I insert like this
thought I am not sure how the joining will work yet

left join allMonths
on month(t_stamp) = allMonths.monthNumber

not sure

doesn’t throw an error, but doesn’t give me any 0s ether

I added allMonths.monthNumber and allMonths.monthName to the columns and group by

now I get an error “The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.”

Tried using isNull instead of Coalesce. Same error

23

Your select * from table1 portion determines the left side of the data, the join is the right side of the data. Since you are joining your months to your history table, you need to use RIGHT JOIN. This will make sure all months are selected whether they have a match or not from the historical table.

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

Not erroring, and not showing the other months with zeroes

I think I should simplify it some for testing.

SELECT month(t_stamp), allMonths.monthName
FROM   mytable
left join allMonths
on month(t_stamp) = allMonths.monthNumber

This also gives no errors, but doesn’t show me any dates that I don’t have a timestamp for.
I think I can mess around with the column and group by easier

SELECT coalesce(month(t_stamp),0), allMonths.monthName
FROM    mytable
right join allMonths
on month(t_stamp) = allMonths.monthNumber

This one gives me 0 on months without timestamps!
Soon as I add a where clause though, they go away because it is no longer being asked for the months without timestamps.

ah

or allMonths.monthNumber<month(DATEADD(month, -6, GETDATE()))

if I add this, now I have null FORMAT(t_stamp, 'yyyy-MMM')

25

I had this problem once and it took me forever to figure it out. Depending on what you are doing, a where clause after a join can auto convert a RIGHT JOIN or LEFT JOIN into a regular INNER JOIN. I solved my particular issue by changing my where clause to another criterion for the right join on.

26

It seems so close
yet so far

isolating the subquery part first to get that working
had my greater than sign backwards

or allMonths.monthNumber>month(DATEADD(month, -6, GETDATE()))

image
image1438×167 10.3 KB

27

try removing the where clause then adding another join clause to select the months you want

SELECT Year_Month, Total
FROM (
	select min(t_stamp) AS earliest, FORMAT(t_stamp, 'yyyy-MMM') as Year_Month, coalesce(sum(someColumn),0) as Total
	from  tableName 
    right join allMonths
    on month(t_stamp) = allMonths.monthNumber and t_stamp>DATEADD(month, -6, GETDATE())
	group by FORMAT(t_stamp, 'yyyy-MMM')
) subq
ORDER BY earliest
28

this got me the same thing except also nulls for months before June

I think I need to find out a way to write values for those nulls to finish
and I need to read how the joins work more, because I don’t really understand left vs right join still

coalesce(FORMAT(t_stamp, 'yyyy-MMM'), some way to set the date to value in that month with the null)

image
lol

29

you probably need to change the 6 months part in the right join to select only the months you want to see based on the allMonths table, not the other one. I’m certain this will work

SELECT Year_Month, Total
FROM (
	select min(t_stamp) AS earliest, FORMAT(t_stamp, 'yyyy-MMM') as Year_Month, coalesce(sum(someColumn),0) as Total
	from  tableName 
    right join allMonths as AM
    on month(t_stamp) = AM.monthNumber and AM.monthNumber >=  month(GETDATE()) - 6
	group by FORMAT(t_stamp, 'yyyy-MMM')
) subq
ORDER BY earliest
30

I am capturing the months.

There is no t_stamp on those months though, so they show up as null

both the WHERE I had last posted and the JOIN you had posted will show them.
Just they come up as null, or if I put something wrong in there, mmm

31

Of course they are null, there is no data to select a t_stamp from. What do you want it to show instead of the null?

32

Instead of the null, I am trying to show “2021-Jul”, “2021-Aug”

33

you’ll have to generate that from the allMonths table instead of from the t_stamp.

34

oh that is a good idea

edit well I am not sure if that will work, as I am not sure how I would modify the year and the month both

35

get the year portion from formatting GET_DATE() and concat it to the month name from the allMonths table

36

is there some way to say like

coalesce(FORMAT(t_stamp, 'yyyy-MMM'),FORMAT(DATEADD(month, (-12+allmonth.monthNumber), GETDATE()), 'yyyy-MMM')

If I could feed that column the value from another column to calculate, then I think I could make it work

37

instead of coalesce you might want to try ifnull(exp1,exp2)

38

ifnull is not a recognized function in MS SQL

39

you’ll have to find the ms sql equivalent function. IFNULL selects the value, and if it is null it falls back to the 2nd expression

40

I think it is Coalesce, supposed to be an ansi standard if I understood correctly, though I am not sure why

coalesce(FORMAT(t_stamp, 'yyyy-MMM'),FORMAT(DATEADD(month, (-3), GETDATE()), 'yyyy-MMM'))

When I do this, I get “2021-Aug” for the nulls
image

I just don’t now how to make that -3 more dynamic

if I could use value from monthNumber for that row, I would be set