Need set based method to insert missing records into table - right join doesn't work

Question

Need set based method to insert missing records into table - right join doesn't work

sqlmom

SSC-Addicted

Points: 412
More actions
April 24, 2014 at 11:42 am

#292656

I apologize if I don't set this up the appropriate way as this is my first time requesting help and I'm trying to keep this short.
I have table A (EmployeeNumber, Grouping, Stages)
and
Table B (Grouping, Stages)
Table A could look like the following where the multiple employees could have multiple types and multiple stages.
EmployeeNumber, Type, Stages
100, 1, Stage1
100, 1, Stage2
100, 2, Stage1
100, 2, Stage2
200, 1, Stage1
200, 2, Stage2
Table B is a list of requirements that each employee must have. So every employee must have a type 1 and 2 and the associated stages listed below.
Type, Stage
1, Stage1
1, Stage2
2, Stage1
2, Stage2
2, Stage3
2, Stage4
So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.
I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.
I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.
My count would look something like this:
EmployeeNumber Type Total
100, 1, 2
100, 2, 2
200, 1, 1
200 1, 2
So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.
The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.
I hope this makes sense to someone and thank you very much for your help.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Can you post some ddl, sample data and desired output based on your sample data? We can't help you with code because we have nothing to work with. Please take a few minutes and read the article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sqlmom SSC-Addicted Points: 412 More actions · Answer 2

I'm sorry; I'm on a huge time crunch. Can anyone get the gist of it from my post? I don't expect anyone to spend a ton of time on it, but if someone can get the gist and just give me a suggestion, I would really appreciate it. If not, thanks anyway I appreciate your time.

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

dmckro (4/24/2014)
I'm sorry; I'm on a huge time crunch. Can anyone get the gist of it from my post? I don't expect anyone to spend a ton of time on it, but if someone can get the gist and just give me a suggestion, I would really appreciate it. If not, thanks anyway I appreciate your time.

There just isn't enough detail to figure out what you are trying to do. Maybe create a table of the requirements, then use a left join to the employee table where null??? Do you also need to verify that there are not rows in the table that are not required? Maybe they have 3 type 2s when there should only be 2?

Honestly putting together ddl and sample data for such a small thing might take about 10 minutes. The problem is we don't know what you are trying to do so the best we can do is guess.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sqlmom SSC-Addicted Points: 412 More actions · Answer 4

I understand, thank you for your response and for trying. I work part-time and I need to leave for the day, but I appreciate it.

Lowell SSC Guru Points: 323504 More actions · Answer 5

it took me longer to build your sample data from what you posted, than it was to create the solution.

help us help you!

if you post consumable data, we can help you faster, and give you a tested working solution.

;WITH TableA([EmployeeNumber], [Type], [Stages])

AS

(

SELECT '100', '1', 'Stage1' UNION ALL

SELECT '100', '1', 'Stage2' UNION ALL

SELECT '100', '2', 'Stage1' UNION ALL

SELECT '100', '2', 'Stage2' UNION ALL

SELECT '200', '1', 'Stage1' UNION ALL

SELECT '200', '2', 'Stage2'

),

TableB([Type], [Stage])

AS

(

SELECT '1', 'Stage1' UNION ALL

SELECT '1', 'Stage2' UNION ALL

SELECT '2', 'Stage1' UNION ALL

SELECT '2', 'Stage2' UNION ALL

SELECT '2', 'Stage3' UNION ALL

SELECT '2', 'Stage4'

),

AssumedMatrix

AS

(

--ALLPossible Employees + Required Stages

SELECT

A.[EmployeeNumber],

A.[Type],

B.[Stage]

FROM TableB B

LEFT OUTER JOIN TableA A

ON B.[Type] = A.[Type]

)

SELECT * FROM AssumedMatrix M

LEFT OUTER JOIN TableA A

ON M.[Type] = A.[Type]

AND M.[Stage] = A.[Stages]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlmom SSC-Addicted Points: 412 More actions · Answer 6

My point exactly! Thank you for your response. I'll take a look at it this afternoon.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 7

dmckro (4/25/2014)
My point exactly! Thank you for your response. I'll take a look at it this afternoon.

I think you missed the point. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlmom SSC-Addicted Points: 412 More actions · Answer 8

Lowell,

Thank you so much for your response. You understand what I'm trying to do! And I greatly appreciate your time. The problem is that I don't have the AssumedMatrix table. I have a table of all the employees and what they have done, but I don't have a table with all the employees and what they are supposed to do. I have a list of the requirements and a list of what they have done. I even have a count of what they have done and I know which ones are missing requirements, but I have to provide a list of missing requirements.

I think once I have that table I could do a left or right join on it and get everything that is null as what they are missing, but I cannot think of a way to get the AssumedMatrix table without using a cursor or a loop( and even then I'm not exactly sure how to do it). My husband used to be a programmer and he was suggesting a while loop to populate the matrix table, but there has to be a set-based method to do it.

Thanks again and I'm sorry I didn't have the code to make it easier to help, but you do have a handle on what I'm trying to do.

Lowell SSC Guru Points: 323504 More actions · Answer 9

well, as you can see from my example code, i generated my best guess from the existing data.

you can do the same, obviously; i would assume that somewhere, someone has defined for Each [Type] there must be these [Stages]; otherwise you wouldn't even have the tables with those columns in it. reverse engineering and digging through business requirements, i guess, is the solution.

you might just select each distinct Type + Stage from your existing table to generate the AssumedMatrix; it really depends on the details.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlmom SSC-Addicted Points: 412 More actions · Answer 10

Thank you for your response. I'm putting together a response with more realistic data of what I'm actually trying to do. Just trying to be careful about what I put out on a public site. But I think this will be okay. Once again I appreciate anyone's help. I'm not trying to be difficult. 🙂

sqlmom SSC-Addicted Points: 412 More actions · Answer 11

You should be able to just copy and paste this whole thing into SSMS. I don't normally ask for help. I usually try to figure everything out on my own, but I'm really stuck with this one. Thank you so much for anyone willing to take a look!

--drop table #subjectExistStages

create table #subjectExistStages (Subject varchar (5), [Name] varchar (10), [Stage] varchar (10))

insert into #subjectExistStages (Subject, [Name], [Stage])

SELECT '100', 'Visit1', 'Pre' UNION ALL

SELECT '100', 'Visit1', 'Post' UNION ALL

SELECT '100', 'Visit2', 'Pre1' UNION ALL

SELECT '100', 'Visit2', 'Post1' UNION ALL

SELECT '100', 'Visit3', 'Stage1' UNION ALL

SELECT '100', 'Visit3', 'Stage2' UNION ALL

SELECT '100', 'Visit3', 'Stage3' UNION ALL

SELECT '100', 'Visit3', 'Stage5' UNION ALL

SELECT '100', 'Visit3', 'Stage7' UNION ALL

SELECT '100', 'Visit3', 'Stage8' UNION ALL

SELECT '100', 'Visit3', 'Stage9' UNION ALL

SELECT '100', 'Visit3', 'Stage10' UNION ALL

SELECT '200', 'Visit1', 'Pre' UNION ALL

SELECT '200', 'Visit1', 'Post' UNION ALL

SELECT '200', 'Visit2', 'Pre1' UNION ALL

SELECT '200', 'Visit2', 'Post1' UNION ALL

SELECT '200', 'Visit2', 'Pre2' UNION ALL

SELECT '200', 'Visit2', 'Post2' UNION ALL

SELECT '200', 'Visit3', 'Stage1' UNION ALL

SELECT '200', 'Visit3', 'Stage2' UNION ALL

SELECT '200', 'Visit3', 'Stage3' UNION ALL

SELECT '200', 'Visit3', 'Stage4' UNION ALL

SELECT '200', 'Visit3', 'Stage5' UNION ALL

SELECT '200', 'Visit3', 'Stage7' UNION ALL

SELECT '200', 'Visit3', 'Stage8' UNION ALL

SELECT '200', 'Visit3', 'Stage9' UNION ALL

SELECT '200', 'Visit3', 'Stage10'

--drop table #requiredstages

create table #requiredstages([Name] varchar (10), [Stage] varchar (10))

insert into #requiredstages ([Name], [Stage] )

SELECT 'Visit1', 'Pre' UNION ALL

SELECT 'Visit1', 'Post' UNION ALL

SELECT 'Visit2', 'Pre1' UNION ALL

SELECT 'Visit2', 'Post1' UNION ALL

SELECT 'Visit2', 'Pre2' UNION ALL

SELECT 'Visit2', 'Post2' UNION All

SELECT 'Visit3', 'Stage1' UNION All

SELECT 'Visit3', 'Stage2' UNION All

SELECT 'Visit3', 'Stage3' UNION All

SELECT 'Visit3', 'Stage4' UNION All

SELECT 'Visit3', 'Stage5' UNION All

SELECT 'Visit3', 'Stage6' UNION All

SELECT 'Visit3', 'Stage7' UNION All

SELECT 'Visit3', 'Stage8' UNION All

SELECT 'Visit3', 'Stage9' UNION All

SELECT 'Visit3', 'Stage10'

--This table tells you the number of stages that should happen at each visit

--drop table #CtRequiredStages

select count(*)Total, name

into #CtRequiredStages

from #RequiredStages

group by name

--The #problemSubjectVisits table tells you the subjects who are missing stages at what visit.

--drop table #problemSubjectVisits

select c.*

into #problemSubjectVisits

from #CtRequiredStages a join (

select b.subject, b.name, count (*) total

from #RequiredStages a join (select subject, name, stage

from #subjectExistStages

group by subject, name, stage) as b on a.Name = b.name and a.Stage = b.stage

group by subject, b.name

) as c on a.name = c.name

where a.total <> c.total

--Now I need a table that gives me a list of stages that are missing at each visit. I thought I could do something like this...

--This does not work because there are subjects that do have some of the stages.

select distinct

a.Subject

, b.Name

, b.stage

from #problemSubjectVisits a

join #subjectExistStages b on a.subject = b.subject and a.name = b.name

right join #RequiredStages c on b.name = c.name and b.stage = c.stage

where b.stage is null

order by Subject

sqlmom SSC-Addicted Points: 412 More actions · Answer 12

Sorry, I didn't get the scroll bar. I'll look at that in a bit when I get back. Thanks!

sqlmom SSC-Addicted Points: 412 More actions · Answer 13

I found my answer. Thank you, no more help is needed.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 14

sqlmom (4/28/2014)
I found my answer. Thank you, no more help is needed.

Would you mind sharing it with the rest?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2