question

Given this DDL: IF EXISTS (SELECT OBJECT_ID('myTemp')) DROP TABLE [myTemp] GO CREATE TABLE [myTemp]( [FileID] [int] IDENTITY(1,1) NOT NULL, [BusinessDate] [datetime] NULL, [FileName] [varchar](500) NULL, [RowsLoaded] [int] NULL, [LoadedDate] [datetime] NULL, [FullFileName] [varchar](500) NULL, [Status] [char](10) NULL ) ON [PRIMARY] GO SET NOCOUNT ON SET IDENTITY_INSERT [myTemp] ON INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (22792, CAST(N'2016-02-12 00:00:00.000' AS DateTime), N'SomeData_20160212.TXT', 118815, CAST(N'2016-02-16 22:32:48.150' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20160212.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (22713, CAST(N'2016-02-08 00:00:00.000' AS DateTime), N'SomeData_20160208.TXT', 117632, CAST(N'2016-02-11 22:33:23.483' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20160208.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (22669, CAST(N'2016-02-04 00:00:00.000' AS DateTime), N'SomeData_20160204.TXT', 117818, CAST(N'2016-02-09 22:32:54.617' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20160204.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (22647, CAST(N'2016-01-29 00:00:00.000' AS DateTime), N'SomeData_20160129.TXT', 117911, CAST(N'2016-02-08 17:30:15.307' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20160129.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (22488, CAST(N'2016-01-25 00:00:00.000' AS DateTime), N'SomeData_20160125.TXT', 118673, CAST(N'2016-01-28 22:33:22.350' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20160125.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (22436, CAST(N'2016-01-21 00:00:00.000' AS DateTime), N'SomeData_20160121.TXT', 118543, CAST(N'2016-01-26 22:33:06.650' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20160121.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (22368, CAST(N'2016-01-18 00:00:00.000' AS DateTime), N'SomeData_20160118.TXT', 130679, CAST(N'2016-01-21 22:33:01.657' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20160118.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (22323, CAST(N'2016-01-14 00:00:00.000' AS DateTime), N'SomeData_20160114.TXT', 129327, CAST(N'2016-01-19 22:32:37.873' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20160114.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (22256, CAST(N'2016-01-11 00:00:00.000' AS DateTime), N'SomeData_20160111.TXT', 128684, CAST(N'2016-01-14 22:32:32.900' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20160111.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (22211, CAST(N'2016-01-07 00:00:00.000' AS DateTime), N'SomeData_20160107.TXT', 128324, CAST(N'2016-01-12 22:32:38.473' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20160107.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (22145, CAST(N'2015-12-31 00:00:00.000' AS DateTime), N'SomeData_20151231.TXT', 128446, CAST(N'2016-01-07 22:33:29.233' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20151231.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (22011, CAST(N'2015-12-25 00:00:00.000' AS DateTime), N'SomeData_20151225.TXT', 128583, CAST(N'2015-12-30 22:34:12.660' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20151225.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (21936, CAST(N'2015-12-21 00:00:00.000' AS DateTime), N'SomeData_20151221.TXT', 128510, CAST(N'2015-12-24 22:33:46.360' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20151221.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (21914, CAST(N'2015-12-17 00:00:00.000' AS DateTime), N'SomeData_20151217.TXT', 128962, CAST(N'2015-12-23 22:34:15.170' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20151217.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (21821, CAST(N'2015-12-14 00:00:00.000' AS DateTime), N'SomeData_20151214.TXT', 146642, CAST(N'2015-12-17 22:32:51.077' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20151214.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (21772, CAST(N'2015-12-10 00:00:00.000' AS DateTime), N'SomeData_20151210.TXT', 145136, CAST(N'2015-12-15 22:33:23.417' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20151210.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (21727, CAST(N'2015-12-07 00:00:00.000' AS DateTime), N'SomeData_20151207.TXT', 145099, CAST(N'2015-12-11 22:34:11.160' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20151207.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (21652, CAST(N'2015-11-30 00:00:00.000' AS DateTime), N'SomeData_20151130.TXT', 146344, CAST(N'2015-12-08 22:33:28.347' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20151130.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (21563, CAST(N'2015-11-26 00:00:00.000' AS DateTime), N'SomeData_20151126.TXT', 147752, CAST(N'2015-12-02 22:32:52.007' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20151126.TXT', N'Completed ') INSERT [myTemp] ([FileID], [BusinessDate], [FileName], [RowsLoaded], [LoadedDate], [FullFileName], [Status]) VALUES (21454, CAST(N'2015-11-20 00:00:00.000' AS DateTime), N'SomeData_20151120.TXT', 148555, CAST(N'2015-11-26 22:35:10.883' AS DateTime), N'\\SERVER1\FILE\PATH\SomeData_20151120.TXT', N'Completed ') SET IDENTITY_INSERT [myTemp] OFF I need to return a result set that shows if RowsLoaded is less than RowsLoaded from the previous LoadedDate where FileName like 'SomeData%' and Status = 'Completed' This seemingly trivial problem is eluding me.