Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Case Statement to pull several queries together

I tried to enter this before but don't see it so doing it again.

I have individual queries that are for 4 individuals who are breakout tickets by Domains.  I run them separately for each but wanted to see if JQL can do a case statement to allow for them all to run and return a Unique Value based on the query.

Here is on of the individual queries and the results.   Below the first results is a combo with the combined Variable of Reviewer added which needs to be some sort of a case statement.   

 

Below its set for me but for 

Individual Query
project = IMPREP AND (status not in ("Prod Report Generation and QA", Closed) AND "Report Group" in ("Group A", "Group A") AND Domain in (Capitation, "Capitation and Delegation", "Provider Data Management", "Provider Data Management (PDM)") OR status not in ("Prod Report Generation and QA", Closed) AND "Report Group" in ("Group B") AND Domain in (Capitation, "Capitation and Delegation", "Provider Data Management", "Provider Data Management (PDM)") OR status not in ("Test UAT", "Prod Report Generation and QA", Closed) AND "Report Group" in ("Group C") AND Domain in (Capitation, "Capitation and Delegation", "Provider Data Management", "Provider Data Management (PDM)") OR status not in ("Solution Scoping", Build, "Build In Progress", "Test UAT", Closed) AND "Report Group" in ("Group D") AND Domain in (Capitation, "Capitation and Delegation", "Provider Data Management", "Provider Data Management (PDM)") OR status not in ("Requirements and Gap Analysis", "Solution Scoping", Build, "Build In Progress", "Test UAT", Closed) AND "Report Group" in ("Group E") AND Domain in (Capitation, "Capitation and Delegation", "Provider Data Management", "Provider Data Management (PDM)")) ORDER BY "Report Group" ASC, due ASC

 

Individual Query RESULTS

Issue TypeIssue keyIssue idSummaryStatusAssigneeCustom field (Resolver Group)CreatedUpdatedCustom field (Last Comment)Due DateCustom field (Report Group)Custom field (Domain)Custom field (Client)Custom field (Client)
ReportIMPREP-681588144BHP IFP/MA Paid After Term Report [Internal]Test UATpevans@evolenthealth.comClaims - Commercial5/19/2021 13:1811/12/2021 9:14Phillip EvansFri Nov 12 09:14:18 CST 2021[~sgarrett@evolenthealth.com] and [~pakim@evolenthealth.com], I moved the ticket to "Test UAT." we can test the report with Bright data once there is live data to test.1/31/2022 6:00Group BClaims -> Payment Integrity Bright Health IFP - Individual, Family, Group (BRIF)Bright Health Medicare Advantage (MABR)
ReportIMPREP-521588109BHP IFP/MA COB Miscellaneous ReportsBuildpevans@evolenthealth.comClaims - Commercial5/19/2021 12:521/6/2022 11:20Phillip EvansTue Jan 04 11:01:26 CST 2022[~pakim@evolenthealth.com], there is no paid claims data yet, nor have we received the list of members identified has having other coverage yet.2/15/2022 6:00Group CClaims -> Payment Integrity Bright Health IFP - Individual, Family, Group (BRIF)Bright Health Medicare Advantage (MABR)
ReportIMPREP-1041603640BHP IFP/MA Monthly PI DashboardBuildpevans@evolenthealth.comClaims - Commercial6/16/2021 10:4412/10/2021 9:15Phillip EvansFri Dec 03 16:33:49 CST 2021HI [~rtcampbell@evolenthealth.com], 



 



I have attached the version of the sample dashboard that we reviewed a few weeks back with the Bright team.  They received a copy of this report to review and I have not heard any additional feedback from what was discussed during the call.  This is a standardized report we produce monthly, so the report is built but we won't have any live data to populate until overpayment identifications are made.



[^PI.CDCC SAMPLE Dashboard YYYYMM.xlsx]
3/23/2022 5:00Group CClaims -> Payment Integrity Bright Health IFP - Individual, Family, Group (BRIF)Bright Health Medicare Advantage (MABR)

Requested Reviewer results - just used Paul's output and changed a few for me as an example as I'm not assigned to Payment Integrity.

 

Issue TypeIssue keyIssue idSummaryStatusAssigneeCustom field (Resolver Group)CreatedUpdatedCustom field (Last Comment)Due DateCustom field (Report Group)Custom field (Domain)Custom field (Client)Custom field (Client)Reviewer
ReportIMPREP-1011590221BHP IFP/MA Paid Claims AgingTest UATswoods@evolenthealth.comClient Analytics5/24/2021 13:271/10/2022 12:32Paul KimMon Jan 10 10:06:47 CST 2022Per Rhett Campbell, claims data will be available in PDW data sets as of Thursday (1/13) or Friday (1/14).1/14/2022 6:00Group BClaims -> Claims OpsBright Health IFP - Individual, Family, Group (BRIF)Bright Health Medicare Advantage (MABR)Paul Kim
ReportIMPREP-681588144BHP IFP/MA Paid After Term Report [Internal]Test UATpevans@evolenthealth.comClaims - Commercial5/19/2021 13:1811/12/2021 9:14Phillip EvansFri Nov 12 09:14:18 CST 2021[~sgarrett@evolenthealth.com] and [~pakim@evolenthealth.com], I moved the ticket to "Test UAT." we can test the report with Bright data once there is live data to test.1/31/2022 6:00Group BClaims -> Payment Integrity Bright Health IFP - Individual, Family, Group (BRIF)Bright Health Medicare Advantage (MABR)Newelle Horn
ReportIMPREP-501588106BHP IFP Retro Adds/Retro Deletes/Recouped or Reprocessed Report(s) [Internal]Test UATpakim@evolenthealth.comPayment Integrity5/19/2021 12:4812/6/2021 12:11Paul KimThu Oct 07 11:29:37 CDT 2021Duplicate ticket, linked to HPSIMP-6264 Group BClaims -> Claims OpsBright Health IFP - Individual, Family, Group (BRIF) Paul Kim
ReportIMPREP-951589362BHP IFP Claim TAT SLA [Internal]Solution Scopingrtcampbell@evolenthealth.comPerformance Analytics5/21/2021 11:1212/6/2021 10:21Paul KimMon Dec 06 10:21:34 CST 2021[~rtcampbell@evolenthealth.com] can you check in with Lisa A. to see if she needs tickets for the Claim TAT SLA reports (IFP and MA)?2/10/2022 6:00Group CClaims -> Claims OpsBright Health IFP - Individual, Family, Group (BRIF)Bright Health Medicare Advantage (MABR)Paul Kim
ReportIMPREP-1091627468BHP MA Claim TAT SLA [Internal]Solution Scopingrtcampbell@evolenthealth.comPerformance Analytics7/29/2021 14:421/11/2022 10:10Paul KimTue Jan 11 10:10:53 CST 2022[~rtcampbell@evolenthealth.com] Any updates on this (and the IFP) Internal report?2/10/2022 6:00Group CClaims -> Claims OpsBright Health Medicare Advantage (MABR) Paul Kim
ReportIMPREP-521588109BHP IFP/MA COB Miscellaneous ReportsBuildpevans@evolenthealth.comClaims - Commercial5/19/2021 12:521/6/2022 11:20Phillip EvansTue Jan 04 11:01:26 CST 2022[~pakim@evolenthealth.com], there is no paid claims data yet, nor have we received the list of members identified has having other coverage yet.2/15/2022 6:00Group CClaims -> Payment Integrity Bright Health IFP - Individual, Family, Group (BRIF)Bright Health Medicare Advantage (MABR)Newelle Horn
ReportIMPREP-1041603640BHP IFP/MA Monthly PI DashboardBuildpevans@evolenthealth.comClaims - Commercial6/16/2021 10:4412/10/2021 9:15Phillip EvansFri Dec 03 16:33:49 CST 2021HI [~rtcampbell@evolenthealth.com], 



 



I have attached the version of the sample dashboard that we reviewed a few weeks back with the Bright team.  They received a copy of this report to review and I have not heard any additional feedback from what was discussed during the call.  This is a standardized report we produce monthly, so the report is built but we won't have any live data to populate until overpayment identifications are made.



[^PI.CDCC SAMPLE Dashboard YYYYMM.xlsx]
3/23/2022 5:00Group CClaims -> Payment Integrity Bright Health IFP - Individual, Family, Group (BRIF)Bright Health Medicare Advantage (MABR)Newelle Horn

 

1 answer

1 accepted

1 vote
Answer accepted

That's a very complex query, I'd want to try to simplify it as much as possible, just so people can try to work out what it is doing by reading it without having to spend an hour disassembling it.

It is also unlikely to be doing what you want because you have not defined it clearly.  This is not a problem if you are joining clauses with and or or throughout the query, but most people fail to understand what mixing and and or in the headline query and often read them wrong.

A and B or C 

is totally different to 

C or B and A

despite the clauses being related the same way (A and B, and B or C).

You need to be explicit with your parentheses, or Jira will simply read left-to-right, and I very much doubt that's what you want in this query.

Hi Nic - I struggled with how to represent this - the query isn't the issue - what I need is to create a literal at the end of it to state the person's name.  I created 4 queries like the one above and it pulls the data for that person based on the status listed (all the same for each query) and the domains - which are unique to each of the 4 people on the team.  

We broke it out this way to ensure everyone on the team had roughly the same amount of work to do so used the domains as a way to break it out.  

I can combine it using OR statements between the 4 Different queries but I really need the Reviewer to be part of the query output.  Its not a field in JIRA so have to insert it somehow like in SQL when you do the select statement of 

"Newelle" as "Reviewer"

Ok, well "creating a literal" is nothing to do with JQL.

JQL finds issues.  That's all it does, it doesn't show them to you, and it doesn't choose what is displayed.  It just hands back a list of issues.

You'll need to look at whatever you are reporting the list in, as that's the thing that determines what you are seeing.

If the reviewer is not a field in Jira, then there's no way that anything in Jira can report on it.

That was what I was afraid of - so short of creating a field in Jira for this I'm just going to have to do it in Excel - thanks for the review - appreciate it

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you