How can i display the result with date sequence via using SQL in table-transformer.

[Oujy_lala]

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

[Nikita Kamai · Accepted solution]

Hi,

Seems you are referring to our Table Filter, Charts & Spreadsheets app.

As I can see, you are ordering the JOINED result only by the T1 Labels column, which leads to the incorrect output.

Please try the following query instead:

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) WHERE T1.'Labels'!="Total"

GROUP BY T1.'Labels', T1.'Count'

ORDER BY CAST('20'||SUBSTR('Labels',4,2) AS INTEGER),

CASE SUBSTR('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

This should order the JOINED result in full.

Best wishes,
Nikita

[Oujy_lala]

Hi Nikita,

Thank you so much, really appreciate your help! Now the order is correct.