Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,458,145
Community Members
 
Community Events
176
Community Groups

How to write sql to join same tabl

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.

1 answer

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:

Tue 13-1.png

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.

Tue 13-2.png
Hope that it helps your case.

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'

Screen Shot 2023-01-10 at 7.34.32 AM.png

 

 

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'

 

 

 

Screen Shot 2023-01-10 at 7.26.11 AM.png 

Like Stiltsoft support likes this

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' 

Like Stiltsoft support likes this

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.

Screen Shot 2023-01-10 at 7.26.11 AM.png

@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.

Tue 14-1.png

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

Tue 14-2.png
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'

Tue 14-3.png

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'

Tue 14-4.png

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'


Tue 14-5.png

Hope it helps your case. 

Like Stiltsoft support likes this

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?

 

Screen Shot 2023-01-10 at 9.11.46 PM.png

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:

Wed 7-1.png

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.

Wed 7-2.png

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'

Wed 7-3.png

Hope it helps.

Like Stiltsoft support likes this

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?

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'

Like Stiltsoft support likes this

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'
)
)

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events