[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Eliminate names based on records in joined table

Posted on 2011-10-16
10
Medium Priority
?
183 Views
Last Modified: 2012-05-12
What I am trying to do is eliminate a person from showin gin a gridview if they have valid records in another table.  They can have up to 3 valid records that are good for a year and if they are good i do not want there name to show.

for example if record 156 is valid but 158 or 159 is not there name should show, if 156 and 158 are expired there name should show if all 3 are vaild there name should nto show.

The following code gets a list of names from Oracle DB, then I need to eliminate names based on the folloin table if they have a intSRPID records 3 of them (156, 158, 159) that are not expired.  If they are expired or they do not have one in the table their name should show up.





Left Joined table
intHistID       SSN      intUnitId     intsrpId     dtExpire
   1         000000000       5            156      2011/10/16
   2         000000000       5            158      2011/10/16
   3         000000000       5            159      2011/10/18
   4         000000001      10            156      2010/09/08
   5         000000001      10            158      2011/11/25
   6         000000003       15           158      2012/15/28

The following code does not show a name but its if any of the records are vaild.


Select SSN_SM as SSN, name_ind, Rank, POSC, AUTH_PARA_DSG, AUTH_LINE_DSG, COnvert(varchar(10), DOB, 111) DOB from OPENQUERY(SIDPERS, 'Select pt.SSN_SM, pt.name_ind, substr(st.GR_ABBR_CODE,1,3) Rank , dt.POSC, dt.AUTH_PARA_DSG,
       dt.AUTH_LINE_DSG, pt.DOB from PERS_PERSON_TBL pt LEFT JOIN PERS_SVCMBR_TBL st on st.mpc = pt.mpc and st.ASG_SEQ_NBR = pt.ASG_SEQ_NBR LEFT JOIN PERS_DUTY_POSN_TBL dt on dt.mpc = st.mpc and 
       dt.ASG_SEQ_NBR = st.ASG_SEQ_NBR LEFT JOIN PERS_SVCMBR_ATCHMT_TBL at on at.mpc = pt.mpc and at.ASG_SEQ_NBR = pt.ASG_SEQ_NBR Where pt.REC_PREC = ''99999999'' and st.REC_STAT <> ''M'' AND (dt.UPC IN 
       (''PUNC0'') or at.UPC IN (''PUNC)''))') LEFT JOIN
       (Select strssn, intSRPID from tblUnitSRPPersonnelHist where intSRpId in (156, 158, 159) and dtExpire < GETDATE()) h on h.strSSn = SSN_SM
       Order by name_ind

Open in new window

0
Comment
Question by:kdeutsch
  • 7
  • 3
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36976292
Something like this perhaps:
SELECT  o.SSN_SM AS SSN,
        o.name_ind,
        o.[Rank],
        o.POSC,
        o.AUTH_PARA_DSG,
        o.AUTH_LINE_DSG,
        CONVERT(varchar(10), o.DOB, 111) DOB
FROM    OPENQUERY(SIDPERS, '
		Select pt.SSN_SM,
		       pt.name_ind, 
		       substr(st.GR_ABBR_CODE,1,3) Rank, 
		       dt.POSC, 
		       dt.AUTH_PARA_DSG, 
		       dt.AUTH_LINE_DSG, 
		       pt.DOB 
		from   PERS_PERSON_TBL pt 
		       LEFT JOIN PERS_SVCMBR_TBL st on st.mpc = pt.mpc and st.ASG_SEQ_NBR = pt.ASG_SEQ_NBR 
		       LEFT JOIN PERS_DUTY_POSN_TBL dt on dt.mpc = st.mpc and dt.ASG_SEQ_NBR = st.ASG_SEQ_NBR 
		       LEFT JOIN PERS_SVCMBR_ATCHMT_TBL at on at.mpc = pt.mpc and at.ASG_SEQ_NBR = pt.ASG_SEQ_NBR 
	         Where  pt.REC_PREC = ''99999999'' 
	                and st.REC_STAT <> ''M'' 
		       AND (dt.UPC IN (''PUNC0'') or at.UPC IN (''PUNC)''))') o
        LEFT JOIN (SELECT   strssn
                   FROM     tblUnitSRPPersonnelHist
                   WHERE    intSRpId IN (156, 158, 159)
                            AND dtExpire < GETDATE()
                   GROUP BY strssn
                   HAVING   COUNT(*) >= 3
                  ) h ON h.strSSn = o.SSN_SM
WHERE   h.strssn IS NULL
ORDER BY o.name_ind

Open in new window

0
 

Author Comment

by:kdeutsch
ID: 36976414
acPerkins,

here is what I came up with and it works to a certain extent with more coding.  So both records 156 and 158 are not expired the person does not show this is good, if either one are expired they show up, this is good.  So it works to this extend.  However the last piece I cannot get going is that record 159 trumps them both so if they have a record 159 and both 156 and 158 are expired there name should not show until it expires.

 Where SSN_SM NOT IN
       (Select strssn from tblUnitSRPPersonnelHist where intSRpId = 156 and dtExpire > GETDATE()) OR SSN_SM NOT IN
       (Select strssn from tblUnitSRPPersonnelHist where intSRpId = 158 and dtExpire > GETDATE())
       order by name_ind
0
 

Author Comment

by:kdeutsch
ID: 36976427
acperkins:

Working with your code if I have a vaild 159 record it still shows there name, also I guess they do not have to have a 159 record Id on there records and potentially they never will.  it seems to work for teh stiuations like I mentioned for what I came up with.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36976452
I guess I am not following you.  But why don't I tell you what I am doing in English and you can tell me what is wrong with that picture:
Al data will be picked, except for those rows that match the SSN in tblUnitSRPPersonnelHist and that have three or more entries of either 156, 158 or 159 that have expired.
0
 

Author Comment

by:kdeutsch
ID: 36976506
acperkins:,

yup i gotcha, i see what the code is doing.  Hopefully this will explain.  They will always have a 156 or 158 record in the table.  If either of these are expired with dtExpire column then their name should show back up in the pool agian.  You code is working for this scenerio.  The last scenerio is when they put in a 159 record, now some might have it and some might not.  But if 156 and 158 are expired and 159 is not , their name should not show because 159's expired date trumps the other 2.  If they are all expired there name should show.
record id            dtExpired        
156                    2010/10/05
157                   2010/10/20
Name should show becuase 156 is expired, it would be same if both where expired or just 158 was expired and 156 was not

record id            dtExpired        
156                    2010/10/05
157                   2010/10/05
159                   2010/10/20
Name should not show becuase 159 is  not expired, but 156 and 157 are but 159 overrides them.


record id            dtExpired        
156                    2010/10/20
157                   2010/10/05
159                   2010/10/05

Name should  show becuase 159 is expired and 157 is expired, but 156 is not.  Because 157 is expired they should show, in this scenerio, we don't care about 159 because its a specialized record and might not show up for a long time agian.

Hope this better explains it.

0
 

Author Comment

by:kdeutsch
ID: 36976508
acperkins:,


Sorry above the 157 records should be 156 records.
0
 

Author Comment

by:kdeutsch
ID: 36976511
acperkins:,

UGH,

They shoudl be 156, 158 and 159 records.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36977240
I am so sorry, but I am drawing a total blank.  Hopefully someone will step up to the plate.
0
 

Accepted Solution

by:
kdeutsch earned 0 total points
ID: 36987079
Solution was to add bitActive fields to the records to only pull the avtice records by dtExpire and tehn when a 159 records is introduced to make all 3 records the same expiration date.
0
 

Author Closing Comment

by:kdeutsch
ID: 37013545
No other solutions offered.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question