Case Statement to pull several queries together

[J Newelle Horn]

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 Type

Issue key

Issue id

Summary

Status

Assignee

Custom field (Resolver Group)

Created

Updated

Custom field (Last Comment)

Due Date

Custom field (Report Group)

Custom field (Domain)

Custom field (Client)

Custom field (Client)

Report

IMPREP-68

1588144

BHP IFP/MA Paid After Term Report [Internal]

Test UAT

pevans@evolenthealth.com

Claims - Commercial

5/19/2021 13:18

11/12/2021 9:14

Phillip 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:00

Group B

Claims -> Payment Integrity

Bright Health IFP - Individual, Family, Group (BRIF)

Bright Health Medicare Advantage (MABR)

Report

IMPREP-52

1588109

BHP IFP/MA COB Miscellaneous Reports

Build

pevans@evolenthealth.com

Claims - Commercial

5/19/2021 12:52

1/6/2022 11:20

Phillip 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:00

Group C

Claims -> Payment Integrity

Bright Health IFP - Individual, Family, Group (BRIF)

Bright Health Medicare Advantage (MABR)

Report

IMPREP-104

1603640

BHP IFP/MA Monthly PI Dashboard

Build

pevans@evolenthealth.com

Claims - Commercial

6/16/2021 10:44

12/10/2021 9:15

Phillip 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:00

Group C

Claims -> 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 Type

Issue key

Issue id

Summary

Status

Assignee

Custom field (Resolver Group)

Created

Updated

Custom field (Last Comment)

Due Date

Custom field (Report Group)

Custom field (Domain)

Custom field (Client)

Custom field (Client)

Reviewer

Report

IMPREP-101

1590221

BHP IFP/MA Paid Claims Aging

Test UAT

swoods@evolenthealth.com

Client Analytics

5/24/2021 13:27

1/10/2022 12:32

Paul 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:00

Group B

Claims -> Claims Ops

Bright Health IFP - Individual, Family, Group (BRIF)

Bright Health Medicare Advantage (MABR)

Paul Kim

Report

IMPREP-68

1588144

BHP IFP/MA Paid After Term Report [Internal]

Test UAT

pevans@evolenthealth.com

Claims - Commercial

5/19/2021 13:18

11/12/2021 9:14

Phillip 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:00

Group B

Claims -> Payment Integrity

Bright Health IFP - Individual, Family, Group (BRIF)

Bright Health Medicare Advantage (MABR)

Newelle Horn

Report

IMPREP-50

1588106

BHP IFP Retro Adds/Retro Deletes/Recouped or Reprocessed Report(s) [Internal]

Test UAT

pakim@evolenthealth.com

Payment Integrity

5/19/2021 12:48

12/6/2021 12:11

Paul KimThu Oct 07 11:29:37 CDT 2021Duplicate ticket, linked to HPSIMP-6264

Group B

Claims -> Claims Ops

Bright Health IFP - Individual, Family, Group (BRIF)

Paul Kim

Report

IMPREP-95

1589362

BHP IFP Claim TAT SLA [Internal]

Solution Scoping

rtcampbell@evolenthealth.com

Performance Analytics

5/21/2021 11:12

12/6/2021 10:21

Paul 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:00

Group C

Claims -> Claims Ops

Bright Health IFP - Individual, Family, Group (BRIF)

Bright Health Medicare Advantage (MABR)

Paul Kim

Report

IMPREP-109

1627468

BHP MA Claim TAT SLA [Internal]

Solution Scoping

rtcampbell@evolenthealth.com

Performance Analytics

7/29/2021 14:42

1/11/2022 10:10

Paul KimTue Jan 11 10:10:53 CST 2022[~rtcampbell@evolenthealth.com] Any updates on this (and the IFP) Internal report?

2/10/2022 6:00

Group C

Claims -> Claims Ops

Bright Health Medicare Advantage (MABR)

Paul Kim

Report

IMPREP-52

1588109

BHP IFP/MA COB Miscellaneous Reports

Build

pevans@evolenthealth.com

Claims - Commercial

5/19/2021 12:52

1/6/2022 11:20

Phillip 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:00

Group C

Claims -> Payment Integrity

Bright Health IFP - Individual, Family, Group (BRIF)

Bright Health Medicare Advantage (MABR)

Newelle Horn

Report

IMPREP-104

1603640

BHP IFP/MA Monthly PI Dashboard

Build

pevans@evolenthealth.com

Claims - Commercial

6/16/2021 10:44

12/10/2021 9:15

Phillip 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:00

Group C

Claims -> Payment Integrity

Bright Health IFP - Individual, Family, Group (BRIF)

Bright Health Medicare Advantage (MABR)

Newelle Horn

[Nic Brough -Adaptavist- · Accepted solution]

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.

[J Newelle Horn]

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"

[Nic Brough -Adaptavist-]

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.

[J Newelle Horn]

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