How to write sql to join same tabl

[abhilocalcircles]

Hi,

I am trying to create report using Table transformer using JIRA as source. I would like to display no of tickets those have status='blocked' along with other columns using below query

SELECT SUM(T1.'Story Points'::integer) as 'Days Needed'
,count(T1.'Assignee') NoOfTickets
,(select count(T1.'Assignee') from T1 where T1.'Status' LIKE "%BLOCK%") as 'Blocked Tickets'
, T1.'Assignee' as 'Assignee'
,CEIL(DATEDIFF(DAY,NOW(),"17-01-2023")) as 'Calendars Days Left'
FROM T1
group by T1.'Assignee'

But, this show blocked tickets for all assignee same if one of them has it. I am not able to corelate assignee to sub query from T1.Assignee table. As only one of the assignee has blocked status tickets but result shows same value for assignees.

Please advise.

[Katerina Rudkovskaya [Stiltsoft]]

Hi @abhilocalcircles,

Seems that the issue comes from the inappropriate usage of the external and internal SELECT statements.

Unfortunately, you didn’t provide us with the screenshot of your original table, but I’ve recreated something similar myself:



Here is my SQL query:
SELECT 'Assignee',
SUM('Story Points'::integer) as 'Days Needed',
COUNT('Assignee') AS 'NoOfTickets',
CEIL(DATEDIFF(DAY,NOW(),"17-01-2023")) as 'Calendars Days Left'
FROM (
SELECT * FROM T*
WHERE T1.'Status' LIKE "%BLOCK%")
GROUP BY 'Assignee'

As you can see, the T1.’Column name’ functions are used only inside the internal SELECT. The external SELECT calls columns by their names only (without the T1. prefix).

In the internal SELECT we filter our table T1 by statuses, but after that we can’t refer to this table as T1 in the external SELECT anymore.


Hope that it helps your case.

[abhilocalcircles]

I am not sure if you got my requirements, I need to have no of blocked story points and no of blocked days as a column if an assignee has JIRA status as BLOCKED.

My current table looks like this.

SELECT SUM(T1.'Story Points'::integer) as 'Days Needed',count(T1.'Assignee') NoOfTickets
, T1.'Assignee'
,CEIL(DATEDIFF(DAY,NOW(),"17-01-2023")) as 'Calendars Days Left'
FROM T1
group by T1.'Assignee'

I need to add no of blocked tickets for each assignee if they have any and sum of story points called No of blocked days like below by adding two more columns the table:

Query:

SELECT SUM(T1.'Story Points'::integer) as 'Days Needed',count(T1.'Assignee') NoOfTickets
, (select count(T1.'Assignee') from T1 where T1.'Status' LIKE "%BLOCK%") as 'NoOfBlocked Tickets'
,(select SUM(T1.'Story Points'::integer) from T1 where T1.'Status' LIKE "%BLOCK%") as 'NoOfBlocked Days'
, T1.'Assignee'
,CEIL(DATEDIFF(DAY,NOW(),"17-01-2023")) as 'Calendars Days Left'
FROM T1
group by T1.'Assignee'

 

[Katerina Rudkovskaya [Stiltsoft]]

Hi @abhilocalcircles,

Please try to use the following SQL query in your case:

SELECT
SUM(T1.'Story Points'::integer) as 'Days Needed',
count(T1.'Assignee') 'NoOfTickets',
(SELECT COUNT(T1.'Assignee')
FROM T1
WHERE T1.'Status' LIKE "%BLOCK%"
GROUP BY T1.'Assignee') AS 'NoOfBlocked Tickets',
(SELECT SUM(T1.'Story Points'::integer)
FROM T1
WHERE T1.'Status' LIKE "%BLOCK%"
GROUP BY T1.'Assignee') AS 'NoOfBlocked Days',
T1.'Assignee',
CEIL(DATEDIFF(DAY,NOW(),"17-01-2023")) as 'Calendars Days Left'
FROM T1
group by T1.'Assignee' 

[abhilocalcircles]

I used this but the problem is same. In output only one user has blocked status but it shows for all as we don't have join for sub query assignee to outside query assignee. 

Result is still same like posted earlier. As you notice no blocked tickets and days should appear only for Souvik but also appearing for Hari and Smarak. They don;t have any blocked tickets.

Thank you in advance for helping. I am not sure how to write sub query joins on same table.

[Katerina Rudkovskaya [Stiltsoft]]

@abhilocalcircles, I could reproduce the issue and we'll investigate it. For now, please try this workaround if you're using Confluence Cloud:

Insert the Table Toolbox macro and place the two identical Jira tables inside its body.



Then go to the Table Toolbox macro and recreate the following structure inside its body:


If you have Server/Data Center, nest the Table Transformer macros directly on the page.

Now go to the upper internal Table Transformer macro, group assignees by all the tickets and count calendar days left:

SELECT 'Assignee',
COUNT('Assignee') AS 'NoOfTickets',
SUM('Story Points') AS 'NoOfDays',
CEIL(DATEDIFF(DAY,NOW(),"17-01-2023")) as 'Calendars Days Left'
FROM T*
GROUP BY 'Assignee'



Navigate to the lower internal Table Transformer macro and group the BLOCKED tickets as I’ve shown in my first reply:

SELECT 'Assignee',
COUNT('Assignee') AS 'NoOfBlockedTickets',
SUM('Story Points') AS 'NoOfBlockedDays'
FROM (
SELECT * FROM T*
WHERE T1.'Status' LIKE "%BLOCK%")
GROUP BY 'Assignee'



Now go to the external Table Transformer macro, select the order in which you want your columns to appear and join these two tables:

SELECT T1.'Assignee', T1.'NoOfTickets', T1.'NoOfDays',
T2.'NoOfBlockedTickets', T2.'NoOfBlockedDays',
T1.'Calendars Days Left'
FROM T1 LEFT JOIN T2 ON T1.'Assignee' = T2.'Assignee'

Hope it helps your case. 

[abhilocalcircles]

Thank you but I don't find Table Tool Box as Macro. Please advise how to find Table tool box?

May be I am not on cloud? I would request if you can help on the query part?

[Katerina Rudkovskaya [Stiltsoft]]

Hi abhilocalcircles,

If you can’t find the Table Toolbox macro in the macro browser, it means that you are on Server/Data Center.

As I’ve mentioned before, for these hostings you just nest macros directly on the page:



Other steps/queries are the same.

Don’t forget that you can nest macros in different combinations in general and not only regarding this case. For example, you can modify your table with the help of the Table Transformer macro and then use the Table Filter macro on top to filter your transformed data.

What concerns this case, I’ve consulted with our developers, and they came up with a more beautiful solution – you may also try it.



SELECT
SUM(T1.'Story Points'::integer) as 'Days Needed',
count(T1.'Assignee') 'NoOfTickets',
SUM(IF(T1.'Status' LIKE "%BLOCK%", 1, 0)) AS 'NoOfBlocked Tickets',
SUM(IF(T1.'Status' LIKE "%BLOCK%", 'Story Points'::integer, 0)) AS 'NoOfBlocked Days',
T1.'Assignee',
CEIL(DATEDIFF(DAY,NOW(),"17-01-2023")) as 'Calendars Days Left'
FROM T1
group by T1.'Assignee'



Hope it helps.

[abhilocalcircles]

Thank you your query works. Another problem I have is when i add below case statement

,CASE
WHEN 'NoOfBlocked Tickets' > 0 THEN FORMATWIKI("{cell:bgColor=red}" + 'NoOfBlocked Tickets' + "{cell}")
ELSE 'NoOfBlocked Tickets'
END as 'NoOfBlocked Tickets'

then my table representation loose formatting defined in table formatter. I wanted to represent all numbers as 1 decimal float.

How do I achieve same?

[Katerina Rudkovskaya [Stiltsoft]]

To preserve initial cells formatting, you need to use comma (,) instead of plus (+) inside the FORMATWIKI function:

CASE
WHEN 'NoOfBlocked Tickets' > 0 THEN FORMATWIKI("{cell:bgColor=red}" , 'NoOfBlocked Tickets' , "{cell}")
ELSE 'NoOfBlocked Tickets'
END as 'NoOfBlocked Tickets'

[abhilocalcircles]

Hello,

One quick question. I need variable on confluence page that I want to refer inside table formatter query. For an example, project mile stone date needs to be referred with in query. How can i do this?

SELECT 'Days Needed','Working days' FROM
(
SELECT 'Days Needed','Calendars Days Left',
'Calendars Days Left' - 2 * (('Calendars Days Left' / 7)::integer) -
CASE
WHEN 'Calendars Days Left' % 7 = 0 THEN 0
WHEN NOW()::Date->getDay() = 0 THEN 1
WHEN NOW()::Date->getDay() + 'Calendars Days Left' % 7 = 7 THEN 1
WHEN NOW()::Date->getDay() + 'Calendars Days Left' % 7 > 7 THEN 2
ELSE 0
END AS 'Working days'
FROM
(
SELECT SUM(T2.'Story Points'::integer) as 'Days Needed',count(T2.'Assignee') NoOfTickets, T2.'Assignee'
,CEIL(DATEDIFF(DAY,NOW(),"13-01-2023")) as 'Calendars Days Left'
FROM T2
group by T2.'Assignee'
)
)

[Katerina Rudkovskaya [Stiltsoft]]

Hi @abhilocalcircles,

Please check the second answer to this question: the workaround is to create an extra two-cell table with a header and your variable, then you may use it in the query.
For example, for our first case with one table and this SQL query (please refer to the second part of my answer on Jan 11, 2023):

SELECT
SUM(T1.'Story Points'::integer) as 'Days Needed',
count(T1.'Assignee') 'NoOfTickets',
SUM(IF(T1.'Status' LIKE "%BLOCK%", 1, 0)) AS 'NoOfBlocked Tickets',
SUM(IF(T1.'Status' LIKE "%BLOCK%", 'Story Points'::integer, 0)) AS 'NoOfBlocked Days',
T1.'Assignee',
CEIL(DATEDIFF(DAY,NOW(),"17-01-2023")) as 'Calendars Days Left'
FROM T1
group by T1.'Assignee'

the “CEIL(DATEDIFF(DAY,NOW(),"17-01-2023")) as 'Calendars Days Left'” expression will be changed to “CEIL(DATEDIFF(DAY,NOW(),(SELECT T2.'Variable' FROM T2))) as 'Calendars Days Left'” not to type in the date but pull it manually from the second table.
Thus, you may adjust this principle for other queries.

[abhilocalcircles]

Sorry not clear on 11 Jan answer. 

Can you please elaborate on how do I create new second table? If we go this route then I would have to insert this new table in all different places.

I have multiple table formatter on one confluence page and they all refer to this date. is there any way to create just simple variable and refer in all these table formatter?