SQLTeam.com | Weblogs | Forums

Find the result by comparing two table

tsql

#1

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Activitytable](
[ID] varchar NOT NULL,
[Activity] char NOT NULL,
[ActivityDateTime] varchar NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[idtable] Script Date: 25-12-2016 09:54:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[idtable](
[ID] varchar NOT NULL,
[EDateTime] [datetime] NULL,
[PDatetime] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 27 2013 4:55PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 27 2013 4:55PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 27 2013 4:55PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 11:34PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 11:34PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 11:34PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'E', N'Jun 7 2013 10:04AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'P', N'Jun 7 2013 10:09AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 25 2013 11:12PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 25 2013 11:12PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 25 2013 11:12PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 12:18AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 12:18AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 12:18AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 12:44AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 12:44AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 12:44AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 11:35AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 11:35AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 11:35AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'Mar 12 2013 7:49AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'Mar 12 2013 7:49AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'Mar 12 2013 7:49AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 12:18AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 12:18AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 5 2013 7:48PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 5 2013 7:48PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 31 2013 7:07AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 11:38PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 11:38PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 7:46PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 7:46PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 8:43AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 8:43AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 12:18AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 12:18AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 27 2013 4:07PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 27 2013 4:07PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 7:33PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 7:33PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 12:33AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 12:33AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 25 2013 1:13PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 25 2013 1:13PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 9:23PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 9:23PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'Mar 12 2013 7:49AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'Mar 12 2013 7:49AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 27 2013 4:07PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 27 2013 4:07PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 11:35AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 11:35AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 5 2013 11:01PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 5 2013 11:01PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 5 2013 11:01PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 27 2013 4:07PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 27 2013 4:07PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 27 2013 4:07PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 4 2013 10:00PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 4 2013 10:00PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 4 2013 10:00PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 12:17AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 12:17AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 12:17AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 12:05AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 12:05AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 12:05AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 7:33PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 7:33PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 7:33PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 12:33AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 12:33AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 12:33AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 10:18PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 10:18PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 10:18PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'E', N'May 28 2013 9:16AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'P', N'May 28 2013 9:48AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 5:47PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 5:47PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 5:47PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'E', N'May 28 2013 12:19PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'P', N'May 28 2013 12:21PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 8:07PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 8:07PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 8:07PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 27 2013 1:23PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 27 2013 1:23PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 10:11AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 10:11AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 7:33PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 7:33PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 12:52AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 12:52AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 5:47PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 5:47PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 25 2013 11:12PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 25 2013 11:12PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 8:07PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 8:07PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'Mar 11 2013 2:11PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'Mar 11 2013 2:11PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 4 2013 10:00PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 4 2013 10:00PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 4 2013 10:00PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 8:43AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 8:43AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 8:43AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 11:38PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 11:38PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 11:38PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 5 2013 11:01PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 5 2013 11:01PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 5 2013 11:01PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 9:46PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 9:46PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 9:46PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 25 2013 11:12PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 25 2013 11:12PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 25 2013 11:12PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 9:22PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 9:22PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 9:22PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 11:03PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 11:03PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 11:03PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'Mar 11 2013 2:11PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'Mar 11 2013 2:11PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'Mar 11 2013 2:11PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 11:34PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 11:34PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 12:44AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 12:44AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'E', N'May 28 2013 11:45AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'P', N'May 28 2013 11:47AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'E', N'May 28 2013 1:05PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'P', N'May 28 2013 1:08PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 4 2013 10:00PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 4 2013 10:00PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 12:05AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 12:05AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 27 2013 4:55PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 27 2013 4:55PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 9:22PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 9:22PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 5 2013 11:01PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 5 2013 11:01PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 5 2013 11:01PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 5 2013 9:09PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 5 2013 9:09PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 5 2013 9:09PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 27 2013 1:23PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 27 2013 1:23PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 27 2013 1:23PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 10:11AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 10:11AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 10:11AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 7:46PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 7:46PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 7:46PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 25 2013 9:49PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 25 2013 9:49PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 25 2013 9:49PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 26 2013 12:52AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 26 2013 12:52AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 26 2013 12:52AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'E', N'Jun 7 2013 10:04AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'P', N'Jun 7 2013 10:09AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 25 2013 11:12PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 25 2013 11:12PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 25 2013 11:12PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'E', N'Jun 7 2013 10:04AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'P', N'Jun 7 2013 10:09AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002462', N'M', N'May 25 2013 1:13PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002406', N'M', N'May 25 2013 1:13PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002440', N'M', N'May 25 2013 1:13PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 9:46PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 9:46PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 5 2013 9:09PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 5 2013 9:09PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 31 2013 7:07AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 12:02AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 12:02AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 10:18PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 10:18PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 12:17AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 12:17AM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 27 2013 7:41PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 27 2013 7:41PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 7:33PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 7:33PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 25 2013 9:49PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 25 2013 9:49PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 5 2013 11:01PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 5 2013 11:01PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 26 2013 11:03PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 26 2013 11:03PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002417', N'M', N'May 27 2013 4:07PM')
GO
INSERT [dbo].[Activitytable] ([ID], [Activity], [ActivityDateTime]) VALUES (N'22140104613002428', N'M', N'May 27 2013 4:07PM')
GO
INSERT [dbo].[idtable] ([ID], [EDateTime], [PDatetime]) VALUES (N'22140104613002406', CAST(0x0000A1CC0098D5B8 AS DateTime), CAST(0x0000A1CC00A1AADA AS DateTime))
GO
INSERT [dbo].[idtable] ([ID], [EDateTime], [PDatetime]) VALUES (N'22140104613002417', CAST(0x0000A1CC00D7C8B9 AS DateTime), CAST(0x0000A1CC00D885B9 AS DateTime))
GO
INSERT [dbo].[idtable] ([ID], [EDateTime], [PDatetime]) VALUES (N'22140104613002428', CAST(0x0000A1CC00C1B16B AS DateTime), CAST(0x0000A1CC00C259A8 AS DateTime))
GO
INSERT [dbo].[idtable] ([ID], [EDateTime], [PDatetime]) VALUES (N'22140104613002440', CAST(0x0000A1CC00CB07F8 AS DateTime), CAST(0x0000A1CC00CB8D6B AS DateTime))
GO
INSERT [dbo].[idtable] ([ID], [EDateTime], [PDatetime]) VALUES (N'22140104613002462', CAST(0x0000A1D600A5FD5C AS DateTime), CAST(0x0000A1D600A76F87 AS DateTime))
GO
INSERT [dbo].[idtable] ([ID], [EDateTime], [PDatetime]) VALUES (N'22140104613002473', CAST(0x0000A1CC00BB2363 AS DateTime), CAST(0x0000A1CC00BBD110 AS DateTime))
GO
INSERT [dbo].[idtable] ([ID], [EDateTime], [PDatetime]) VALUES (N'22140104613002484', CAST(0x0000A1CC010078A9 AS DateTime), CAST(0x0000A1CC0109EE3F AS DateTime))
GO
INSERT [dbo].[idtable] ([ID], [EDateTime], [PDatetime]) VALUES (N'22140104613002495', CAST(0x0000A1CC00BC618D AS DateTime), CAST(0x0000A1CC00BD38EB AS DateTime))
GO
INSERT [dbo].[idtable] ([ID], [EDateTime], [PDatetime]) VALUES (N'22140104613002506', CAST(0x0000A1A200C0FDDE AS DateTime), CAST(0x0000A1A200C16BAD AS DateTime))
GO
INSERT [dbo].[idtable] ([ID], [EDateTime], [PDatetime]) VALUES (N'22140104613002517', CAST(0x0000A1CC00C4778F AS DateTime), CAST(0x0000A1CC00C8368E AS DateTime))
GO

want to getting result like this
Activity Count
M 10 (1st Query)
M+E 9 (by comparing idtable with id & edatetime with the result came out from 1st query) (2nd query)
M+E+P 8 (by comparing idtable with id & pdatetime with the result came out from 2nd query)