SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Not getting the right result either due to grouping or join


Not getting the right result either due to grouping or join

Author
Message
soldout6000
soldout6000
SSC-Addicted
SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)

Group: General Forum Members
Points: 491 Visits: 140
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.
upload_2017-12-21_18-53-27.png
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);



John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)

Group: General Forum Members
Points: 125267 Visits: 18852
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
drew.allen
drew.allen
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58299 Visits: 15287
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search