June 3, 2016 at 6:52 am
I have the following data
DROP TABLE mytable;
CREATE TABLE mytable(
cst_org_name_dn VARCHAR(25) NOT NULL PRIMARY KEY
,ind_cst_key VARCHAR(36) NOT NULL
,Type VARCHAR(36) NOT NULL
,Title VARCHAR(40) NOT NULL
,Start_Date VARCHAR(15) NOT NULL
,End_Date VARCHAR(4) NOT NULL
,Primary_Affiliation VARCHAR(1)
,mbt_code VARCHAR(2) NOT NULL
);
INSERT INTO mytable(cst_org_name_dn,ind_cst_key,Type,Title,Start_Date,End_Date,Primary_Affiliation,mbt_code) VALUES ('3 Creek Ranch Golf Club','CB72A5E9-A885-4A6E-B13E-B41BF2DADE37','Equipment Manager','Equipment Manager','11/9/2015 0:00',NULL,'X','EM');
INSERT INTO mytable(cst_org_name_dn,ind_cst_key,Type,Title,Start_Date,End_Date,Primary_Affiliation,mbt_code) VALUES ('3 Creek Ranch Golf Club','F07CA269-657B-4BF4-AC8B-4A4FFEE00951','Assistant Golf Course Superintendent','2nd Assistant Golf Course Superintendent','1/29/2013 0:00',NULL,'X','C');
INSERT INTO mytable(cst_org_name_dn,ind_cst_key,Type,Title,Start_Date,End_Date,Primary_Affiliation,mbt_code) VALUES ('3 Creek Ranch Golf Club','4D6C89CC-AB41-41FF-9E26-5D0B047B0878','Assistant Golf Course Superintendent','Assistant Golf Course Superintendent','10/13/2008 0:00',NULL,'X','C');
INSERT INTO mytable(cst_org_name_dn,ind_cst_key,Type,Title,Start_Date,End_Date,Primary_Affiliation,mbt_code) VALUES ('3 Creek Ranch Golf Club','07040EFF-7722-4811-B977-FE9DE1CB2E10','Fundraising Contact','Golf Course Superintendent','5/19/2015 0:00',NULL,NULL,'A');
INSERT INTO mytable(cst_org_name_dn,ind_cst_key,Type,Title,Start_Date,End_Date,Primary_Affiliation,mbt_code) VALUES ('Abbey Springs Golf Course','9F2B109C-4A13-4549-A7D8-F0A52D22FB62','Assistant Golf Course Superintendent','Assistant Golf Course Supt.','3/12/1999 0:00',NULL,'X','C');
INSERT INTO mytable(cst_org_name_dn,ind_cst_key,Type,Title,Start_Date,End_Date,Primary_Affiliation,mbt_code) VALUES ('Abbey Springs Golf Course','561A1126-FC89-4C6D-A93A-B3316D5C232C','Golf Course Superintendent','Golf Course Superintendent','4/16/2014 0:00',NULL,'X','A');
INSERT INTO mytable(cst_org_name_dn,ind_cst_key,Type,Title,Start_Date,End_Date,Primary_Affiliation,mbt_code) VALUES ('Abbey Springs Golf Course','2272A65D-F01E-4AFB-9A34-6DFD32DF7D9A','Golf Course Superintendent','Director of Grounds',NULL,NULL,'X','A');
INSERT INTO mytable(cst_org_name_dn,ind_cst_key,Type,Title,Start_Date,End_Date,Primary_Affiliation,mbt_code) VALUES ('Abenaqui Country Club','EC28D0C1-9CF6-4B04-8A1C-9E9E67DA8AF0','Golf Course Superintendent','Golf Course Superintendent',NULL,NULL,'X','A');
INSERT INTO mytable(cst_org_name_dn,ind_cst_key,Type,Title,Start_Date,End_Date,Primary_Affiliation,mbt_code) VALUES ('Aberdeen Country Club','4CC7DCF2-CFAA-4077-9EC1-81148AEE4349','Golf Course Superintendent','Golf Course Superintendent','6/2/2006 0:00',NULL,'X','A');
INSERT INTO mytable(cst_org_name_dn,ind_cst_key,Type,Title,Start_Date,End_Date,Primary_Affiliation,mbt_code) VALUES ('Aberdeen Country Club','C04432BD-F1F5-40D5-8043-917054804DCF','Assistant Golf Course Superintendent','Assistant Golf Course Superintendent','5/4/2011 0:00',NULL,'X','C');
INSERT INTO mytable(cst_org_name_dn,ind_cst_key,Type,Title,Start_Date,End_Date,Primary_Affiliation,mbt_code) VALUES ('Aberdeen Country Club','9183FE72-8443-44BC-BB99-82D427E52472','Assistant Golf Course Superintendent','Assistant Golf Course Supt.','2/1/2006 0:00',NULL,'X','C');
INSERT INTO mytable(cst_org_name_dn,ind_cst_key,Type,Title,Start_Date,End_Date,Primary_Affiliation,mbt_code) VALUES ('Horseshoe Bay Resort','4E25FF1D-759E-4778-A960-952BD04344C7','Director of Agronomy','Director of Agronomy','1/19/2009 0:00',NULL,'X','A');
INSERT INTO mytable(cst_org_name_dn,ind_cst_key,Type,Title,Start_Date,End_Date,Primary_Affiliation,mbt_code) VALUES ('Horseshoe Bay Resort','0A606842-E25C-41C9-9933-7091202AF864','Assistant Golf Course Superintendent','Assistant Golf Course Superintendent','1/14/2013 0:00',NULL,'X','C');
INSERT INTO mytable(cst_org_name_dn,ind_cst_key,Type,Title,Start_Date,End_Date,Primary_Affiliation,mbt_code) VALUES ('Horseshoe Bay Resort','EF8AD513-1929-48AF-8548-78A23B540D14','Assistant Golf Course Superintendent','Assistant Golf Course Superintendent','4/5/2012 0:00',NULL,'X','C');
INSERT INTO mytable(cst_org_name_dn,ind_cst_key,Type,Title,Start_Date,End_Date,Primary_Affiliation,mbt_code) VALUES ('Horseshoe Bay Resort','E73A88E9-8D50-4F1B-960D-8A83D46B054E','Equipment Manager','Equipment Manager','7/15/2015 0:00',NULL,'X','EM');
INSERT INTO mytable(cst_org_name_dn,ind_cst_key,Type,Title,Start_Date,End_Date,Primary_Affiliation,mbt_code) VALUES ('Horseshoe Bay Resort','4E25FF1D-759E-4778-A960-952BD04344C7','Fundraising Contact','Director of Agronomy','5/18/2015 0:00',NULL,NULL,'A');
There are 4 organizations here 2 of them have a Member type of EM and 2 do now. What I am trying to figure out is how to use TSQL loop through each organization and give me the following
1. If the Organization has a EM member return only the member types of 'A','B','C'
2. If the organization does not have an EM member ignore it in the results set.
Either I have been staring at the tree's to long and can't see the forest or I'm missing something easy.
Could someone much smarter then I offer a suggestion of how this should work.
I've tried grouping and having but not getting it...
June 3, 2016 at 6:57 am
SELECT m.*
FROM mytable m
WHERE EXISTS (
SELECT 1
FROM mytable i
WHERE i.cst_org_name_dn = m.cst_org_name_dn
AND i.mbt_code = 'EM')
AND m.mbt_code <> 'EM'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 3, 2016 at 7:58 am
You should test the code for your sample data before posting it. When I ran the code, I got an error, because the Start_Date is set to NOT NULL, but two of the records have NULL start dates. When I removed all the NOT NULLs and reran the code, I got a primary key violation, because cst_org_name_dn was set to the primary key, but it is not unique.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 3, 2016 at 8:14 am
ChrisM@Work (6/3/2016)
SELECT m.*FROM mytable m
WHERE EXISTS (
SELECT 1
FROM mytable i
WHERE i.cst_org_name_dn = m.cst_org_name_dn
AND i.mbt_code = 'EM')
AND m.mbt_code <> 'EM'
Yes. And if at all there are more possible values in mbt_code,
Select * from mytable I where mbt_code in ('A','B','C' )
AND EXISTS( Select 1 from mytable where mbt_code = 'EM' and cst_org_name_dn=i.cst_org_name_dn )
June 3, 2016 at 8:17 am
My appologies drew.allen
June 3, 2016 at 8:26 am
Thank you so much, This worked like a champ ChrisM@Work you rock
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply