How to accomplish Inner query?

[sekar211]

I have a report in which i have to calculate the metric first (some Max level) and then display only those records which satisfy a condition (some Id=1).
I have query given below:

select a11.fldApprovalId fldApprovalId,
a11.fldResponsibilityLevel fldResponsibilityLevel,
max(a11.fldRuleId) WJXBFS1
from uv_AdjustmentTasklist a11
where a11.fldTaskStatusId = 1
group by a11.fldApprovalId,
a11.fldResponsibilityLevel

in this case i am getting both fldresponsibiltylevel and max(fldruleid) same because fldtaskstatusId=1 is applied first and then the metric is being calculated.

how can achieve the results with first caluclating the max(fldruleid) and then display the records with fldtaskstatusid=1

Thank you,

 

[entaroadun]

Metric set qualification. Create a fldRuleId metric with a fldTaskStatusId filter embedded within it. Then use the fldRuleId metric in a filter for your report. Set the output level of that metric set qualification to fldApprovalId and fldResponsibilityLevel.

 

[sekar211]

given below is the sql for the above mentioned report with some more fields added to it.The problem is i am getting same current responsibilty level and highest responsiblity level.Can you please help me in figuring out where the problem might be?

current responsibilty level is obtained by the condition taskstatusid=1.
Higest approval level is obtained by calculating the max(responsibilty level) for a given approval id.

Tables Accessed:
uv_AdjustDetails
uv_PayAdjust
uv_AdjustmentTasklist


SQL Statements:

Pass0 - Duration: 0:00:00.90
select a12.fldTrackingNum fldTrackingNum,
a13.fldResponsibilityLevel fldResponsibilityLevel,
sum(a11.fldTotalAdjAmt) WJXBFS1
into #ZZT3C0701J8MD000
from uv_AdjustDetails a11
join uv_PayAdjust a12
on (a11.fldRecNum = a12.fldRecNum)
join uv_AdjustmentTasklist a13
on (a12.fldApprovalId = a13.fldApprovalId)
where a13.fldTaskStatusId = 1
group by a12.fldTrackingNum,
a13.fldResponsibilityLevel

Pass1 - Duration: 0:00:01.59
select a12.fldTrackingNum fldTrackingNum,
a11.fldResponsibilityLevel fldResponsibilityLevel,
max(a11.fldRuleId) WJXBFS1
into #ZZT3C0701J8MD001
from uv_AdjustmentTasklist a11
join uv_PayAdjust a12
on (a11.fldApprovalId = a12.fldApprovalId)
group by a12.fldTrackingNum,
a11.fldResponsibilityLevel

Pass2 - Duration: 0:00:00.12
select pa1.fldTrackingNum fldTrackingNum,
pa1.fldResponsibilityLevel fldResponsibilityLevel,
pa1.WJXBFS1 WJXBFS1,
pa2.WJXBFS1 WJXBFS2
from #ZZT3C0701J8MD000 pa1
join #ZZT3C0701J8MD001 pa2
on (pa1.fldResponsibilityLevel = pa2.fldResponsibilityLevel and
pa1.fldTrackingNum = pa2.fldTrackingNum)

Pass3 - Duration: 0:00:00.09
drop table #ZZT3C0701J8MD000

Pass4 - Duration: 0:00:00.06
drop table #ZZT3C0701J8MD001