asked
How to combine 5 activity types into one master table
Hello, I have 5 types of activities: Call, Meeting, Message, Note, and Task. Each one of these activities have a header and detail views. There is also an Activity table that contains the activity type id, and activity type name. I am trying to write a master view that lists all records by activity type. The problem with the format of the output of my view is that it creates columns for each activity, so for call it lists all records and columns, followed by meeting records and columns, and so on, there are a lot of null columns and the table just seems to wide. I am wondering if there is a more efficient and readable method I can use, to combine all of these type of activities into one single table grouping each activity one below another, rather than one next to another. I think, based on what I have read, than a union can be use, but I have not been able to figure out how to write this query, since my query contains a few joins. Thank you for your kind help. Best regards, Al. Here is the sql of my view: SELECT ca.CompanyID , cca.ContactID , 'ActivityID' [PrimaryKeyName] , a.ActivityID [PrimaryKeyValue] , 'ContactID' [ForeignKeyName] , ContactID [ForeignKeyValue] , NULL [CompositeKeyName] , NULL [CompositeKeyValue] , a.UserID , a.ActivityID [ActivityID] , a.ActivityName , a.ActivityDate , c.[Recipient] [ActivityCallRecipient] , c.[Name] [ActivityCallName] , c.[CallDate] [ActivityCallDate] , c.[CallTime] [ActivityCallTime] , c.[Duration] [ActivityCallDuration] , c.[Description] [ActivityCallDescription] , c.[FollowUpDate] [ActivityCallFollowUpDate] , c.[FollowUpTime] [ActivityCallFollowUpTime] , c.[Tags] [ActivityCallTags] , c.[Creator] [ActivityCallCreator] --Meeting , m.Body [ActivityMeetingBody] , m.Category [ActivityMeetingCategory] , m.Creator [ActivityMeetingCreator] , m.FinishDate [ActivityMeetingFinishDate] , m.FinishTime [ActivityMeetingFinishTime] , m.Location [ActivityMeetingLocation] , m.MeetingID , m.Recipient [ActivityMeetingRecipient] , m.Recurrence [ActivityMeetingRecurrence] , m.StartDate [ActivityMeetingStartDate] , m.StartTime [ActivityMeetingStartTime] , m.Subject [ActivityMeetingSubject] --Message , msg.MessageID , msg.AttachmentName [ActivityMessageAttachmentName] , msg.Bcc [ActivityMessageBcc] , msg.Body [ActivityMessageBody] , msg.Cc [ActivityMessageCc] , msg.Creator [ActivityMessageCreator] , msg.Priority [ActivityMessagePriority] , msg.Recipient [ActivityMessageRecipient] , msg.Sender [ActivityMessageSender] , msg.Subject [ActivityMessageSubject] --Note , n.Creator [ActivityNoteCreator] , n.NoteID , n.Subject [ActivityNoteSubject] , n.Title [ActivityNoteTitle] --Task , t.Body [ActivityTaskBody] , t.Category [ActivityTaskCategory] , t.Complete [ActivityTaskComplete] , t.Creator [ActivityTaskCreator] , t.DueDate [ActivityTaskDueDate] , t.FollowUpDate [ActivityTaskFollowUpDate] , t.FollowUpTime [ActivityTaskFollowUpTime] , t.Priority [ActivityTaskPriority] , t.Recipient [ActivityTaskRecipient] , t.Recurrence [ActivityTaskRecurrence] , t.Reminder [ActivityTaskReminder] , t.StartDate [ActivityTaskStartDate] , t.Subject [ActivityTaskSubject] , t.TaskID FROM dbo.vw_tblActivity a LEFT JOIN dbo.vw_tblActivity_Call ac ON ac.ActivityID = a.ActivityID LEFT OUTER JOIN dbo.vw_tblCall c ON c.CallID = ac.CallID LEFT OUTER JOIN dbo.vw_tblActivity_Meeting am ON am.ActivityID = a.ActivityID LEFT OUTER JOIN dbo.vw_tblMeeting m ON m.MeetingID = am.MeetingID LEFT OUTER JOIN dbo.vw_tblActivity_Message amsg ON amsg.ActivityID = a.ActivityID LEFT OUTER JOIN dbo.vw_tblMessage msg ON msg.MessageID = amsg.MessageID LEFT OUTER JOIN dbo.vw_tblActivity_Note an ON an.ActivityID = a.ActivityID LEFT OUTER JOIN dbo.vw_tblNote n ON n.NoteID = an.NoteID LEFT OUTER JOIN dbo.vw_tblActivity_Task at ON at.ActivityID = a.ActivityID LEFT OUTER JOIN dbo.vw_tblTask t ON t.TaskID = at.TaskID LEFT OUTER JOIN dbo.vw_tblCompany_Activity ca ON ca.ActivityID = a.ActivityID LEFT OUTER JOIN dbo.vw_tblContact_Activity cca ON cca.ActivityID = a.ActivityID; Here is the output: CompanyID ContactID PrimaryKeyName PrimaryKeyValue ForeignKeyName ForeignKeyValue CompositeKeyName CompositeKeyValue UserID ActivityID ActivityName ActivityDate ActivityCallRecipient ActivityCallName ActivityCallDate ActivityCallTime ActivityCallDuration ActivityCallDescription ActivityCallFollowUpDate ActivityCallFollowUpTime ActivityCallTags ActivityCallCreator ActivityMeetingBody ActivityMeetingCategory ActivityMeetingCreator ActivityMeetingFinishDate ActivityMeetingFinishTime ActivityMeetingLocation MeetingID ActivityMeetingRecipient ActivityMeetingRecurrence ActivityMeetingStartDate ActivityMeetingStartTime ActivityMeetingSubject MessageID ActivityMessageAttachmentName ActivityMessageBcc ActivityMessageBody ActivityMessageCc ActivityMessageCreator ActivityMessagePriority ActivityMessageRecipient ActivityMessageSender ActivityMessageSubject ActivityNoteCreator NoteID ActivityNoteSubject ActivityNoteTitle ActivityTaskBody ActivityTaskCategory ActivityTaskComplete ActivityTaskCreator ActivityTaskDueDate ActivityTaskFollowUpDate ActivityTaskFollowUpTime ActivityTaskPriority ActivityTaskRecipient ActivityTaskRecurrence ActivityTaskReminder ActivityTaskStartDate ActivityTaskSubject TaskID ----------- ----------- -------------- --------------- -------------- --------------- ---------------- ----------------- ----------- ----------- ---------------------------------------------------------------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------- ---------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ ------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------- ------------------------- ------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------- ------------------------ ------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------- ---------------------------------------------------------------- ------------------- ------------------------ ------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- -------------------- --------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- 5 2 ActivityID 3 ContactID 2 NULL NULL 2 3 Call 2015-10-22 00:00:00.000 Thomas Rufflo Walter Johnson 2015-10-12 04:15:00.0000000 01:00:00.0000000 I called Thomas. Hurrah 2015-10-15 05:00:00.0000000 Tags, More Tags wjohnson 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 NULL NULL NULL NULL NULL 5 2 ActivityID 4 ContactID 2 NULL NULL 2 4 Meeting 2015-10-13 00:10:26.367 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Body NULL Walter Johnson 2015-10-15 16:00:00.0000000 Some Location 1 Thomas Rufflo NULL 2015-10-15 15:00:00.0000000 This is a meeting Request 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 5 2 ActivityID 5 ContactID 2 NULL NULL 2 5 Message 2015-10-13 09:03:43.107 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 NULL NULL Body NULL wjohnson NULL
lnavagomez@anchorbank.com
wjohnson@anchorbank.com Como Estas NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 5 2 ActivityID 6 ContactID 2 NULL NULL 2 6 Note 2015-10-13 09:04:22.097 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 wjohnson 1 Subject Note Title NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 5 2 ActivityID 7 ContactID 2 NULL NULL 2 7 Task 2015-10-13 09:11:19.473 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 NULL bb NULL 0 wjohnson 2015-10-30 2015-10-13 04:30:00.0000000 Medium
lnavagomez@anchorbank.com 1 0 2015-10-15 Hey 1
You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
Hi Yes you are right , union is a best bet for this. For union your number of total columns and their data type should be same:- Firstly you need to decide the column headers which you would get from following query:- For example activityid, activityname, starttime, endtime, body, recipient Secondly try to put column of same dataype and similar value under one column value and if you dont have relevant column put a null value (so that union can be done) like this :- select activityid, activityname, starttime, endtime, body, recipient (if you dont have any header put the hardcoded value) from some_tables union select activityid, 'Meeting',starttime, null, null, receipient from some_tables2 union select activityid, 'Meeting',null, endtime, body121, null from some_tables2 The key here is to build individual queries for all different activities by adding nulls or calculated columns so that each resultset has equal number of columns....then at the very end apply a union operator. Hope this helps.
Hi and thank yo for your comment and example. Will the union work if each individual query has their own combination of joins? Thank you, Al
Yes absolutely...you can use different combination of joins
@erlokeshsharma08 covered most of what you need. (+1) for his answer. I just want to add a few points I believe are important when working with UNION. 1. Set the data types of the columns in the first SELECT to be the highest precision and scale you need for that column. For example, if you have integers, 7-digit numbers with 4-digit decimals, and 10-digit numbers with 3-digit decimals, you will want to ensure the data type is decimal(14,4) to cover the max of every scenario (up to 10 digits to the left of the decimal and up to 4 digits to the right). I often use CONVERT for each column to explicitly ensure the data types are set appropriately. 2. UNION ALL will perform faster than UNION because it doesn't have to perform the extra step of removing duplicates. If you know you won't have duplicate rows, use UNION ALL.
20 People are following this question.
