|
TheMatrixDuck
Starting Member
3 Posts |
 Posted - 2007-12-04 : 10:29:40
|
| I am having problems updating view containing a crosstab join, which makes me believe that the view code is wrong.A little description and then the current code.I have created a view to contain all possible marketing efforts for a contact list. The view is a derived table that contains columns from the contacts table, marketing types table and marketing detail tablethe marketing detail table is slaved to the marketing type (ie: type.name='newsletter', detail.name='2007' The ID columns are the primary keys. The view also includes a marketing history table to show what marketing efforts have been completed. In operation, the user would select a group of records from the view to work with. upon completion, each contact is inserted into the history table and the exportdate column is set to the current date.My view shows the correct number of records (ie, one marketing type/detail for each contact (currently there are 8 records per contact for the various marketing type/detail combinations).The problem is when the user selects a contact, completes the action and the exportdate is assigned to that inserted history record, every record that belongs to the contact has its exportdate assigned. And looking at the history records. I see that the marketingtypes and details are not matching up correctly... each distinct record that is joined to the marketing type/detail is fine, but when the history table is joined in, the typeID's and detail ID's are mismatched... hence the type, detail and exportdate from the history table is joined to EVERY record for that contact.whew!!!Tables: MarketingTypes, MarketingDetail, Leads_contacts, marketingHistory.HERE IS THE VIEWS SQL CODE:USE [MPCS]GO/****** Object: View [dbo].[MarketingRecordsView] Script Date: 12/03/2007 15:35:42 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER VIEW [dbo].[MarketingRecordsView]ASSELECT TOP (100) PERCENT CP.ID AS ViewID, CP.Company_Name, CP.Name, CP.MarkTypeExternalID, CP.MrktPK, CP.MarkDetailName, dbo.MarketingTypes.ID AS MktTypesID, dbo.MarketingTypes.MarkTypeName AS MktTypesName, dbo.MarketingHistory.ID AS MktProcessedID, dbo.MarketingHistory.ContactID AS MktProcContactID, dbo.MarketingHistory.MarkTypeID AS MktProcTypeID, dbo.MarketingHistory.MarkTypeName AS MktProcMarkTypeName, dbo.MarketingHistory.MarkDetailID AS MktProcDetailID, dbo.MarketingHistory.MarkDetailName AS MktProcDetailName, dbo.MarketingHistory.DeliveryMethod, dbo.MarketingHistory.ExportDate, CP.Street_Number, CP.Street_Name, CP.City, CP.Zip, CP.Contact_Type, CP.Phone, CP.Cell, CP.Fax, CP.Title, CP.Email, dbo.MarketingHistory.DeliveredBy, dbo.MarketingHistory.DeliveryDate FROM (SELECT dbo.Leads_Contacts.ID, dbo.Leads_Contacts.Company_Name, dbo.Leads_Contacts.Name, dbo.Leads_Contacts.Street_Number, dbo.Leads_Contacts.Street_Name, dbo.Leads_Contacts.City, dbo.Leads_Contacts.Zip, dbo.Leads_Contacts.Contact_Type, dbo.Leads_Contacts.Phone, dbo.Leads_Contacts.Cell, dbo.Leads_Contacts.Fax, dbo.Leads_Contacts.Title, dbo.Leads_Contacts.Email, dbo.MarketingDetail.MarkTypeExternalID, dbo.MarketingDetail.ID AS MrktPK, dbo.MarketingDetail.MarkDetailName FROM dbo.Leads_Contacts CROSS JOIN dbo.MarketingDetail) AS CP right OUTER JOIN dbo.MarketingTypes ON CP.MarkTypeExternalID = dbo.MarketingTypes.ID left OUTER JOIN dbo.MarketingHistory ON CP.ID = dbo.MarketingHistory.ContactID ORDER BY CP.Company_Name, cp.name |
|
Help with crosstab join