Oracle ranking, Team ranking report

Personal ranking:

View plaincopy to clipboardprint?
Select a. onesumweight, A. wdonesumweight, A. zdonesumweight, A. operatorid, A. Operator, A. COMPID, A. comp,
Row_number () over (partition by A. COMPID, A. Comp order by A. onesumweight DESC) as grpm
, A. totalcust, A. betweencust, A. newcust
From
(
Select sum (. totalcust) as totalcust, sum (. betweencust) as betweencust, sum (. newcust) as newcust, sum (. onesumweight) as onesumweight, sum (. wdonesumweight) as wdonesumweight, sum (zdonesumweight) as zdonesumweight,. operatorid,. operator,. COMPID,. comp from
(

Select 0 as totalcust, 0 as betweencust, 0 as newcust, sum (L. weight) as onesumweight, 0 as wdonesumweight, 0 as zdonesumweight, M. operatorid, M. operator, M. COMPID, M. comp from es_contractline L
Join es_contract m on L. mainid = M. ID and L. isdel = 0 and M. isdel = 0
Where 1 = 1
Group by M. operatorid, M. Operator, M. COMPID, M. Comp
Union all

Select 0 as totalcust, 0 as betweencust, 0 as newcust, 0 as onesumweight, sum (L. weight) as wdonesumweight, 0 as zdonesumweight, M. operatorid, M. operator, M. COMPID, M. comp from es_contractline L
Join es_contract m on L. mainid = M. ID and L. isdel = 0 and M. isdel = 0
Where M. conproperty = 18002 and 2 = 2

Group by M. operatorid, M. Operator, M. COMPID, M. Comp
Union all

Select 0 as totalcust, 0 as betweencust, 0 as newcust, 0 as onesumweight, 0 as wdonesumweight, sum (L. weight) as zdonesumweight, M. operatorid, M. operator, M. COMPID, M. comp from es_contractline L
Join es_contract m on L. mainid = M. ID and L. isdel = 0 and M. isdel = 0
Left join eb_corp C on M. corpid = C. ID
Where C. isdel = 0 and C. defaultcustsort = 93002 and 3 = 3
Group by M. operatorid, M. Operator, M. COMPID, M. Comp

Union all

Select C. totalcust, C. betweencust, C. newcust, 0 as onesumweight, 0 as wdonesumweight, 0 as zdonesumweight, C. operatorid, C. operator, C. COMPID, C. comp from
(
Select sum (A. Flag) as totalcust, 0 as betweencust, 0 as newcust, A. operatorid, A. Operator, A. COMPID, A. comp from
(
Select 1 as flag, A. operatorid, A. Operator, A. COMPID, A. comp, A. corpid, A. Corp from es_contract
Left join eb_corp B on A. corpid = B. ID
Where a. isdel = 0 and B. isdel = 0 and 6 = 6
Group by A. operatorid, A. Operator, A. COMPID, A. comp, A. corpid, A. Corp
) A group by A. operatorid, A. Operator, A. COMPID, A. comp

Union all

Select 0 as totalcust, sum (A. Flag) as betweencust, 0 as newcust, A. operatorid, A. Operator, A. COMPID, A. comp from
(
Select 1 as flag, A. operatorid, A. Operator, A. COMPID, A. comp, A. corpid, A. Corp from es_contract
Left join eb_corp B on A. corpid = B. ID
Where a. isdel = 0 and B. isdel = 0 and 4 = 4
Group by A. operatorid, A. Operator, A. COMPID, A. comp, A. corpid, A. Corp
) A group by A. operatorid, A. Operator, A. COMPID, A. comp

Union all

Select 0 as totalcust, 0 as betweencust, sum (A. Flag) as newcust, A. operatorid, A. Operator, A. COMPID, A. comp from
(
Select 1 as flag, A. operatorid, A. Operator, A. COMPID, A. comp, A. corpid, A. Corp from es_contract
Left join eb_corp B on A. corpid = B. ID
Where a. isdel = 0 and B. isdel = 0 and 5 = 5
Group by A. operatorid, A. Operator, A. COMPID, A. comp, A. corpid, A. Corp
) A group by A. operatorid, A. Operator, A. COMPID, A. comp
) C

)
Join eb_emp B on A. operatorid = B. ID
And B. groupname! = 'Warehouse delivery amount' and B. groupname! = 'Logistics'
Where B. isdel = 0
Group by A. operatorid, A. Operator, A. COMPID, A. comp
Order by A. COMPID, sum (A. onesumweight) DESC
)



<Field name = "onesumweight" caption = "personal sales"/>
<Field name = "wdonesumweight" caption = "personal tone"/>
<Field name = "zdonesumweight" caption = "personal terminal"/>
<Field name = "operatorid" caption = "salesman Id"/>
<Field name = "operator" caption = "salesman"/>
<Field name = "COMPID" caption = "branch ID"/>
<Field name = "comp" caption = ""/>
<Field name = "grpm" caption = "ranking"/>
<Field name = "totalcust" caption = "Total number of operating customers"/>
<Field name = "betweencust" caption = "number of customers operated in this period"/>
<Field name = "newcust" caption = "Number of new developers"/>
Select a. onesumweight, A. wdonesumweight, A. zdonesumweight, A. operatorid, A. Operator, A. COMPID, A. comp,
Row_number () over (partition by A. COMPID, A. Comp order by A. onesumweight DESC) as grpm
, A. totalcust, A. betweencust, A. newcust
From
(
Select sum (. totalcust) as totalcust, sum (. betweencust) as betweencust, sum (. newcust) as newcust, sum (. onesumweight) as onesumweight, sum (. wdonesumweight) as wdonesumweight, sum (zdonesumweight) as zdonesumweight,. operatorid,. operator,. COMPID,. comp from
(

Select 0 as totalcust, 0 as betweencust, 0 as newcust, sum (L. weight) as onesumweight, 0 as wdonesumweight, 0 as zdonesumweight, M. operatorid, M. operator, M. COMPID, M. comp from es_contractline L
Join es_contract m on L. mainid = M. ID and L. isdel = 0 and M. isdel = 0
Where 1 = 1
Group by M. operatorid, M. Operator, M. COMPID, M. Comp
Union all

Select 0 as totalcust, 0 as betweencust, 0 as newcust, 0 as onesumweight, sum (L. weight) as wdonesumweight, 0 as zdonesumweight, M. operatorid, M. operator, M. COMPID, M. comp from es_contractline L
Join es_contract m on L. mainid = M. ID and L. isdel = 0 and M. isdel = 0
Where M. conproperty = 18002 and 2 = 2

Group by M. operatorid, M. Operator, M. COMPID, M. Comp
Union all

Select 0 as totalcust, 0 as betweencust, 0 as newcust, 0 as onesumweight, 0 as wdonesumweight, sum (L. weight) as zdonesumweight, M. operatorid, M. operator, M. COMPID, M. comp from es_contractline L
Join es_contract m on L. mainid = M. ID and L. isdel = 0 and M. isdel = 0
Left join eb_corp C on M. corpid = C. ID
Where C. isdel = 0 and C. defaultcustsort = 93002 and 3 = 3
Group by M. operatorid, M. Operator, M. COMPID, M. Comp

Union all

Select C. totalcust, C. betweencust, C. newcust, 0 as onesumweight, 0 as wdonesumweight, 0 as zdonesumweight, C. operatorid, C. operator, C. COMPID, C. comp from
(
Select sum (A. Flag) as totalcust, 0 as betweencust, 0 as newcust, A. operatorid, A. Operator, A. COMPID, A. comp from
(
Select 1 as flag, A. operatorid, A. Operator, A. COMPID, A. comp, A. corpid, A. Corp from es_contract
Left join eb_corp B on A. corpid = B. ID
Where a. isdel = 0 and B. isdel = 0 and 6 = 6
Group by A. operatorid, A. Operator, A. COMPID, A. comp, A. corpid, A. Corp
) A group by A. operatorid, A. Operator, A. COMPID, A. comp

Union all

Select 0 as totalcust, sum (A. Flag) as betweencust, 0 as newcust, A. operatorid, A. Operator, A. COMPID, A. comp from
(
Select 1 as flag, A. operatorid, A. Operator, A. COMPID, A. comp, A. corpid, A. Corp from es_contract
Left join eb_corp B on A. corpid = B. ID
Where a. isdel = 0 and B. isdel = 0 and 4 = 4
Group by A. operatorid, A. Operator, A. COMPID, A. comp, A. corpid, A. Corp
) A group by A. operatorid, A. Operator, A. COMPID, A. comp

Union all

Select 0 as totalcust, 0 as betweencust, sum (A. Flag) as newcust, A. operatorid, A. Operator, A. COMPID, A. comp from
(
Select 1 as flag, A. operatorid, A. Operator, A. COMPID, A. comp, A. corpid, A. Corp from es_contract
Left join eb_corp B on A. corpid = B. ID
Where a. isdel = 0 and B. isdel = 0 and 5 = 5
Group by A. operatorid, A. Operator, A. COMPID, A. comp, A. corpid, A. Corp
) A group by A. operatorid, A. Operator, A. COMPID, A. comp
) C

)
Join eb_emp B on A. operatorid = B. ID
And B. groupname! = 'Warehouse delivery amount' and B. groupname! = 'Logistics'
Where B. isdel = 0
Group by A. operatorid, A. Operator, A. COMPID, A. comp
Order by A. COMPID, sum (A. onesumweight) DESC
)

 

<Field name = "onesumweight" caption = "personal sales"/>
<Field name = "wdonesumweight" caption = "personal tone"/>
<Field name = "zdonesumweight" caption = "personal terminal"/>
<Field name = "operatorid" caption = "salesman Id"/>
<Field name = "operator" caption = "salesman"/>
<Field name = "COMPID" caption = "branch ID"/>
<Field name = "comp" caption = ""/>
<Field name = "grpm" caption = "ranking"/>
<Field name = "totalcust" caption = "Total number of operating customers"/>
<Field name = "betweencust" caption = "number of customers operated in this period"/>
<Field name = "newcust" caption = "Number of new developers"/>

Team ranking:

View plaincopy to clipboardprint?
Select a. onesumweight, A. wdonesumweight, A. zdonesumweight, A. operatorid, A. Operator, A. COMPID, A. comp, A. teamid, A. Team,
A. teamsumweight,. teamwdsumweight,. teamzdsumweight, round (. teamzdsumweight/. teamsumweight, 3) * 100 | '%' as ZB, round (. teamwdsumweight/. teamsumweight, 3) * 100 | '%' as LB,. grpm,. teampm
From
(
Select a3.onesumweight, a3.wdonesumweight, a3.zdonesumweight, a3.teamsumweight, a3.operatorid, a3.operator, a3.compid, a3.comp, a3.teamid, a3.team,
Sum (a3.wdonesumweight) over (partition by a3.teamid, a3.team order by a3.compid DESC) as teamwdsumweight,
Sum (a3.zdonesumweight) over (partition by a3.teamid, a3.team order by a3.compid DESC) as teamzdsumweight,

Row_number () over (partition by a3.compid, a3.comp, a3.teamid, a3.team order by a3.onesumweight DESC) as grpm,
A3.teampm
From
(
Select sum (a2.onesumweight) as onesumweight, sum (inflow) as wdonesumweight, sum (a2.zdonesumweight) as zdonesumweight, sum (a2.teamsumweight) as teamsumweight,
A2.operatorid, a2.operator, a2.compid, a2.comp, a2.teamid, a2.team, b2.teampm
From
(

Select sum (A. onesumweight) as onesumweight, sum (A. wdonesumweight) as wdonesumweight, sum (A. zdonesumweight) as zdonesumweight, A. teamsumweight,
A. operatorid, A. Operator, A. COMPID, A. comp, A. teamid, A. Team
From (

Select 0 as onesumweight, 0 as wdonesumweight, 0 as zdonesumweight,
Sum (L. Weight) over (partition by Z. teamid, Z. Team order by M. compid desc) as teamsumweight,
M. operatorid, M. Operator, M. COMPID, M. comp, Z. teamid, Z. Team
From es_contractline L
Join es_contract m on L. mainid = M. ID and L. isdel = 0 and M. isdel = 0
Left join (
Select X. empid, Y. ID as teamid, Y. teamname as Team
From eb_team_emp x
Inner join eb_team y
On X. teamid = Y. ID
Where X. isdel = 0 and Y. isdel = 0
) Z
On M. operatorid = Z. empid
Where 1 = 1
) A group by A. operatorid, A. Operator, A. COMPID, A. comp, A. teamid, A. Team, A. teamsumweight

Union all

Select sum (a1.onesumweight) as onesumweight, sum (bytes) as wdonesumweight, sum (a1.zdonesumweight) as zdonesumweight, sum (a1.teamsumweight) as teamsumweight,
A1.operatorid, a1.operator, a1.compid, a1.comp, Z. teamid, Z. Team
From
(
-- Total sales
Select sum (L. weight) as onesumweight, 0 as wdonesumweight, 0 as zdonesumweight, 0 as teamsumweight, M. operatorid, M. Operator, M. COMPID, M. comp from es_contractline L
Join es_contract m on L. mainid = M. ID and L. isdel = 0 and M. isdel = 0
Where 2 = 2
Group by M. operatorid, M. Operator, M. COMPID, M. Comp

Union all

-- Outsourced sales volume
Select 0 as onesumweight, sum (L. weight) as wdonesumweight, 0 as zdonesumweight, 0 as teamsumweight, M. operatorid, M. Operator, M. COMPID, M. comp from es_contractline L
Join es_contract m on L. mainid = M. ID and L. isdel = 0 and M. isdel = 0
Where M. conproperty = 18002 and 3 = 3
Group by M. operatorid, M. Operator, M. COMPID, M. Comp

Union all
-- Terminal sales
Select 0 as onesumweight, 0 as wdonesumweight, sum (L. weight) as zdonesumweight, 0 as teamsumweight, M. operatorid, M. Operator, M. COMPID, M. comp from es_contractline L
Join es_contract m on L. mainid = M. ID and L. isdel = 0 and M. isdel = 0
Left join eb_corp C on M. corpid = C. ID
Where C. isdel = 0 and C. defaultcustsort = 93002 and 4 = 4
Group by M. operatorid, M. Operator, M. COMPID, M. Comp

) A1
Left join (
Select X. empid, Y. ID as teamid, Y. teamname as Team
From eb_team_emp x
Inner join eb_team y
On X. teamid = Y. ID
Where X. isdel = 0 and Y. isdel = 0
) Z
On a1.operatorid = Z. empid
Group by a1.operatorid, a1.operator, a1.compid, a1.comp, Z. teamid, Z. Team


) A2
Left join
(
Select row_number () over (partition by M. COMPID, M. Comp order by sum (L. Weight) DESC) as teampm,
Z. teamid, Z. Team
From es_contractline L
Join es_contract m on L. mainid = M. ID and L. isdel = 0 and M. isdel = 0
Left join (
Select X. empid, Y. ID as teamid, Y. teamname as Team
From eb_team_emp x
Inner join eb_team y
On X. teamid = Y. ID
Where X. isdel = 0 and Y. isdel = 0
) Z
On M. operatorid = Z. empid
Where 5 = 5
Group by Z. teamid, Z. Team, M. COMPID, M. Comp
) B2 on a2.teamid = b2.teamid

Group by a2.compid, a2.comp, a2.teamid, a2.team, a2.operatorid, a2.operator, b2.teampm


) A3

)

Join eb_emp B on A. operatorid = B. ID
Where B. isdel = 0 and 6 = 6
Order by A. COMPID, A. teampm, A. grpm




<Field name = "onesumweight" caption = "personal sales"/>
<Field name = "wdonesumweight" caption = "personal tone"/>
<Field name = "zdonesumweight" caption = "personal terminal"/>
<Field name = "operatorid" caption = "salesman Id"/>
<Field name = "operator" caption = "salesman"/>
<Field name = "COMPID" caption = "branch ID"/>
<Field name = "comp" caption = ""/>
<Field name = "teamid" caption = "Team ID"/>
<Field name = "team" caption = "team"/>
<Field name = "teamsumweight" caption = "team sales"/>
<Field name = "teamwdsumweight" caption = "team calls"/>
<Field name = "teamzdsumweight" caption = "Team terminal"/>
<Field name = "ZB" caption = "final ratio"/>
<Field name = "LB" caption = "linbi"/>
<Field name = "grpm" caption = "personal ranking"/>
<Field name = "teampm" caption = "Team ranking"/>

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/xiao_jun_0820/archive/2010/04/02/5445666.aspx