Left JOIN Works Appears to Only Work with Certain Table
-
Hello Community,
If I execute the following query on table name optionsettransposedall_copy:
SELECT C1.Id
,C1.SinkCreatedOn
,C1.SinkModifiedOn
,C1.statecode
,C1.statuscode,
CASE WHEN ISNULL(CAST(C1.prioritycode AS VARCHAR(50)),'') <> ISNULL(CAST(C2.prioritycode AS VARCHAR(50)),'') THEN CAST(C2.prioritycode AS VARCHAR(50))
ELSE CAST(C1.prioritycode AS VARCHAR(50))
END AS prioritycode
FROM
(
SELECT *,ROW_NUMBER()OVER(ORDER BY prioritycode)RowNum FROM appointment
)C1
LEFT JOIN
(
SELECT *,ROW_NUMBER()OVER(ORDER BY prioritycode)RowNum FROM optionsettransposedall_copy
)C2
ON C1.RowNum = C2.RowNumI get results in the field prioritycode, as shown in image:
However, if I execute the same code on the table optionsettransposedall_why, which also has the prioritycode field I get NULLs
SELECT C1.Id
,C1.SinkCreatedOn
,C1.SinkModifiedOn
,C1.statecode
,C1.statuscode,
CASE WHEN ISNULL(CAST(C1.prioritycode AS VARCHAR(50)),'') <> ISNULL(CAST(C2.prioritycode AS VARCHAR(50)),'') THEN CAST(C2.prioritycode AS VARCHAR(50))
ELSE CAST(C1.prioritycode AS VARCHAR(50))
END AS prioritycode
FROM
(
SELECT *,ROW_NUMBER()OVER(ORDER BY prioritycode)RowNum FROM appointment
)C1
LEFT JOIN
(
SELECT *,ROW_NUMBER()OVER(ORDER BY prioritycode)RowNum FROM optionsettransposedall_why
)C2
ON C1.RowNum = C2.RowNumI get NULLs with the above
Any thoughts, because this is driving me crazy
I have sample data from both tables
CREATE TABLE optionsettransposedall_why (
participationtypemask varchar(50),
instancetypecode varchar(50),
donotpostalmail varchar(50),
donotfax varchar(50),
donotphone varchar(50),
ispartydeleted varchar(50),
donotemail varchar(50),
ts_primarysecondaryfocus varchar(50),
customertypecode varchar(50),
address1_addresstypecode varchar(50),
accountclassificationcode varchar(50),
ts_ukrow varchar(50),
preferredappointmenttimecode varchar(50),
address2_freighttermscode varchar(50),
ts_introducertype varchar(50),
accountcategorycode varchar(50),
paymenttermscode varchar(50),
preferredappointmentdaycode varchar(50),
businesstypecode varchar(50),
industrycode varchar(100),
address1_shippingmethodcode varchar(50),
ts_portfoliocompanystatus varchar(50),
customersizecode varchar(50),
preferredcontactmethodcode varchar(50),
accountratingcode varchar(50),
address2_addresstypecode varchar(50),
address2_shippingmethodcode varchar(50),
ownershipcode varchar(50),
territorycode varchar(50),
shippingmethodcode varchar(50),
address1_freighttermscode varchar(50),
ts_vendorsummary varchar(100),
mpe_livedeal varchar(50),
donotbulkpostalmail varchar(50),
msdyn_gdproptout varchar(50),
ts_organisationalchange varchar(50),
xpd_publictoprivatetracker varchar(50),
ts_auditor varchar(50),
donotsendmm varchar(50),
donotbulkemail varchar(50),
followemail varchar(50),
creditonhold varchar(50),
marketingonly varchar(50),
participatesinworkflow varchar(50),
merged varchar(50),
ts_validated varchar(50),
xpd_sharepointlocationcreated varchar(50),
xpd_setreminder varchar(50),
ts_personnelchange varchar(50),
isprivate varchar(50),
xpd_rpo varchar(50),
ts_trackcompany varchar(50),
ts_stageofmeetingrounds varchar(50),
XXXX varchar(50),
prioritycode varchar(50),
attachmenterrors varchar(250),
isworkflowcreated varchar(50),
isalldayevent varchar(50),
ts_interview varchar(50),
isbilled varchar(50),
isdraft varchar(50),
ts_keyiractivity varchar(50),
ts_noninvestor varchar(50),
ismapiprivate varchar(50),
haschildrencode varchar(50),
gendercode varchar(50),
ts_dealannouncedreason varchar(50),
ts_highprioritynew varchar(50),
ts_actionstatus varchar(50),
ts_internalintermediarysource varchar(50),
ts_currentactivity varchar(50),
ts_opportunitystatus varchar(50),
ts_opportunitytype varchar(50),
ts_posthumstatus varchar(50),
ts_meetingremindermonths varchar(50),
ts_setreminder varchar(50),
ts_stagereachedfir varchar(50),
ts_stagereachedipr varchar(50),
ts_duplicateopportunityfordiscussion varchar(50),
mpe_smalltarget varchar(50),
ts_stagereachedcr varchar(50),
xpd_includeonallpapers varchar(50),
ts_sold varchar(50))
INSERT optionsettransposedall_why VALUES
('Sender','Not Recurring','Allow','Allow','Allow','No','Allow','Tier 1','Competitor','Bill To','Default Value','UK','Morning','Default Value','Primary','Preferred Customer','Net 30','Sunday','Default Value','Accounting','Airborne','Current','Default Value','Any','Default Value','Default Value','Default Value','Public','Default Value','Default Value','FOB','Contains target','No','No','No','No','No','No','Send','Allow','Do Not Allow','No','No','No','No','No','No','No','No','No','No','No','First Management Meeting','Not Recurring','Low','None','No','No','No','No','No','No','No','No','Default Value','Male','NOB','Yes','Chopped','Internal','1. Analysing','Anticipated','P2P','WIP','3 Months','No','No','No','No','No','No','No','--'),
('Sender','Not Recurring','Allow','Allow','Allow','No','Allow','Tier 1','Default Value','Bill To','Default Value','UK','Morning','Default Value','Primary','Preferred Customer','Net 30','Sunday','Default Value','Accounting','Airborne','Current','Default Value','Any','Default Value','Default Value','Default Value','Public','Default Value','Default Value','FOB','Contains target','No','No','No','No','No','No','Send','Allow','Do Not Allow','No','No','No','No','No','No','No','No','No','No','No','First Management Meeting','Not Recurring','Low','None','No','No','No','No','No','No','No','No','Default Value','Male','NOB','Yes','Chopped','Internal','1. Analysing','Anticipated','P2P','WIP','3 Months','No','No','No','No','No','No','No','--'),
('To Recipient','Not Recurring','Allow','Allow','Allow','Yes','Allow','Tier 2','Default Value','Default Value',NULL,'RoW','Morning','Default Value','Boutique','Standard','Net 30','Sunday',NULL,'Agriculture and Non-petrol Natural Resource Extraction','Default Value','Former','Default Value','Any',NULL,'Default Value','Default Value','Private','Default Value','Default Value','FOB','Active in M&A','Yes','No','No','Yes','Yes','Yes','Send','Allow','Do Not Allow','No','No','No','No','No','No','Yes','Yes','No','Yes','Yes','Follow Up Management Meeting','Not Recurring','Low','The appointment was saved as a Microsoft Dynamics 365 appointment record, but not all the attachments could be saved with it. An attachment cannot be saved if it is blocked or if its file type is invalid.','No','Yes','Yes','No','Yes','No','Yes','No',NULL,'Female','Price','No','Anticipated HuM','Intermediary','2. Trying to meet','In Market','Primary','Won','6 Months','Yes','Yes','Yes','Yes','Yes','Yes','Yes','Yes'),
('To Recipient','Not Recurring','Allow','Allow','Allow','Yes','Allow','Tier 2','Competitor','Bill To',NULL,'RoW','Morning','Default Value','Boutique','Standard','Net 30','Sunday',NULL,'Agriculture and Non-petrol Natural Resource Extraction','Airborne','Former','Default Value','Any',NULL,'Default Value','Default Value','Private','Default Value','Default Value','FOB','Active in M&A','Yes','No','No','Yes','Yes','Yes','Send','Allow','Do Not Allow','No','No','No','No','No','No','Yes','Yes','No','Yes','Yes','Follow Up Management Meeting','Not Recurring','Low','The appointment was saved as a Microsoft Dynamics 365 appointment record, but not all the attachments could be saved with it. An attachment cannot be saved if it is blocked or if its file type is invalid.','No','Yes','Yes','No','Yes','No','Yes','No',NULL,'Female','Price','No','Anticipated HuM','Intermediary','2. Trying to meet','In Market','Primary','Won','6 Months','Yes','Yes','Yes','Yes','Yes','Yes','Yes','Yes'),
('CC Recipient','Not Recurring','Allow','Allow','Allow',NULL,'Allow','TBC','Consultant','Default Value',NULL,NULL,'Afternoon',NULL,'T2 Generalists',NULL,'2% 10, Net 30','Monday',NULL,'Broadcasting Printing and Publishing','Default Value',NULL,NULL,'Email',NULL,'Default Value','Default Value','Subsidiary',NULL,NULL,'No Charge','Operates across areas of interest',NULL,'Yes','No',NULL,NULL,NULL,'Do Not Send','Do Not Allow','Allow','Yes','Yes','Yes','Yes','Yes','No',NULL,NULL,'Yes',NULL,NULL,NULL,'Not Recurring','Low',NULL,'No',NULL,NULL,'No',NULL,'Yes',NULL,'Yes',NULL,NULL,'Terms',NULL,NULL,NULL,'3. Date agreed','On Hold/Frozen','Secondary','Lost','12 Months',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('CC Recipient','Not Recurring','Allow','Allow','Allow',NULL,'Allow','TBC','Consultant','Bill To',NULL,NULL,'Afternoon',NULL,'T2 Generalists',NULL,'2% 10, Net 30','Monday',NULL,'Broadcasting Printing and Publishing','Airborne',NULL,NULL,'Email',NULL,'Default Value','Default Value','Subsidiary',NULL,NULL,'No Charge','Operates across areas of interest',NULL,'Yes','No',NULL,NULL,NULL,'Do Not Send','Do Not Allow','Allow','Yes','Yes','Yes','Yes','Yes','No',NULL,NULL,'Yes',NULL,NULL,NULL,'Not Recurring','Low',NULL,'No',NULL,NULL,'No',NULL,'Yes',NULL,'Yes',NULL,NULL,'Terms',NULL,NULL,NULL,'3. Date agreed','On Hold/Frozen','Secondary','Lost','12 Months',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('BCC Recipient','Recurring Master','Do Not Allow','Do Not Allow','Do Not Allow',NULL,'Do Not Allow',NULL,'Customer','Ship To',NULL,NULL,'Afternoon',NULL,NULL,NULL,'2% 10, Net 30','Monday',NULL,'Brokers','DHL',NULL,NULL,'Email',NULL,NULL,NULL,'Other',NULL,NULL,'No Charge','Limited targets of interest',NULL,'Yes','Yes',NULL,NULL,NULL,'Do Not Send','Do Not Allow','Allow','Yes','Yes','Yes','Yes','Yes','Yes',NULL,NULL,'Yes',NULL,NULL,NULL,'Recurring Master','Low',NULL,'No',NULL,NULL,'No',NULL,'Yes',NULL,'Yes',NULL,NULL,'No Deal',NULL,NULL,NULL,'5. Chopped','Don''t Know',NULL,'Chopped','9 Months',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('BCC Recipient','Recurring Master','Do Not Allow','Do Not Allow','Do Not Allow',NULL,'Do Not Allow',NULL,'Customer','Ship To',NULL,NULL,'Afternoon',NULL,NULL,NULL,'2% 10, Net 30','Monday',NULL,'Brokers','DHL',NULL,NULL,'Email',NULL,NULL,NULL,'Other',NULL,NULL,'No Charge','Limited targets of interest',NULL,'Yes','Yes',NULL,NULL,NULL,'Do Not Send','Do Not Allow','Allow','Yes','Yes','Yes','Yes','Yes','Yes',NULL,NULL,'Yes',NULL,NULL,NULL,'Recurring Master','Low',NULL,'No',NULL,NULL,'No',NULL,'Yes',NULL,'Yes',NULL,NULL,'No Deal',NULL,NULL,NULL,'5. Chopped','Don''t Know',NULL,'Chopped','9 Months',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('Required attendee','Recurring Master','Do Not Allow','Do Not Allow','Do Not Allow',NULL,'Do Not Allow',NULL,'Investor','Ship To',NULL,NULL,'Evening',NULL,NULL,NULL,'Net 45','Tuesday',NULL,'Building Supply Retail','DHL',NULL,NULL,'Phone',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Whole business target',NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,NULL,'Recurring Master','Low',NULL,'No',NULL,NULL,'No',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'4. Post meeting','No Process',NULL,'Faded',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('Required attendee','Recurring Master','Do Not Allow','Do Not Allow','Do Not Allow',NULL,'Do Not Allow',NULL,'Investor','Ship To',NULL,NULL,'Evening',NULL,NULL,NULL,'Net 45','Tuesday',NULL,'Building Supply Retail','DHL',NULL,NULL,'Phone',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Whole business target',NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,NULL,'Recurring Master','Low',NULL,'No',NULL,NULL,'No',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'4. Post meeting','No Process',NULL,'Faded',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('Optional attendee','Recurring Master','Do Not Allow','Do Not Allow','Do Not Allow',NULL,'Do Not Allow',NULL,'Partner','Primary',NULL,NULL,'Evening',NULL,NULL,NULL,'Net 45','Tuesday',NULL,'Business Services','FedEx',NULL,NULL,'Phone',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Whole group of interest',NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,NULL,'Recurring Master','Low',NULL,'No',NULL,NULL,'No',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('Optional attendee','Recurring Master','Do Not Allow','Do Not Allow','Do Not Allow',NULL,'Do Not Allow',NULL,'Partner','Primary',NULL,NULL,'Evening',NULL,NULL,NULL,'Net 45','Tuesday',NULL,'Business Services','FedEx',NULL,NULL,'Phone',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Whole group of interest',NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,NULL,'Recurring Master','Low',NULL,'No',NULL,NULL,'No',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('Organizer','Recurring Instance',NULL,NULL,NULL,NULL,NULL,NULL,'Influencer','Primary',NULL,NULL,NULL,NULL,NULL,NULL,'Net 60','Wednesday',NULL,'Consulting','FedEx',NULL,NULL,'Fax',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Unusual industrial acquirer - worth knowing',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Recurring Instance','Low',NULL,'No',NULL,NULL,'No',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('Organizer','Recurring Instance',NULL,NULL,NULL,NULL,NULL,NULL,'Influencer','Primary',NULL,NULL,NULL,NULL,NULL,NULL,'Net 60','Wednesday',NULL,'Consulting','FedEx',NULL,NULL,'Fax',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Unusual industrial acquirer - worth knowing',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Recurring Instance','Low',NULL,'No',NULL,NULL,'No',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('Regarding','Recurring Instance',NULL,NULL,NULL,NULL,NULL,NULL,'Press','Other',NULL,NULL,NULL,NULL,NULL,NULL,'Net 60','Wednesday',NULL,'Consumer Services','UPS',NULL,NULL,'Fax',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Potential future targets',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Recurring Instance','Normal',NULL,'Yes',NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('Regarding','Recurring Instance',NULL,NULL,NULL,NULL,NULL,NULL,'Press','Other',NULL,NULL,NULL,NULL,NULL,NULL,'Net 60','Wednesday',NULL,'Consumer Services','UPS',NULL,NULL,'Fax',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Potential future targets',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Recurring Instance','Normal',NULL,'Yes',NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
SELECT * FROM optionsettransposedall_whyCREATE TABLE optionsettransposedall_copy (
participationtypemask varchar(50),
instancetypecode varchar(50),
donotpostalmail varchar(50),
donotfax varchar(50),
donotphone varchar(50),
ispartydeleted varchar(50),
donotemail varchar(50),
ts_primarysecondaryfocus varchar(50),
customertypecode varchar(50),
address1_addresstypecode varchar(50),
accountclassificationcode varchar(50),
ts_ukrow varchar(50),
preferredappointmenttimecode varchar(50),
address2_freighttermscode varchar(50),
ts_introducertype varchar(50),
accountcategorycode varchar(50),
paymenttermscode varchar(50),
preferredappointmentdaycode varchar(50),
businesstypecode varchar(50),
industrycode varchar(100),
address1_shippingmethodcode varchar(50),
ts_portfoliocompanystatus varchar(50),
customersizecode varchar(50),
preferredcontactmethodcode varchar(50),
accountratingcode varchar(50),
address2_addresstypecode varchar(50),
address2_shippingmethodcode varchar(50),
ownershipcode varchar(50),
territorycode varchar(50),
shippingmethodcode varchar(50),
address1_freighttermscode varchar(50),
ts_vendorsummary varchar(100),
mpe_livedeal varchar(50),
donotbulkpostalmail varchar(50),
donotpostalmail1 varchar(50),
msdyn_gdproptout varchar(50),
ts_organisationalchange varchar(50),
xpd_publictoprivatetracker varchar(50),
ts_auditor varchar(50),
donotsendmm varchar(50),
donotbulkemail varchar(50),
followemail varchar(50),
creditonhold varchar(50),
marketingonly varchar(50),
participatesinworkflow varchar(50),
merged varchar(50),
ts_validated varchar(50),
xpd_sharepointlocationcreated varchar(50),
xpd_setreminder varchar(50),
ts_personnelchange varchar(50),
donotfax1 varchar(50),
isprivate varchar(50),
donotphone1 varchar(50),
xpd_rpo varchar(50),
ts_trackcompany varchar(50),
ts_stageofmeetingrounds varchar(50),
XXXX varchar(50),
prioritycode varchar(50),
attachmenterrors varchar(250),
isworkflowcreated varchar(50),
isalldayevent varchar(50),
ts_interview varchar(50),
isbilled varchar(50),
isdraft varchar(50),
ts_keyiractivity varchar(50),
ts_noninvestor varchar(50),
ismapiprivate varchar(50),
haschildrencode varchar(50),
customertypecode1 varchar(50),
gendercode varchar(50),
ts_dealannouncedreason varchar(50),
ts_highprioritynew varchar(50),
ts_actionstatus varchar(50),
ts_internalintermediarysource varchar(50),
ts_currentactivity varchar(50),
ts_opportunitystatus varchar(50),
ts_opportunitytype varchar(50),
ts_posthumstatus varchar(50),
ts_meetingremindermonths varchar(50),
ts_setreminder varchar(50),
ts_stagereachedfir varchar(50),
ts_stagereachedipr varchar(50),
ts_duplicateopportunityfordiscussion varchar(50),
mpe_smalltarget varchar(50),
ts_stagereachedcr varchar(50),
xpd_includeonallpapers varchar(50),
ts_sold varchar(50),
xpd_sharepointlocationcreated1 varchar(50))
INSERT optionsettransposedall_copy VALUES
('Sender','Not Recurring','Allow','Allow','Allow','No','Allow','Tier 1','Default Value','Bill To','Default Value','UK','Morning','Default Value','Primary','Preferred Customer','Net 30','Sunday','Default Value','Accounting','Airborne','Current','Default Value','Any','Default Value','Default Value','Default Value','Public','Default Value','Default Value','FOB','Contains target','No','No','Allow','No','No','No','No','Send','Allow','Do Not Allow','No','No','No','No','No','No','No','No','Allow','No','Allow','No','No','First Management Meeting','Not Recurring','Low','None','No','No','No','No','No','No','No','No','Default Value','Default Value','Male','NOB','Yes','Chopped','Internal','1. Analysing','Anticipated','P2P','WIP','3 Months','No','No','No','No','No','No','No','--','No'),
('To Recipient','Not Recurring','Allow','Allow','Allow','Yes','Allow','Tier 2','Competitor','Default Value',NULL,'RoW','Morning','Default Value','Boutique','Standard','Net 30','Sunday',NULL,'Agriculture and Non-petrol Natural Resource Extraction','Default Value','Former','Default Value','Any',NULL,'Default Value','Default Value','Private','Default Value','Default Value','FOB','Active in M&A','Yes','No','Allow','No','Yes','Yes','Yes','Send','Allow','Do Not Allow','No','No','No','No','No','No','Yes','Yes','Allow','No','Allow','Yes','Yes','Follow Up Management Meeting','Not Recurring','Low','The appointment was saved as a Microsoft Dynamics 365 appointment record, but not all the attachments could be saved with it. An attachment cannot be saved if it is blocked or if its file type is invalid.','No','Yes','Yes','No','Yes','No','Yes','No',NULL,'Competitor','Female','Price','No','Anticipated HuM','Intermediary','2. Trying to meet','In Market','Primary','Won','6 Months','Yes','Yes','Yes','Yes','Yes','Yes','Yes','Yes','No'),
('CC Recipient','Not Recurring','Allow','Allow','Allow',NULL,'Allow','TBC','Consultant','Bill To',NULL,NULL,'Afternoon',NULL,'T2 Generalists',NULL,'2% 10, Net 30','Monday',NULL,'Broadcasting Printing and Publishing','Airborne',NULL,NULL,'Email',NULL,'Default Value','Default Value','Subsidiary',NULL,NULL,'No Charge','Operates across areas of interest',NULL,'Yes','Allow','No',NULL,NULL,NULL,'Do Not Send','Do Not Allow','Allow','Yes','Yes','Yes','Yes','Yes','No',NULL,NULL,'Allow','Yes','Allow',NULL,NULL,NULL,'Not Recurring','Low',NULL,'No',NULL,NULL,'No',NULL,'Yes',NULL,'Yes',NULL,'Consultant',NULL,'Terms',NULL,NULL,NULL,'3. Date agreed','On Hold/Frozen','Secondary','Lost','12 Months',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'No'),
('BCC Recipient','Recurring Master','Do Not Allow','Do Not Allow','Do Not Allow',NULL,'Do Not Allow',NULL,'Customer','Ship To',NULL,NULL,'Afternoon',NULL,NULL,NULL,'2% 10, Net 30','Monday',NULL,'Brokers','DHL',NULL,NULL,'Email',NULL,NULL,NULL,'Other',NULL,NULL,'No Charge','Limited targets of interest',NULL,'Yes','Do Not Allow','Yes',NULL,NULL,NULL,'Do Not Send','Do Not Allow','Allow','Yes','Yes','Yes','Yes','Yes','Yes',NULL,NULL,'Do Not Allow','Yes','Do Not Allow',NULL,NULL,NULL,'Recurring Master','Low',NULL,'No',NULL,NULL,'No',NULL,'Yes',NULL,'Yes',NULL,'Customer',NULL,'No Deal',NULL,NULL,NULL,'5. Chopped','Don''t Know',NULL,'Chopped','9 Months',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Yes'),
('Required attendee','Recurring Master','Do Not Allow','Do Not Allow','Do Not Allow',NULL,'Do Not Allow',NULL,'Investor','Ship To',NULL,NULL,'Evening',NULL,NULL,NULL,'Net 45','Tuesday',NULL,'Building Supply Retail','DHL',NULL,NULL,'Phone',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Whole business target',NULL,NULL,'Do Not Allow','Yes',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Yes',NULL,NULL,'Do Not Allow',NULL,'Do Not Allow',NULL,NULL,NULL,'Recurring Master','Low',NULL,'No',NULL,NULL,'No',NULL,NULL,NULL,NULL,NULL,'Investor',NULL,NULL,NULL,NULL,NULL,'4. Post meeting','No Process',NULL,'Faded',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Yes'),
('Optional attendee','Recurring Master','Do Not Allow','Do Not Allow','Do Not Allow',NULL,'Do Not Allow',NULL,'Partner','Primary',NULL,NULL,'Evening',NULL,NULL,NULL,'Net 45','Tuesday',NULL,'Business Services','FedEx',NULL,NULL,'Phone',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Whole group of interest',NULL,NULL,'Do Not Allow','Yes',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Yes',NULL,NULL,'Do Not Allow',NULL,'Do Not Allow',NULL,NULL,NULL,'Recurring Master','Low',NULL,'No',NULL,NULL,'No',NULL,NULL,NULL,NULL,NULL,'Partner',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Yes'),
('Organizer','Recurring Instance',NULL,NULL,NULL,NULL,NULL,NULL,'Influencer','Primary',NULL,NULL,NULL,NULL,NULL,NULL,'Net 60','Wednesday',NULL,'Consulting','FedEx',NULL,NULL,'Fax',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Unusual industrial acquirer - worth knowing',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Recurring Instance','Low',NULL,'No',NULL,NULL,'No',NULL,NULL,NULL,NULL,NULL,'Influencer',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('Regarding','Recurring Instance',NULL,NULL,NULL,NULL,NULL,NULL,'Press','Other',NULL,NULL,NULL,NULL,NULL,NULL,'Net 60','Wednesday',NULL,'Consumer Services','UPS',NULL,NULL,'Fax',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Potential future targets',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Recurring Instance','Normal',NULL,'Yes',NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,'Press',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('Owner','Recurring Instance',NULL,NULL,NULL,NULL,NULL,NULL,'Prospect','Other',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Thursday',NULL,'Design, Direction and Creative Management','UPS',NULL,NULL,'Mail',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TBC',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Recurring Instance','Normal',NULL,'Yes',NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,'Prospect',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('Resource','Recurring Exception',NULL,NULL,NULL,NULL,NULL,NULL,'Reseller',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Thursday',NULL,'Distributors, Dispatchers and Processors','Postal Mail',NULL,NULL,'Mail',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Recurring Exception','Normal',NULL,'Yes',NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,'Reseller',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('Customer','Recurring Exception',NULL,NULL,NULL,NULL,NULL,NULL,'Supplier',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Friday',NULL,'Doctor''s Offices and Clinics','Postal Mail',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Recurring Exception','Normal',NULL,'Yes',NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,'Supplier',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(NULL,'Recurring Exception',NULL,NULL,NULL,NULL,NULL,NULL,'Vendor',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Friday',NULL,'Durable Manufacturing','Full Load',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Recurring Exception','Normal',NULL,'Yes',NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,'Vendor',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(NULL,'Recurring Future Exception',NULL,NULL,NULL,NULL,NULL,NULL,'Other',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Saturday',NULL,'Eating and Drinking Places','Full Load',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Recurring Future Exception','Normal',NULL,'Yes',NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,'Other',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(NULL,'Recurring Future Exception',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Saturday',NULL,'Entertainment Retail','Will Call',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Recurring Future Exception','Normal',NULL,'Yes',NULL,NULL,'Yes',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
SELECT * FROM optionsettransposedall_copy- This topic was modified 1 year, 9 months ago by carlton 84646. Reason: update question
-
You don't have an order by on your join query so they're returning rows in different orders, try ordering that by maybe the guid and see what you get 🙂
-
I thought I included order by here:
SELECT *,ROW_NUMBER()OVER(ORDER BY prioritycode)
-
Plus the code are identical, its just the table that I see that is different
-
I'm still confused!
-
I did an orderby with the following full code, and still nothing
SELECT
C1.Id
,C1.SinkCreatedOn
,C1.SinkModifiedOn
,C1.statecode
,C1.statuscode
,CASE
WHEN ISNULL(CAST(C1.prioritycode AS VARCHAR(50)), '') <> ISNULL(CAST(C2.prioritycode AS VARCHAR(50)), '') THEN CAST(C2.prioritycode AS VARCHAR(50))
ELSE CAST(C1.prioritycode AS VARCHAR(50))
END AS prioritycode
FROM (SELECT
appointment.Id
,appointment.SinkCreatedOn
,appointment.SinkModifiedOn
,appointment.statecode
,appointment.statuscode
,appointment.ts_stageofmeetingrounds
,appointment.ts_talentmeetingtype
,appointment.instancetypecode
,appointment.ts_whooriginatedthemeeting
,appointment.onlinemeetingtype
,appointment.ts_meetingteam
,appointment.ts_othermeetingmethod
,appointment.ts_tone
,appointment.ts_meetingmethod
,appointment.ts_originationmeetingtype
,appointment.prioritycode
,appointment.attachmenterrors
,appointment.ts_eventtype
,appointment.ts_talentmeetingmethod
,appointment.ts_productsdiscussed
,appointment.ts_contentcovered
,appointment.isonlinemeeting
,appointment.isworkflowcreated
,appointment.isalldayevent
,appointment.ts_interview
,appointment.isbilled
,appointment.isdraft
,appointment.ts_keyiractivity
,appointment.ts_noninvestor
,appointment.ismapiprivate
,appointment.isregularactivity
,appointment.slaid
,appointment.slaid_entitytype
,appointment.owningbusinessunit
,appointment.owningbusinessunit_entitytype
,appointment.createdonbehalfby
,appointment.createdonbehalfby_entitytype
,appointment.ts_investorgroup
,appointment.ts_investorgroup_entitytype
,appointment.modifiedby
,appointment.modifiedby_entitytype
,appointment.modifiedonbehalfby
,appointment.modifiedonbehalfby_entitytype
,appointment.owningteam
,appointment.owningteam_entitytype
,appointment.owninguser
,appointment.owninguser_entitytype
,appointment.regardingobjectid
,appointment.regardingobjectid_entitytype
,appointment.slainvokedid
,appointment.slainvokedid_entitytype
,appointment.transactioncurrencyid
,appointment.transactioncurrencyid_entitytype
,appointment.createdby
,appointment.createdby_entitytype
,appointment.ts_officemet
,appointment.ts_officemet_entitytype
,appointment.ts_taskowner
,appointment.ts_taskowner_entitytype
,appointment.serviceid
,appointment.serviceid_entitytype
,appointment.ownerid
,appointment.ownerid_entitytype
,appointment.requiredattendees
,appointment.optionalattendees
,appointment.organizer
,appointment.scheduledend
,appointment.ts_taskdescription
,appointment.modifiedon
,appointment.ts_purpose
,appointment.overriddencreatedon
,appointment.ts_taskownername
,appointment.activityadditionalparams
,appointment.ts_tonepersonalities
,appointment.seriesid
,appointment.transactioncurrencyidname
,appointment.activityid
,appointment.modifiedfieldsmask
,appointment.utcconversiontimezonecode
,appointment.ts_officemetname
,appointment.scheduledstart
,appointment.outlookownerapptid
,appointment.ts_topicscovered
,appointment.ts_objective
,appointment.onholdtime
,appointment.onlinemeetingjoinurl
,appointment.ts_notes
,appointment.originalstartdate
,appointment.ts_opportunitiesdiscussed
,appointment.ts_sectorscoveredbyfirm
,appointment.attachmentcount
,appointment.exchangerate
,appointment.ts_originationcommentsnotes
,appointment.category
,appointment.createdon
,appointment.ts_eventdescription
,appointment.globalobjectid
,appointment.onlinemeetingid
,appointment.serviceidname
,appointment.owneridyominame
,appointment.description
,appointment.subcategory
,appointment.owningbusinessunitname
,appointment.ts_taskduedate
,appointment.traversedpath
,appointment.sortdate
,appointment.createdonbehalfbyname
,appointment.ts_sectorscoveredids
,appointment.safedescription
,appointment.ts_taskowneryominame
,appointment.owneridtype
,appointment.isunsafe
,appointment.modifiedonbehalfbyname
,appointment.createdonbehalfbyyominame
,appointment.ts_conferencename
,appointment.timezoneruleversionnumber
,appointment.actualend
,appointment.regardingobjectidname
,appointment.processid
,appointment.activitytypecode
,appointment.actualstart
,appointment.xpd_listofexternalattendees
,appointment.slainvokedidname
,appointment.location
,appointment.subject
,appointment.createdbyname
,appointment.actualdurationminutes
,appointment.modifiedbyyominame
,appointment.lastonholdtime
,appointment.modifiedonbehalfbyyominame
,appointment.ts_attendeestoaddtocrm
,appointment.regardingobjectidyominame
,appointment.regardingobjecttypecode
,appointment.stageid
,appointment.scheduleddurationminutes
,appointment.ts_sourceid
,appointment.ts_investorgroupyominame
,appointment.importsequencenumber
,appointment.subscriptionid
,appointment.onlinemeetingchatid
,appointment.ts_lpupdates
,appointment.ts_executivesummary
,appointment.ts_investorgroupname
,appointment.ts_meetingcity
,appointment.modifiedbyname
,appointment.createdbyyominame
,appointment.owneridname
,appointment.ts_theirprogramme
,appointment.ts_ifdeclinedreason
,appointment.slaname
,appointment.versionnumber
,ROW_NUMBER() OVER (ORDER BY appointment.prioritycode) AS RowNum
FROM dbo.appointment) C1
LEFT OUTER JOIN (SELECT
optionsettransposedall_why.participationtypemask
,optionsettransposedall_why.instancetypecode
,optionsettransposedall_why.donotpostalmail
,optionsettransposedall_why.donotfax
,optionsettransposedall_why.donotphone
,optionsettransposedall_why.ispartydeleted
,optionsettransposedall_why.donotemail
,optionsettransposedall_why.ts_primarysecondaryfocus
,optionsettransposedall_why.customertypecode
,optionsettransposedall_why.address1_addresstypecode
,optionsettransposedall_why.accountclassificationcode
,optionsettransposedall_why.ts_ukrow
,optionsettransposedall_why.preferredappointmenttimecode
,optionsettransposedall_why.address2_freighttermscode
,optionsettransposedall_why.ts_introducertype
,optionsettransposedall_why.accountcategorycode
,optionsettransposedall_why.paymenttermscode
,optionsettransposedall_why.preferredappointmentdaycode
,optionsettransposedall_why.businesstypecode
,optionsettransposedall_why.industrycode
,optionsettransposedall_why.address1_shippingmethodcode
,optionsettransposedall_why.ts_portfoliocompanystatus
,optionsettransposedall_why.customersizecode
,optionsettransposedall_why.preferredcontactmethodcode
,optionsettransposedall_why.accountratingcode
,optionsettransposedall_why.address2_addresstypecode
,optionsettransposedall_why.address2_shippingmethodcode
,optionsettransposedall_why.ownershipcode
,optionsettransposedall_why.territorycode
,optionsettransposedall_why.shippingmethodcode
,optionsettransposedall_why.address1_freighttermscode
,optionsettransposedall_why.ts_vendorsummary
,optionsettransposedall_why.mpe_livedeal
,optionsettransposedall_why.donotbulkpostalmail
,optionsettransposedall_why.msdyn_gdproptout
,optionsettransposedall_why.ts_organisationalchange
,optionsettransposedall_why.xpd_publictoprivatetracker
,optionsettransposedall_why.ts_auditor
,optionsettransposedall_why.donotsendmm
,optionsettransposedall_why.donotbulkemail
,optionsettransposedall_why.followemail
,optionsettransposedall_why.creditonhold
,optionsettransposedall_why.marketingonly
,optionsettransposedall_why.participatesinworkflow
,optionsettransposedall_why.merged
,optionsettransposedall_why.ts_validated
,optionsettransposedall_why.xpd_sharepointlocationcreated
,optionsettransposedall_why.xpd_setreminder
,optionsettransposedall_why.ts_personnelchange
,optionsettransposedall_why.isprivate
,optionsettransposedall_why.xpd_rpo
,optionsettransposedall_why.ts_trackcompany
,optionsettransposedall_why.ts_stageofmeetingrounds
,optionsettransposedall_why.XXXX
,optionsettransposedall_why.prioritycode
,optionsettransposedall_why.attachmenterrors
,optionsettransposedall_why.isworkflowcreated
,optionsettransposedall_why.isalldayevent
,optionsettransposedall_why.ts_interview
,optionsettransposedall_why.isbilled
,optionsettransposedall_why.isdraft
,optionsettransposedall_why.ts_keyiractivity
,optionsettransposedall_why.ts_noninvestor
,optionsettransposedall_why.ismapiprivate
,optionsettransposedall_why.haschildrencode
,optionsettransposedall_why.gendercode
,optionsettransposedall_why.ts_dealannouncedreason
,optionsettransposedall_why.ts_highprioritynew
,optionsettransposedall_why.ts_actionstatus
,optionsettransposedall_why.ts_internalintermediarysource
,optionsettransposedall_why.ts_currentactivity
,optionsettransposedall_why.ts_opportunitystatus
,optionsettransposedall_why.ts_opportunitytype
,optionsettransposedall_why.ts_posthumstatus
,optionsettransposedall_why.ts_meetingremindermonths
,optionsettransposedall_why.ts_setreminder
,optionsettransposedall_why.ts_stagereachedfir
,optionsettransposedall_why.ts_stagereachedipr
,optionsettransposedall_why.ts_duplicateopportunityfordiscussion
,optionsettransposedall_why.mpe_smalltarget
,optionsettransposedall_why.ts_stagereachedcr
,optionsettransposedall_why.xpd_includeonallpapers
,optionsettransposedall_why.ts_sold
,ROW_NUMBER() OVER (ORDER BY optionsettransposedall_why.prioritycode) AS RowNum
FROM dbo.optionsettransposedall_why) C2
ON C1.RowNum = C2.RowNum
ORDER BY prioritycode DESC -
Do the order by on the guid.
-
That join will work fine too.
Try an ORDER BY C1.RowNum so that you can see the matches between rows, if any, in order.
SELECT C1.Id
...
ON C1.RowNum = C2.RowNum
ORDER BY C1.RowNum --<<--<<--SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
as to us avoiding wasting time kindly tell us what software and db engine you are using.
with regards to trying to identify your issue change the select bit to be like the one below - that will give you the 2 fields you are getting messed by
SELECT C1.Id
, C1.SinkCreatedOn
, C1.SinkModifiedOn
, C1.statecode
, C1.statuscode
, C1.prioritycode as C1_prioritycode
, C2.prioritycode as C2_prioritycode
, CASE
WHEN ISNULL(CAST(C1.prioritycode AS VARCHAR(50)),'')
<> ISNULL(CAST(C2.prioritycode AS VARCHAR(50)),'')
THEN CAST(C2.prioritycode AS VARCHAR(50))
ELSE CAST(C1.prioritycode AS VARCHAR(50))
END AS prioritycodeps.. do always give an alias (short one) to your tables - makes reading sql a lot easier.
-
Hmmm... That's a strange way to join two tables, but you'll probably have your reasons. It doesn't make sense to me - it seems very arbitrary. Anyway the only thing I can think of is:
If your appointment table contains more rows than your optionsettransposedall_why table then you will get a null in the c2.prioritycode column of the rows that will not be joined on the rownumber.
-
Hi Scott,
Sorry for the delayed response.
I tried your suggestion with ORDER BY C1.RowNum
But I'm still getting NULLs in the prioritycode on table optionsettransposedall_why
SELECT C1.Id
,C1.SinkCreatedOn
,C1.SinkModifiedOn
,C1.statecode
,C1.statuscode,
CASE WHEN ISNULL(CAST(C1.prioritycode AS VARCHAR(50)),'') <> ISNULL(CAST(C2.prioritycode AS VARCHAR(50)),'') THEN CAST(C2.prioritycode AS VARCHAR(50))
ELSE CAST(C1.prioritycode AS VARCHAR(50))
END AS prioritycode
FROM
(
SELECT *,ROW_NUMBER()OVER(ORDER BY prioritycode)RowNum FROM appointment
)C1
LEFT JOIN
(
SELECT *,ROW_NUMBER()OVER(ORDER BY prioritycode)RowNum FROM optionsettransposedall_why
)C2
ON C1.RowNum = C2.RowNum
ORDER BY C1.RowNumThe data is definitely there, I just don't understand why I can't see it
-
Any thoughts on this guys/girls?
-
look at the data - I gave you a pointer with a sample to what you need to look at - but it is your data, your rules and you need to figure out how the joins should really work.
and answer the questions that were posted over and over.
-
Frederico, I'm confused.
How does the following code, give me the results I need:
SELECT C1.Id
, C1.SinkCreatedOn
, C1.SinkModifiedOn
, C1.statecode
, C1.statuscode
, C1.prioritycode as C1_prioritycode
, C2.prioritycode as C2_prioritycode
, CASE
WHEN ISNULL(CAST(C1.prioritycode AS VARCHAR(50)),'')
<> ISNULL(CAST(C2.prioritycode AS VARCHAR(50)),'')
THEN CAST(C2.prioritycode AS VARCHAR(50))
ELSE CAST(C1.prioritycode AS VARCHAR(50))
END AS prioritycode -
I just realised that I didn't give you sample data for the appointment table. Maybe it shed some further light on the problem
CREATE TABLE appointment (
Id nvarchar(50),
SinkCreatedOn datetime2,
SinkModifiedOn datetime2,
statecode int,
statuscode int,
ts_stageofmeetingrounds nvarchar(255),
ts_talentmeetingtype nvarchar(255),
instancetypecode nvarchar(50),
ts_whooriginatedthemeeting nvarchar(50),
onlinemeetingtype nvarchar(255),
ts_meetingteam nvarchar(50),
ts_othermeetingmethod nvarchar(255),
ts_tone nvarchar(255),
ts_meetingmethod nvarchar(50),
ts_originationmeetingtype nvarchar(50),
prioritycode nvarchar(50),
attachmenterrors nvarchar(50),
ts_eventtype nvarchar(255),
ts_talentmeetingmethod nvarchar(255),
ts_productsdiscussed nvarchar(50),
ts_contentcovered nvarchar(255),
isonlinemeeting nvarchar(255),
isworkflowcreated nvarchar(50),
isalldayevent nvarchar(50),
ts_interview nvarchar(50),
isbilled nvarchar(50),
isdraft nvarchar(50),
ts_keyiractivity nvarchar(50),
ts_noninvestor nvarchar(255),
ismapiprivate nvarchar(50),
isregularactivity nvarchar(50),
slaid nvarchar(255),
slaid_entitytype nvarchar(255),
owningbusinessunit nvarchar(50),
owningbusinessunit_entitytype nvarchar(50),
createdonbehalfby nvarchar(255),
createdonbehalfby_entitytype nvarchar(255),
ts_investorgroup nvarchar(50),
ts_investorgroup_entitytype nvarchar(50),
modifiedby nvarchar(50),
modifiedby_entitytype nvarchar(50),
modifiedonbehalfby nvarchar(255),
modifiedonbehalfby_entitytype nvarchar(255),
owningteam nvarchar(255),
owningteam_entitytype nvarchar(255),
owninguser nvarchar(50),
owninguser_entitytype nvarchar(50),
regardingobjectid nvarchar(50),
regardingobjectid_entitytype nvarchar(50),
slainvokedid nvarchar(255),
slainvokedid_entitytype nvarchar(255),
transactioncurrencyid nvarchar(255),
transactioncurrencyid_entitytype nvarchar(255),
createdby nvarchar(50),
createdby_entitytype nvarchar(50),
ts_officemet nvarchar(255),
ts_officemet_entitytype nvarchar(255),
ts_taskowner nvarchar(255),
ts_taskowner_entitytype nvarchar(255),
serviceid nvarchar(255),
serviceid_entitytype nvarchar(255),
ownerid nvarchar(50),
ownerid_entitytype nvarchar(50),
requiredattendees nvarchar(50),
optionalattendees nvarchar(255),
organizer nvarchar(50),
scheduledend datetime2,
ts_taskdescription nvarchar(255),
modifiedon datetime2,
ts_purpose nvarchar(255),
overriddencreatedon datetime2,
ts_taskownername nvarchar(255),
activityadditionalparams nvarchar(100),
ts_tonepersonalities nvarchar(150),
seriesid nvarchar(255),
transactioncurrencyidname nvarchar(255),
activityid nvarchar(50),
modifiedfieldsmask nvarchar(255),
utcconversiontimezonecode nvarchar(255),
ts_officemetname nvarchar(255),
scheduledstart datetime2,
outlookownerapptid nvarchar(255),
ts_topicscovered nvarchar(300),
ts_objective nvarchar(255),
onholdtime nvarchar(255),
onlinemeetingjoinurl nvarchar(255),
ts_notes nvarchar(2350),
originalstartdate nvarchar(255),
ts_opportunitiesdiscussed nvarchar(255),
ts_sectorscoveredbyfirm nvarchar(255),
attachmentcount int,
exchangerate nvarchar(255),
ts_originationcommentsnotes nvarchar(255),
category nvarchar(255),
createdon datetime2,
ts_eventdescription nvarchar(255),
globalobjectid nvarchar(255),
onlinemeetingid nvarchar(255),
serviceidname nvarchar(255),
owneridyominame nvarchar(50),
description nvarchar(255),
subcategory nvarchar(255),
owningbusinessunitname nvarchar(50),
ts_taskduedate nvarchar(255),
traversedpath nvarchar(255),
sortdate datetime2,
createdonbehalfbyname nvarchar(255),
ts_sectorscoveredids nvarchar(255),
safedescription nvarchar(255),
ts_taskowneryominame nvarchar(255),
owneridtype nvarchar(255),
isunsafe int,
modifiedonbehalfbyname nvarchar(255),
createdonbehalfbyyominame nvarchar(255),
ts_conferencename nvarchar(255),
timezoneruleversionnumber int,
actualend datetime2,
regardingobjectidname nvarchar(50),
processid nvarchar(255),
activitytypecode nvarchar(50),
actualstart nvarchar(255),
xpd_listofexternalattendees nvarchar(255),
slainvokedidname nvarchar(255),
location nvarchar(50),
subject nvarchar(150),
createdbyname nvarchar(50),
actualdurationminutes int,
modifiedbyyominame nvarchar(50),
lastonholdtime nvarchar(255),
modifiedonbehalfbyyominame nvarchar(255),
ts_attendeestoaddtocrm nvarchar(255),
regardingobjectidyominame nvarchar(255),
regardingobjecttypecode nvarchar(255),
stageid nvarchar(255),
scheduleddurationminutes int,
ts_sourceid int,
ts_investorgroupyominame nvarchar(255),
importsequencenumber nvarchar(255),
subscriptionid nvarchar(255),
onlinemeetingchatid nvarchar(255),
ts_lpupdates nvarchar(255),
ts_executivesummary nvarchar(300),
ts_investorgroupname nvarchar(100),
ts_meetingcity nvarchar(50),
modifiedbyname nvarchar(50),
createdbyyominame nvarchar(50),
owneridname nvarchar(50),
ts_theirprogramme nvarchar(1650),
ts_ifdeclinedreason nvarchar(255),
slaname nvarchar(255),
versionnumber int)
INSERT appointment VALUES
(N'6ec988ae-b089-e811-a95f-00224800c719',CONVERT(DATETIME2, '2022-05-19 16:13:15.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:13:15.0000000', 121),1,3,N'',N'',N'0',N'717750000',N'',N'717750000',N'',N'',NULL,NULL,N'1',N'0',N'',N'',NULL,N'',N'',N'0',N'0',N'0',N'0',N'0',N'1',N'',N'0',N'1',N'',N'',N'47b5e190-c26b-e811-a96f-002248003cb8',N'businessunit',N'',N'',N'',N'',N'e9947e6f-6ae0-ea11-a81b-000d3a86b5bd',N'systemuser',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'6ec988ae-b089-e811-a95f-00224800c719',N'',N'6ec988ae-b089-e811-a95f-00224800c719',CONVERT(DATETIME2, '2005-06-01 08:54:00.0000000', 121),N'',CONVERT(DATETIME2, '2020-09-24 08:21:38.0000000', 121),N'',CONVERT(DATETIME2, '2018-07-17 11:00:59.0000000', 121),N'',N'{"scheduledstartformatted":"01/06/2005 08:54","scheduledendformatted":"01/06/2005 08:54"}',N'',N'',N'',N'6ec988ae-b089-e811-a95f-00224800c719',N'',N'',N'',CONVERT(DATETIME2, '2005-06-01 08:54:00.0000000', 121),N'',N'',N'',N'',N'',N'Abbott CapitalParis team DD mtg
Meeting with T. Gray on 1 June (DL MG SBD)
He let us speak really, presenting ourselves (previous experience + why we joined), the office, the French market and the way we had done deals.
Main questions:
- our competitors on the French market, and if we were seeing Vestar and Advent
- he asked a lot about Candover (they are considering an investment), why Spencer left, what we thought about Chevrillon and Coulot (mmmh)
- he was surprised that vendors in France were not systematically auctioning their assets (ex. Actaris)
He was not too talkative on what he thinks about Montagu really',N'',N'',N'',0,N'',N'',N'',CONVERT(DATETIME2, '2005-06-01 09:54:00.0000000', 121),N'',N'',N'',N'',N'Data.Import #',N'',N'',N'montaguequity',N'',N'',CONVERT(DATETIME2, '2005-06-01 08:54:00.0000000', 121),N'',N'',N'',N'',N'',0,N'',N'',N'',4,CONVERT(DATETIME2, '2020-09-07 15:22:13.0000000', 121),N'',N'',N'appointment',N'',N'',N'',N'',N'Abbott CapitalParis team DD mtgNote',N'Data.Import #',0,N'James Cann',N'',N'',N'',N'',N'',N'',0,241606,N'',N'',N'',N'',N'',N'',N'',N'',N'James Cann',N'Data.Import #',N'Data.Import #',N'',N'',N'',106001186),
(N'94bff31b-b189-e811-a95f-00224800c719',CONVERT(DATETIME2, '2022-05-19 16:13:15.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:13:15.0000000', 121),1,3,N'',N'',N'0',N'717750000',N'',N'717750000',N'',N'',NULL,NULL,N'1',N'0',N'',N'',NULL,N'',N'',N'0',N'0',N'0',N'0',N'0',N'1',N'',N'0',N'1',N'',N'',N'47b5e190-c26b-e811-a96f-002248003cb8',N'businessunit',N'',N'',N'',N'',N'e9947e6f-6ae0-ea11-a81b-000d3a86b5bd',N'systemuser',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'94bff31b-b189-e811-a95f-00224800c719',N'',N'94bff31b-b189-e811-a95f-00224800c719',CONVERT(DATETIME2, '2005-06-08 09:30:00.0000000', 121),N'',CONVERT(DATETIME2, '2020-09-24 08:21:38.0000000', 121),N'',CONVERT(DATETIME2, '2018-07-17 11:04:03.0000000', 121),N'',N'{"scheduledstartformatted":"08/06/2005 09:30","scheduledendformatted":"08/06/2005 09:30"}',N'',N'',N'',N'94bff31b-b189-e811-a95f-00224800c719',N'',N'',N'',CONVERT(DATETIME2, '2005-06-08 09:30:00.0000000', 121),N'',N'',N'',N'',N'',N'KVH had a call from Chris Welsh from Meketa. Meketa is a Pension fund adviser in the US and they have some clients who in invest in Standard Life''s fund. Standard Life has given him our name.
He would like to come in next week to meet you and to have an introduction to the business with a view to building a relationship for the next fund.',N'',N'',N'',0,N'',N'',N'',CONVERT(DATETIME2, '2005-06-08 10:30:00.0000000', 121),N'',N'',N'',N'',N'Data.Import #',N'',N'',N'montaguequity',N'',N'',CONVERT(DATETIME2, '2005-06-08 09:30:00.0000000', 121),N'',N'',N'',N'',N'',0,N'',N'',N'',4,CONVERT(DATETIME2, '2020-09-07 15:35:06.0000000', 121),N'',N'',N'appointment',N'',N'',N'',N'',N'CMM/VOB mtg with Chris Welch in London',N'Data.Import #',0,N'James Cann',N'',N'',N'',N'',N'',N'',0,242811,N'',N'',N'',N'',N'',N'',N'',N'',N'James Cann',N'Data.Import #',N'Data.Import #',N'',N'',N'',106001190),
(N'ddd19598-b189-e811-a95d-00224800bb9b',CONVERT(DATETIME2, '2022-05-19 16:13:15.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:13:15.0000000', 121),1,3,N'',N'',N'0',N'717750000',N'',N'717750000',N'',N'',NULL,NULL,N'1',N'0',N'',N'',NULL,N'',N'',N'0',N'0',N'0',N'0',N'0',N'1',N'',N'0',N'1',N'',N'',N'47b5e190-c26b-e811-a96f-002248003cb8',N'businessunit',N'',N'',N'',N'',N'e9947e6f-6ae0-ea11-a81b-000d3a86b5bd',N'systemuser',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'ddd19598-b189-e811-a95d-00224800bb9b',N'',N'ddd19598-b189-e811-a95d-00224800bb9b',CONVERT(DATETIME2, '2005-06-09 08:59:00.0000000', 121),N'',CONVERT(DATETIME2, '2020-09-24 08:21:38.0000000', 121),N'',CONVERT(DATETIME2, '2018-07-17 11:07:34.0000000', 121),N'',N'{"scheduledstartformatted":"09/06/2005 08:59","scheduledendformatted":"09/06/2005 08:59"}',N'',N'',N'',N'ddd19598-b189-e811-a95d-00224800bb9b',N'',N'',N'',CONVERT(DATETIME2, '2005-06-09 08:59:00.0000000', 121),N'',N'',N'',N'',N'',N'VOB/KVH Markus Voelkel/ Norbert Jakobs
Ø Allconsult makes investments on behalf of the Ackermann family.
Ø They may come through the KG fund
Ø VOB had previously met Markus re: Scheme
Ø Told them about the 2.3bn fund size
Ø Interested in how long the team has been working together
Ø What is our deal source
Ø Interested in investments that we have written off or that haven''t done so well
Ø Wanted to know when we stop investing in a company to put it back on track
Ø They think that those successful managers stop putting cash into the investment quickly if it is going wrong',N'',N'',N'',0,N'',N'',N'',CONVERT(DATETIME2, '2005-06-09 09:59:00.0000000', 121),N'',N'',N'',N'',N'Data.Import #',N'',N'',N'montaguequity',N'',N'',CONVERT(DATETIME2, '2005-06-09 08:59:00.0000000', 121),N'',N'',N'',N'',N'',0,N'',N'',N'',4,CONVERT(DATETIME2, '2020-09-07 16:11:31.0000000', 121),N'',N'',N'appointment',N'',N'',N'',N'',N'VOB KVH Markus V',N'Data.Import #',0,N'James Cann',N'',N'',N'',N'',N'',N'',0,244912,N'',N'',N'',N'',N'',N'',N'',N'',N'James Cann',N'Data.Import #',N'Data.Import #',N'',N'',N'',106001194),
(N'bf8751b8-b089-e811-a95d-00224800bb9b',CONVERT(DATETIME2, '2022-05-19 16:13:18.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:13:18.0000000', 121),1,3,N'',N'',N'0',N'717750000',N'',N'717750000',N'',N'',NULL,NULL,N'1',N'0',N'',N'',NULL,N'',N'',N'0',N'0',N'0',N'0',N'0',N'1',N'',N'0',N'1',N'',N'',N'47b5e190-c26b-e811-a96f-002248003cb8',N'businessunit',N'',N'',N'',N'',N'e9947e6f-6ae0-ea11-a81b-000d3a86b5bd',N'systemuser',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'bf8751b8-b089-e811-a95d-00224800bb9b',N'',N'bf8751b8-b089-e811-a95d-00224800bb9b',CONVERT(DATETIME2, '2005-07-08 09:02:00.0000000', 121),N'',CONVERT(DATETIME2, '2020-09-24 08:21:38.0000000', 121),N'',CONVERT(DATETIME2, '2018-07-17 11:01:21.0000000', 121),N'',N'{"scheduledstartformatted":"08/07/2005 09:02","scheduledendformatted":"08/07/2005 09:02"}',N'',N'',N'',N'bf8751b8-b089-e811-a95d-00224800bb9b',N'',N'',N'',CONVERT(DATETIME2, '2005-07-08 09:02:00.0000000', 121),N'',N'',N'',N'',N'',N'Ronan Cunningham, Arnaud de Cremiers, CMM/VOB/KVH
Arnaud was previously at Access Partners in Paris
Ø Meeting went well, he was impressed by the shape of the portfolio.
Ø Definitely interested, would like to get cracking with DD
Ø Would be interested in any secondary opportunities if they come up in the current or the previous funds.
Ø Don''t currently do co-invest, spoke to others about it and the only uplift is no carry/fees
Follow-ups
Ø Wants to meet some of the cont. Europe teams, said he would get in touch when he is there.
Ø Interested in looking at deal flow and geographic split further',N'',N'',N'',0,N'',N'',N'',CONVERT(DATETIME2, '2005-07-08 10:02:00.0000000', 121),N'',N'',N'',N'',N'Data.Import #',N'',N'',N'montaguequity',N'',N'',CONVERT(DATETIME2, '2005-07-08 09:02:00.0000000', 121),N'',N'',N'',N'',N'',0,N'',N'',N'',4,CONVERT(DATETIME2, '2020-09-07 15:59:32.0000000', 121),N'',N'',N'appointment',N'',N'',N'',N'',N'Note',N'Data.Import #',0,N'James Cann',N'',N'',N'',N'',N'',N'',0,241702,N'',N'',N'',N'',N'',N'',N'',N'',N'James Cann',N'Data.Import #',N'Data.Import #',N'',N'',N'',106001198),
(N'5cdbbe50-b189-e811-a95d-00224800cf35',CONVERT(DATETIME2, '2022-05-19 16:13:18.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:13:18.0000000', 121),1,3,N'',N'',N'0',N'717750000',N'',N'717750000',N'',N'',NULL,NULL,N'1',N'0',N'',N'',NULL,N'',N'',N'0',N'0',N'0',N'0',N'0',N'1',N'',N'0',N'1',N'',N'',N'47b5e190-c26b-e811-a96f-002248003cb8',N'businessunit',N'',N'',N'',N'',N'e9947e6f-6ae0-ea11-a81b-000d3a86b5bd',N'systemuser',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'5cdbbe50-b189-e811-a95d-00224800cf35',N'',N'5cdbbe50-b189-e811-a95d-00224800cf35',CONVERT(DATETIME2, '2005-07-14 15:47:00.0000000', 121),N'',CONVERT(DATETIME2, '2020-09-24 08:21:39.0000000', 121),N'',CONVERT(DATETIME2, '2018-07-17 11:05:37.0000000', 121),N'',N'{"scheduledstartformatted":"14/07/2005 15:47","scheduledendformatted":"14/07/2005 15:47"}',N'',N'',N'',N'5cdbbe50-b189-e811-a95d-00224800cf35',N'',N'',N'',CONVERT(DATETIME2, '2005-07-14 15:47:00.0000000', 121),N'',N'',N'',N'',N'',N'',N'',N'',N'',0,N'',N'',N'',CONVERT(DATETIME2, '2005-07-14 16:47:00.0000000', 121),N'',N'',N'',N'',N'Data.Import #',N'',N'',N'montaguequity',N'',N'',CONVERT(DATETIME2, '2005-07-14 15:47:00.0000000', 121),N'',N'',N'',N'',N'',0,N'',N'',N'',4,CONVERT(DATETIME2, '2020-09-07 15:35:13.0000000', 121),N'',N'',N'appointment',N'',N'',N'',N'',N'CMM/VOB/KVH met with Andreas Kosse & Mr Steer + 2 in London',N'Data.Import #',0,N'James Cann',N'',N'',N'',N'',N'',N'',0,243421,N'',N'',N'',N'',N'',N'',N'',N'',N'James Cann',N'Data.Import #',N'Data.Import #',N'',N'',N'',106001202),
(N'b55974e4-b089-e811-a95e-00224800c940',CONVERT(DATETIME2, '2022-05-19 16:13:28.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:13:28.0000000', 121),1,3,N'',N'',N'0',N'717750000',N'',N'717750000',N'',N'',NULL,NULL,N'1',N'0',N'',N'',NULL,N'',N'',N'0',N'0',N'0',N'0',N'0',N'1',N'',N'0',N'1',N'',N'',N'47b5e190-c26b-e811-a96f-002248003cb8',N'businessunit',N'',N'',N'',N'',N'e9947e6f-6ae0-ea11-a81b-000d3a86b5bd',N'systemuser',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'b55974e4-b089-e811-a95e-00224800c940',N'',N'b55974e4-b089-e811-a95e-00224800c940',CONVERT(DATETIME2, '2005-10-03 15:55:00.0000000', 121),N'',CONVERT(DATETIME2, '2020-09-24 08:21:39.0000000', 121),N'',CONVERT(DATETIME2, '2018-07-17 11:02:29.0000000', 121),N'',N'{"scheduledstartformatted":"03/10/2005 15:55","scheduledendformatted":"03/10/2005 15:55"}',N'',N'',N'',N'b55974e4-b089-e811-a95e-00224800c940',N'',N'',N'',CONVERT(DATETIME2, '2005-10-03 15:55:00.0000000', 121),N'',N'',N'',N'',N'',N'CMM/VOB = 3pm - 3.30pm
Nigel Hammond = 3.30pm - 4pm
Mark Dunfoy = 4pm - 4.30pm
Jason Gatenby = 4.30pm - 5pm',N'',N'',N'',0,N'',N'',N'',CONVERT(DATETIME2, '2005-10-03 16:55:00.0000000', 121),N'',N'',N'',N'',N'Data.Import #',N'',N'',N'montaguequity',N'',N'',CONVERT(DATETIME2, '2005-10-03 15:55:00.0000000', 121),N'',N'',N'',N'',N'',0,N'',N'',N'',4,CONVERT(DATETIME2, '2020-09-07 15:59:25.0000000', 121),N'',N'',N'appointment',N'',N'',N'',N'',N'Nicole Belytschko met in London (when at Comprehensive)',N'Data.Import #',0,N'James Cann',N'',N'',N'',N'',N'',N'',0,242120,N'',N'',N'',N'',N'',N'',N'',N'',N'James Cann',N'Data.Import #',N'Data.Import #',N'',N'',N'',106001227),
(N'53647566-b189-e811-a95e-00224800ce20',CONVERT(DATETIME2, '2022-05-19 16:13:28.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:13:28.0000000', 121),1,3,N'',N'',N'0',N'717750000',N'',N'717750000',N'',N'',NULL,NULL,N'1',N'0',N'',N'',NULL,N'',N'',N'0',N'0',N'0',N'0',N'0',N'1',N'',N'0',N'1',N'',N'',N'47b5e190-c26b-e811-a96f-002248003cb8',N'businessunit',N'',N'',N'',N'',N'e9947e6f-6ae0-ea11-a81b-000d3a86b5bd',N'systemuser',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'53647566-b189-e811-a95e-00224800ce20',N'',N'53647566-b189-e811-a95e-00224800ce20',CONVERT(DATETIME2, '2005-10-05 09:02:00.0000000', 121),N'',CONVERT(DATETIME2, '2020-09-24 08:21:40.0000000', 121),N'',CONVERT(DATETIME2, '2018-07-17 11:06:15.0000000', 121),N'',N'{"scheduledstartformatted":"05/10/2005 09:02","scheduledendformatted":"05/10/2005 09:02"}',N'',N'',N'',N'53647566-b189-e811-a95e-00224800ce20',N'',N'',N'',CONVERT(DATETIME2, '2005-10-05 09:02:00.0000000', 121),N'',N'',N'',N'',N'',N'VOB, NDH, MPD
John Mogg, Investment Advisor
John Adams, Director, Investments
John and John were very friendly. They met Chris earlier in the year in Washington but wouldn''t have had enough time to process a commitment to Montagu III.
Work as a pair together with Kimberley, who has just gone off to have her first child. Chris met her when he called in.
$6 billion portfolio of which about 8% is allocated to private equity
Invested in 16/18 GPs across venture and MBO''s.
In Europe have invested in:
· Charterhouse
· Phoenix
· Schroder Life Sciences (over for the AGM)
Would like to stay in touch. I said we would mail them our quarterly newsletter.
Action:
Add to potentials
VOB to send thank you letter',N'',N'',N'',0,N'',N'',N'',CONVERT(DATETIME2, '2005-10-05 10:02:00.0000000', 121),N'',N'',N'',N'',N'Data.Import #',N'',N'',N'montaguequity',N'',N'',CONVERT(DATETIME2, '2005-10-05 09:02:00.0000000', 121),N'',N'',N'',N'',N'',0,N'',N'',N'',4,CONVERT(DATETIME2, '2020-09-07 16:12:15.0000000', 121),N'',N'',N'appointment',N'',N'',N'',N'',N'VOB/MPD/NDH + John Adams & John Mogg',N'Data.Import #',0,N'James Cann',N'',N'',N'',N'',N'',N'',0,243693,N'',N'',N'',N'',N'',N'',N'',N'',N'James Cann',N'Data.Import #',N'Data.Import #',N'',N'',N'',106001231),
(N'199f1f27-b189-e811-a95e-00224800c940',CONVERT(DATETIME2, '2022-05-19 16:13:28.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:13:28.0000000', 121),1,3,N'',N'',N'0',N'717750000',N'',N'717750000',N'',N'',NULL,NULL,N'1',N'0',N'',N'',N'717750000',N'',N'',N'0',N'0',N'0',N'0',N'0',N'1',N'',N'0',N'1',N'',N'',N'47b5e190-c26b-e811-a96f-002248003cb8',N'businessunit',N'',N'',N'4866d0fe-dd7a-e811-a95c-00224800c9ff',N'account',N'e9947e6f-6ae0-ea11-a81b-000d3a86b5bd',N'systemuser',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'4866d0fe-dd7a-e811-a95c-00224800c9ff',N'account',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'199f1f27-b189-e811-a95e-00224800c940',N'',N'199f1f27-b189-e811-a95e-00224800c940',CONVERT(DATETIME2, '2005-10-05 15:52:00.0000000', 121),N'',CONVERT(DATETIME2, '2020-09-24 08:21:40.0000000', 121),N'',CONVERT(DATETIME2, '2018-07-17 11:04:26.0000000', 121),N'',N'{"scheduledstartformatted":"05/10/2005 15:52","scheduledendformatted":"05/10/2005 15:52"}',N'',N'',N'',N'199f1f27-b189-e811-a95e-00224800c940',N'',N'',N'',CONVERT(DATETIME2, '2005-10-05 15:52:00.0000000', 121),N'',N'',N'',N'',N'',N'Northrop Grumman
Note of Potential Investor meeting 5 October 2005
In attendance:
VOB, NDH, CJG
Rajender ("Raj") Chandhok - VP
Andy Ward - Corporate Director
Introduced by Marc Boheim of Goldman Sachs
NG is a large defence business based in Los Angeles. The pension fund has a bigger capitalisation than the company.
Want to stay in touch so we will add to our target list and send them quarterly newsletter etc
Total assets of $30 billion
Started building portfolio in 1998
Now have pe commitments of 2.5 billion of which 10% is overseas
Relationships with
· Permira
· Carlyle
· EAC
· Sovereign
· Apax France
· Odevalde
Also have venture capital relationships:
· Merlin
· Schroder Life Sciences
· Warburg Pincus
They asked if we would let them attend investor meetings - I said we had no problem with this but they would only come if they happened to be over here at the time, so I think this is unlikely to materialise.
Action:
Ø Definitely one to stay in touch with.
Ø Add to Potentials
Ø Visit next time we are over there
Ø VOB to drop them a thank you letter',N'',N'',N'',0,N'',N'',N'',CONVERT(DATETIME2, '2005-10-05 16:52:00.0000000', 121),N'',N'',N'',N'',N'Data.Import #',N'',N'',N'montaguequity',N'',N'',CONVERT(DATETIME2, '2005-10-05 15:52:00.0000000', 121),N'',N'',N'',N'',N'',0,N'',N'',N'',4,CONVERT(DATETIME2, '2020-09-07 15:58:50.0000000', 121),N'Northrop Grumman Corporation',N'',N'appointment',N'',N'',N'',N'',N'Mtg note Oct 05 VOB/NDH/CJG',N'Data.Import #',0,N'James Cann',N'',N'',N'',N'',N'',N'',0,242979,N'',N'',N'',N'',N'',N'',N'Northrop Grumman Corporation',N'',N'James Cann',N'Data.Import #',N'Data.Import #',N'',N'',N'',106001235),
(N'0f93222a-b189-e811-a95d-00224800cf35',CONVERT(DATETIME2, '2022-05-19 16:13:28.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:13:28.0000000', 121),1,3,N'',N'',N'0',N'717750000',N'',N'717750000',N'',N'',NULL,NULL,N'1',N'0',N'',N'',N'717750000',N'',N'',N'0',N'0',N'0',N'0',N'0',N'1',N'',N'0',N'1',N'',N'',N'47b5e190-c26b-e811-a96f-002248003cb8',N'businessunit',N'',N'',N'',N'',N'e9947e6f-6ae0-ea11-a81b-000d3a86b5bd',N'systemuser',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'0f93222a-b189-e811-a95d-00224800cf35',N'',N'0f93222a-b189-e811-a95d-00224800cf35',CONVERT(DATETIME2, '2005-10-05 16:00:00.0000000', 121),N'',CONVERT(DATETIME2, '2020-09-24 08:21:40.0000000', 121),N'',CONVERT(DATETIME2, '2018-07-17 11:04:26.0000000', 121),N'',N'{"scheduledstartformatted":"05/10/2005 16:00","scheduledendformatted":"05/10/2005 16:00"}',N'',N'',N'',N'0f93222a-b189-e811-a95d-00224800cf35',N'',N'',N'',CONVERT(DATETIME2, '2005-10-05 16:00:00.0000000', 121),N'',N'',N'',N'',N'',N'Mtg was set up by GS AM
Northrop Grumman''s Raj Chandhok, CIO, and Andy Ward, head of private equity, are visiting Europe in early October to meet with a number of fund managers and get market views from a few leading LPs in the market.
Northrop Grumman is one of the leading defence contractors in the US, based in Los Angeles. They have a $17bn pension plan with about $2bn committed to about 100 private equity funds. They have a 5% allocation to PE. They recently took control of their private equity program from West AM which managed it for several years. They are going to be making direct commitments to funds and have already begun to do some re-ups with existing managers as well as making commitments to new managers. They will probably commit about $200mm to $250mm each year to private equity.',N'',N'',N'',0,N'',N'',N'',CONVERT(DATETIME2, '2005-10-05 17:00:00.0000000', 121),N'',N'',N'',N'',N'Data.Import #',N'',N'',N'montaguequity',N'',N'',CONVERT(DATETIME2, '2005-10-05 16:00:00.0000000', 121),N'',N'',N'',N'',N'',0,N'',N'',N'',4,CONVERT(DATETIME2, '2020-09-07 15:59:10.0000000', 121),N'',N'',N'appointment',N'',N'',N'',N'',N'NDH/CJG met with Raj and Andy in London',N'Data.Import #',0,N'James Cann',N'',N'',N'',N'',N'',N'',0,242980,N'',N'',N'',N'',N'',N'',N'',N'',N'James Cann',N'Data.Import #',N'Data.Import #',N'',N'',N'',106001241),
(N'c5718fa4-b189-e811-a95d-00224800bb9b',CONVERT(DATETIME2, '2022-05-19 16:13:28.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:13:28.0000000', 121),1,3,N'',N'',N'0',NULL,N'',N'717750000',N'',N'',NULL,NULL,N'1',N'0',N'',N'',NULL,N'',N'',N'0',N'0',N'0',N'0',N'0',N'1',N'',N'0',N'1',N'',N'',N'47b5e190-c26b-e811-a96f-002248003cb8',N'businessunit',N'',N'',N'',N'',N'e9947e6f-6ae0-ea11-a81b-000d3a86b5bd',N'systemuser',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'c5718fa4-b189-e811-a95d-00224800bb9b',N'',N'c5718fa4-b189-e811-a95d-00224800bb9b',CONVERT(DATETIME2, '2005-10-07 16:01:00.0000000', 121),N'',CONVERT(DATETIME2, '2020-09-24 08:21:40.0000000', 121),N'',CONVERT(DATETIME2, '2018-07-17 11:07:58.0000000', 121),N'',N'{"scheduledstartformatted":"07/10/2005 16:01","scheduledendformatted":"07/10/2005 16:01"}',N'',N'',N'',N'c5718fa4-b189-e811-a95d-00224800bb9b',N'',N'',N'',CONVERT(DATETIME2, '2005-10-07 16:01:00.0000000', 121),N'',N'',N'',N'',N'',N'',N'',N'',N'',0,N'',N'',N'',CONVERT(DATETIME2, '2005-10-07 17:01:00.0000000', 121),N'',N'',N'',N'',N'Data.Import #',N'',N'',N'montaguequity',N'',N'',CONVERT(DATETIME2, '2005-10-07 16:01:00.0000000', 121),N'',N'',N'',N'',N'',0,N'',N'',N'',4,CONVERT(DATETIME2, '2020-09-07 15:58:37.0000000', 121),N'',N'',N'appointment',N'',N'',N'',N'',N'MPD/VOB call with Tim & Pinal to give an update on the portfolio',N'Data.Import #',0,N'James Cann',N'',N'',N'',N'',N'',N'',0,245148,N'',N'',N'',N'',N'',N'',N'',N'',N'James Cann',N'Data.Import #',N'Data.Import #',N'',N'',N'',106001245),
(N'86dcd182-b189-e811-a95d-00224800c5df',CONVERT(DATETIME2, '2022-05-19 16:13:28.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:13:28.0000000', 121),1,3,N'',N'',N'0',N'717750000',N'',N'717750000',N'',N'',NULL,NULL,N'1',N'0',N'',N'',N'717750000',N'',N'',N'0',N'0',N'0',N'0',N'0',N'1',N'',N'0',N'1',N'',N'',N'47b5e190-c26b-e811-a96f-002248003cb8',N'businessunit',N'',N'',N'',N'',N'e9947e6f-6ae0-ea11-a81b-000d3a86b5bd',N'systemuser',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'86dcd182-b189-e811-a95d-00224800c5df',N'',N'86dcd182-b189-e811-a95d-00224800c5df',CONVERT(DATETIME2, '2005-10-07 16:03:00.0000000', 121),N'',CONVERT(DATETIME2, '2020-09-24 08:21:40.0000000', 121),N'',CONVERT(DATETIME2, '2018-07-17 11:06:58.0000000', 121),N'',N'{"scheduledstartformatted":"07/10/2005 16:03","scheduledendformatted":"07/10/2005 16:03"}',N'',N'',N'',N'86dcd182-b189-e811-a95d-00224800c5df',N'',N'',N'',CONVERT(DATETIME2, '2005-10-07 16:03:00.0000000', 121),N'',N'',N'',N'',N'',N'',N'',N'',N'',0,N'',N'',N'',CONVERT(DATETIME2, '2005-10-07 17:03:00.0000000', 121),N'',N'',N'',N'',N'Data.Import #',N'',N'',N'montaguequity',N'',N'',CONVERT(DATETIME2, '2005-10-07 16:03:00.0000000', 121),N'',N'',N'',N'',N'',0,N'',N'',N'',4,CONVERT(DATETIME2, '2020-09-07 16:11:59.0000000', 121),N'',N'',N'appointment',N'',N'',N'',N'',N'VOB/CMM/KVH met at VP with Helen, Dushy and Charles',N'Data.Import #',0,N'James Cann',N'',N'',N'',N'',N'',N'',0,244101,N'',N'',N'',N'',N'',N'- Advisory board - Helen very keen to be a member, we said observer is fine, and perhaps she could move onto the board after the first meeting
- They always meet the manager soon after the close of a fund
- Explained the situation with PWG, and that he i',N'',N'',N'James Cann',N'Data.Import #',N'Data.Import #',N'',N'',N'',106001249),
(N'ad0503d2-b089-e811-a95d-00224800cf35',CONVERT(DATETIME2, '2022-05-19 16:13:28.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:13:28.0000000', 121),1,3,N'',N'',N'0',N'717750000',N'',N'717750000',N'',N'',NULL,NULL,N'1',N'0',N'',N'',NULL,N'',N'',N'0',N'0',N'0',N'0',N'0',N'1',N'',N'0',N'1',N'',N'',N'47b5e190-c26b-e811-a96f-002248003cb8',N'businessunit',N'',N'',N'',N'',N'e9947e6f-6ae0-ea11-a81b-000d3a86b5bd',N'systemuser',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'ad0503d2-b089-e811-a95d-00224800cf35',N'',N'ad0503d2-b089-e811-a95d-00224800cf35',CONVERT(DATETIME2, '2005-10-10 09:15:00.0000000', 121),N'',CONVERT(DATETIME2, '2020-09-24 08:21:40.0000000', 121),N'',CONVERT(DATETIME2, '2018-07-17 11:01:58.0000000', 121),N'',N'{"scheduledstartformatted":"10/10/2005 09:15","scheduledendformatted":"10/10/2005 09:15"}',N'',N'',N'',N'ad0503d2-b089-e811-a95d-00224800cf35',N'',N'',N'',CONVERT(DATETIME2, '2005-10-10 09:15:00.0000000', 121),N'',N'',N'',N'',N'',N'Vince & I met with Vicky and Kayte on 10 Oct 05. Below are the meeting notes:
Ø Vicky joined in July 2005 from Shell and is responsible for helping to diversify the portfolio and increase allocation
Ø Kayte is head of alternative investments
Ø BA started investing in PE in 1992 to diversify their portfolio
Ø The portfolio is currently made up of approx 50 funds with a current market value of £133m
Ø They are looking to increase their allocation to 5% from 1.5% in one of their two programmes (they have two schemes, and they are looking to increase allocation of one of them further into PE)
Ø They are investing in Europe/US and possibly Asia
Ø Focus is mainly the mid market, and they don''t have much at the larger end of the buyout market
Ø Will do direct investments
Ø David Gamble (former Chief Exec) has gone and is now involved as a non-exec director at Gartmore amongst other things
Ø Vicky keen to maintain the relationship with Montagu, and was sorry to leave us when she left Shell
Ø Took them through Montagu''s strategy and gave them an information presentation (for Kayte''s sake)
Ø Kayte interested to know if we are multiple or IRR driven
Kayte works part time (Mon-Wed) and seemed to be quite a tough cookie, although Vicky is very keen to remain in touch with Montagu IV in mind. Vicky also asked us to contact her if any of our MIII LPs pull out of the fund.',N'',N'',N'',0,N'',N'',N'',CONVERT(DATETIME2, '2005-10-10 10:15:00.0000000', 121),N'',N'',N'',N'',N'Data.Import #',N'',N'',N'montaguequity',N'',N'',CONVERT(DATETIME2, '2005-10-10 09:15:00.0000000', 121),N'',N'',N'',N'',N'',0,N'',N'',N'',4,CONVERT(DATETIME2, '2020-09-07 16:12:09.0000000', 121),N'',N'',N'appointment',N'',N'',N'',N'',N'VOB/KVH mtg 10 Oct 05',N'Data.Import #',0,N'James Cann',N'',N'',N'',N'',N'',N'',0,241950,N'',N'',N'',N'',N'',N'',N'',N'',N'James Cann',N'Data.Import #',N'Data.Import #',N'',N'',N'',106001253),
(N'085da08a-b189-e811-a95f-00224800c719',CONVERT(DATETIME2, '2022-05-19 16:13:28.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:13:28.0000000', 121),1,3,N'',N'',N'0',N'717750000',N'',N'717750000',N'',N'',NULL,NULL,N'1',N'0',N'',N'',NULL,N'',N'',N'0',N'0',N'0',N'0',N'0',N'1',N'',N'0',N'1',N'',N'',N'47b5e190-c26b-e811-a96f-002248003cb8',N'businessunit',N'',N'',N'',N'',N'e9947e6f-6ae0-ea11-a81b-000d3a86b5bd',N'systemuser',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'',N'',N'',N'',N'',N'',N'40fc055a-5a78-e811-a95b-00224800c3e8',N'systemuser',N'085da08a-b189-e811-a95f-00224800c719',N'',N'085da08a-b189-e811-a95f-00224800c719',CONVERT(DATETIME2, '2005-10-10 10:59:00.0000000', 121),N'',CONVERT(DATETIME2, '2020-09-24 08:21:40.0000000', 121),N'',CONVERT(DATETIME2, '2018-07-17 11:07:16.0000000', 121),N'',N'{"scheduledstartformatted":"10/10/2005 10:59","scheduledendformatted":"10/10/2005 10:59"}',N'',N'',N'',N'085da08a-b189-e811-a95f-00224800c719',N'',N'',N'',CONVERT(DATETIME2, '2005-10-10 10:59:00.0000000', 121),N'',N'',N'',N'',N'',N'',N'',N'',N'',0,N'',N'',N'',CONVERT(DATETIME2, '2005-10-10 11:59:00.0000000', 121),N'',N'',N'',N'',N'Data.Import #',N'',N'',N'montaguequity',N'',N'',CONVERT(DATETIME2, '2005-10-10 10:59:00.0000000', 121),N'',N'',N'',N'',N'',0,N'',N'',N'',4,CONVERT(DATETIME2, '2020-09-07 15:34:19.0000000', 121),N'',N'',N'appointment',N'',N'',N'',N'',N'CMM met May 2004',N'Data.Import #',0,N'James Cann',N'',N'',N'',N'',N'',N'',0,244673,N'',N'',N'',N'',N'',N'',N'',N'',N'James Cann',N'Data.Import #',N'Data.Import #',N'',N'',N'',106001258)
SELECT * FROM appointment
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply