Hihi, I am trying to create report using Table transformer using JIRA as source. I would like to display the quantify of the tickets of each month, also would like the result shown according to the sequence of Month(such as Jan24,Feb24,Mar24..etc) using below query:
SELECT
T1.'Labels',
T1.[Count] AS 'ABC',
SUM(T2.[Count]) AS 'PrJ'
FROM T1 LEFT JOIN T2 ON T1.'Labels' = SUBSTR(T2.'Labels', 1, 5)
GROUP BY T1.'Labels', T1.[Count]
ORDER BY CAST(CONCAT('20', SUBSTR(T1.'Labels',4,2)) AS UNSIGNED),
CASE SUBSTR(T1.'Labels',1,3)
WHEN "Jan" THEN 1 WHEN "Feb" THEN 2
WHEN "Mar" THEN 3 WHEN "Apr" THEN 4
WHEN "May" THEN 5 WHEN "Jun" THEN 6
WHEN "Jul" THEN 7 WHEN "Aug" THEN 8
WHEN "Sep" THEN 9 WHEN "Oct" THEN 10
WHEN "Nov" THEN 11 WHEN "Dec" THEN 12
END
---------------------------------
with this SQL, the result can be shown with sequence of the first character, but not date sequence. I had tried to test the SQL when the data from only one table, and it worked, it can shown via date sequence, but have no idea why can not work when join the table as SQL shown above, is that because the "Group by" not correct. Can someone please help?Many thanks.
SELECT
T1.'Labels',
T1.'Count' as 'ABC'
FROM T1
Order by
CAST('20'||SUBSTR(T1.'Labels',4,2) AS INTEGER),
CASE SUBSTR(T1.'Labels',1,3)
WHEN "Jan" THEN 1 WHEN "Feb" THEN 2
WHEN "Mar" THEN 3 WHEN "Apr" THEN 4
WHEN "May" THEN 5 WHEN "Jun" THEN 6
WHEN "Jul" THEN 7 WHEN "Aug" THEN 8
WHEN "Sep" THEN 9 WHEN "Oct" THEN 10
WHEN "Nov" THEN 11 WHEN "Dec" THEN 12
END ASC
