December 16, 2017 at 10:01 am
I obtaining information from one main table to retrieve employees and spouses, and display the fields in my report. I'm counting employees and spouses based on different scenarios or grouping. I'm having trouble coming tb_emp and tb_spouse. I'm getting cartesian product with the following join.
Please advise me as to how can I combine two mini tables. Thanks in advance!
With tb_main as
(
SELECT CLIENT_NO, AMOUNT, ACTIVITY_TYPE, PERSON_ID, EMP_SPOUSE_NUMBER
FROM table_main_db
WHERE EMPLOYER_NUMBER = 1234
),
tb_emp as
(SELECT e.CLIENT_NO, e.AMOUNT, e.EMP_SPOUSE_NUMBER,
COUNT(e.CLIENT_NO) OVER (PARTITION BY e.CLIENT_NO) emp_group,
SUM(e.AMOUNT) OVER (PARTITION BY e.CLIENT_NO) emp_amt_group,
COUNT(DISTINCT e.ACTIVITY_TYPE) OVER (PARTITION BY e.CLIENT_NUMBER) emp_act_group
FROM tb_main e
WHERE PERSON_ID = 11 -- id for employees
),
tb_spouse as
(SELECT s.CLIENT_NO, s.AMOUNT, s.EMP_SPOUSE_NUMBER,
COUNT(s.CLIENT_NO) OVER (PARTITION BY s.CLIENT_NO) spouse_group,
SUM(s.AMOUNT) OVER (PARTITION BY s.CLIENT_NO) spouse_amt_group,
COUNT(DISTINCT s.ACTIVITY_TYPE) OVER (PARTITION BY s.CLIENT_NO) spouse_act_group
FROM tb_main s
WHERE PERSON_ID = 99 -- id for spouse
),
SELECT e.CLIENT_NO, e.AMOUNT, e.emp_group, e.emp_amt_group,
s.CLIENT_NO, s.AMOUNT, s.spouse_group, s.spouse_amt_group
FROM tb_emp e
LEFT OUTER JOIN tb_spouse s ON (e.EMP_SPOUSE_NUMBER = s.EMP_SPOUSE_NUMBER)
December 16, 2017 at 10:43 am
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 20, 2018 at 7:52 am
J Livingston SQL - Saturday, December 16, 2017 10:43 AM
That's really going to help isn't it!
Gordon Barclay
July 20, 2018 at 8:00 am
Gordon Barclay - Friday, July 20, 2018 7:52 AMJ Livingston SQL - Saturday, December 16, 2017 10:43 AMThat's really going to help isn't it!
Well, he should have also said that we need DDL (CREATE TABLE statement) for the table(s) involved, sample data (as INSERT INTO statements) for the table(s) involved that is representative of the problem domain (in other words, not production data), and finally the expected results based on the provided sample data. All of the SQL code provided should also be tested in an empty sandbox database to ensure all works prior to posting.
July 20, 2018 at 8:05 am
Gordon Barclay - Friday, July 20, 2018 7:52 AMJ Livingston SQL - Saturday, December 16, 2017 10:43 AMThat's really going to help isn't it!
maybe you can explain why the link does not help ?
it provides all the necessary prerequisites to post a question that is most likely to get accurate answers quickly.
I posted the link because I thought it helpful........
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 20, 2018 at 8:09 am
J Livingston SQL - Friday, July 20, 2018 8:05 AMGordon Barclay - Friday, July 20, 2018 7:52 AMJ Livingston SQL - Saturday, December 16, 2017 10:43 AMThat's really going to help isn't it!
maybe you can explain why the link does not help ?
it provides all the necessary prerequisites to post a question that is most likely to get accurate answers quickly.
I posted the link because I thought it helpful........
I think it would have been more helpful with an explanation of why to read it and follow the directions. Others coming on to this thread my wonder the meaning as well thinking it would answer the question.
July 20, 2018 at 8:35 am
Lynn Pettis - Friday, July 20, 2018 8:09 AMJ Livingston SQL - Friday, July 20, 2018 8:05 AMGordon Barclay - Friday, July 20, 2018 7:52 AMJ Livingston SQL - Saturday, December 16, 2017 10:43 AMThat's really going to help isn't it!
maybe you can explain why the link does not help ?
it provides all the necessary prerequisites to post a question that is most likely to get accurate answers quickly.
I posted the link because I thought it helpful........I think it would have been more helpful with an explanation of why to read it and follow the directions. Others coming on to this thread my wonder the meaning as well thinking it would answer the question.
Not sure why you're asking when you know full well what I mean?
Gordon Barclay
July 20, 2018 at 8:59 am
Gordon Barclay - Friday, July 20, 2018 8:35 AMLynn Pettis - Friday, July 20, 2018 8:09 AMJ Livingston SQL - Friday, July 20, 2018 8:05 AMGordon Barclay - Friday, July 20, 2018 7:52 AMJ Livingston SQL - Saturday, December 16, 2017 10:43 AMThat's really going to help isn't it!
maybe you can explain why the link does not help ?
it provides all the necessary prerequisites to post a question that is most likely to get accurate answers quickly.
I posted the link because I thought it helpful........I think it would have been more helpful with an explanation of why to read it and follow the directions. Others coming on to this thread my wonder the meaning as well thinking it would answer the question.
Not sure why you're asking when you know full well what I mean?
Not sure why you posted as you did...the thread is 7 months old ?
Are you looking for a solution that is similar to the OP's ?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply