Not getting the right result either due to grouping or join
-
I have a table that contains employee's information and spouse's information. They both have their own client_number. Both employees and spouses participate in several activities due to which their associated client_number repeats in the table.
I want to do two things;
1) count the number of all employees and all spouses separately.
2) count how many employees and spouses (separately) participate in specific number of activities. For this part the output should be as shown below.
For part 2, I need to group by client number to get their activities. Then I'm using cross tab in crystal to display the result.
The problem is that I'm not getting right result for part 2. "Over Partition grouping" is not grouping part 2 correctly by CLIENT_NUMBER . Any suggestion will be greatly appreciated. Following is my query.
WITH tb_main AS
(
SELECT CLIENT_NUMBER, -->is different for employees and spouses
ACTIVITY_TYPE, --> the activity they participate in.
PERSON_ID, --> 11 for employees and 99 for spouses.
EMPLOYEE_NUMBER, --> this is same as spouse number
SPOUSE_NUMBER, --> this is same as employee number
INCENTIVE_AMOUNT --> money they receive for participating in activity.
FROM tb_main_db
),
rpt_emp as
(SELECT e.EMPLOYEE_NUMBER, e.CLIENT_NUMBER emp_cl_number, -->Part 1 for employees
COUNT(e.CLIENT_NUMBER) OVER() emp_cl, --> Part 1 for employees
COUNT(DISTINCT e.CLIENT_NUMBER) OVER() dist_emp_cl, ---> Part 1 for employees
SUM(e.INCENTIVE_AMOUNT) OVER() emp_amt, ---> part 1 for emplyees
COUNT(e.CLIENT_NUMBER) OVER (PARTITION BY e.CLIENT_NUMBER) emp_group, ----> part 2 for employees
SUM(e.INCENTIVE_AMOUNT) OVER (PARTITION BY e.CLIENT_NUMBER) emp_inc_group, ----> part 2 for employees
COUNT(DISTINCT e.ACTIVITY_TYPE) OVER (PARTITION BY e.CLIENT_NUMBER) emp_act_type--> part2 for employees
FROM tb_main e
WHERE PERSON_ID = 11
),rpt_spouse as
(SELECT s.SPOUSE_NUMBER, s.CLIENT_NUMBER sp_cl_number, ---> part 1 for spouse
COUNT(s.CLIENT_NUMBER) OVER() sal_cl, -----> part 1 for spouse
COUNT(DISTINCT s.CLIENT_NUMBER) OVER() dist_sal_cl, ----> part 1 for spouse
SUM(s.INCENTIVE_AMOUNT) OVER() sal_amt, ------> part 1 for spouse
COUNT(s.CLIENT_NUMBER) OVER (PARTITION BY s.CLIENT_NUMBER) sal_group, ----> part 2 for spouse
SUM(s.INCENTIVE_AMOUNT) OVER (PARTITION BY s.CLIENT_NUMBER) sal_inc_group, ----> part 2 for spouse
COUNT(DISTINCT s.ACTIVITY_TYPE) OVER (PARTITION BY s.CLIENT_NUMBER) sal_act_type ---> part 2 for spouse
FROM rpt_main s
WHERE PERSON_ID = 99
)
SELECT e.*, s.*
FROM rpt_emp e
INNER JOIN rpt_spouse s ON (e.EMPLOYEE_NUMBER = s.SPOUSE_NUMBER); -
Let's start by removing the spurious first CTE from your code:
WITH rpt_emp as
(SELECT e.EMPLOYEE_NUMBER, e.CLIENT_NUMBER emp_cl_number, -->Part 1 for employees
COUNT(e.CLIENT_NUMBER) OVER() emp_cl, --> Part 1 for employees
COUNT(DISTINCT e.CLIENT_NUMBER) OVER() dist_emp_cl, ---> Part 1 for employees
SUM(e.INCENTIVE_AMOUNT) OVER() emp_amt, ---> part 1 for emplyees
COUNT(e.CLIENT_NUMBER) OVER (PARTITION BY e.CLIENT_NUMBER) emp_group, ----> part 2 for employees
SUM(e.INCENTIVE_AMOUNT) OVER (PARTITION BY e.CLIENT_NUMBER) emp_inc_group, ----> part 2 for employees
COUNT(DISTINCT e.ACTIVITY_TYPE) OVER (PARTITION BY e.CLIENT_NUMBER) emp_act_type--> part2 for employees
FROM tb_main_db e
WHERE PERSON_ID = 11
),
rpt_spouse as
(SELECT s.SPOUSE_NUMBER, s.CLIENT_NUMBER sp_cl_number, ---> part 1 for spouse
COUNT(s.CLIENT_NUMBER) OVER() sal_cl, -----> part 1 for spouse
COUNT(DISTINCT s.CLIENT_NUMBER) OVER() dist_sal_cl, ----> part 1 for spouse
SUM(s.INCENTIVE_AMOUNT) OVER() sal_amt, ------> part 1 for spouse
COUNT(s.CLIENT_NUMBER) OVER (PARTITION BY s.CLIENT_NUMBER) sal_group, ----> part 2 for spouse
SUM(s.INCENTIVE_AMOUNT) OVER (PARTITION BY s.CLIENT_NUMBER) sal_inc_group, ----> part 2 for spouse
COUNT(DISTINCT s.ACTIVITY_TYPE) OVER (PARTITION BY s.CLIENT_NUMBER) sal_act_type ---> part 2 for spouse
FROM rpt_main s
WHERE PERSON_ID = 99
)
SELECT e.*, s.*
FROM rpt_emp e
INNER JOIN rpt_spouse s ON (e.EMPLOYEE_NUMBER = s.SPOUSE_NUMBER);You have a very confusing naming convention. I assume tb_main_db and rpt_main are both tables? Please will you provide DDL for them both in the form of CREATE TABLE statements and sample data in the form of INSERT statements? Finally, what results do you expect given the sample data that you provide?
John
-
Let's start by providing some sample data and expected results as outlined in the first link in my signature. This will help answer some basic questions.
Also, you say that the Employee_Number and Spouse_Number are the same. They SHOULD NOT BE. One should be a link to the other. If they are the same, you are saying that an employee is their own spouse.
Also, how are you handling two employees that are married?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply