SQL Server query problem_问答_开发者_运维开发者技术经验分享

I have a problem regarding this query. I want to get the applicant latest position (please see below query). The result should be like this:

aId    Position               Startdate       Enddate     

154    Web Developer          2008-04-07      NULL
155    Analyst                2008-06-12      2009-06-12
156    Quality Controller     2001-08-01      2010-01-01
165    Programmer             1995-08-02      2010-01-01
166    Programmer             2001-01-14      2010-01-14
170    Web Developer          2010-03-17      NULL
168    Business Analyst       2010-05-10      NULL

but the query below was gave me the result like this

aId  Position                     Startdate                   Enddate     

154 Lead Software Developer 2007-04-07 00:00:00.000 2008-04-07 00:00:00.000
154 Web Developer   2008-04-07 00:00:00.000 NULL
155 Analyst 2008-06-12 00:00:00.000 2009-06-12 00:00:00.000
156 Quality Controller  2001-08-01 00:00:00.000 2010-01-01 00:00:00.000
165 Programmer  1995-08-02 00:00:00.000 2010-01-01 00:00:00.000
166 Programmer  2001-01-14 00:00:00.000 2010-01-14 00:00:00.000
170 Web Developer   2010-03-17 00:00:00.000 NULL
168 Business Analyst    2010-05-10 00:00:00.000 NULL
168 Analyst Programmer  2010-05-03 00:00:00.000 2010-05-10 00:00:00.000

The query must not repeat the data having the same aId, what are the missing with this the query below?

SELECT 
   aId
   , Position
   , StartDate
   ,  Enddate
FROM EmploymentDetails ed 
WHERE
   ((Enddate IS NULL) OR
    (Enddate = (SELECT MAX(Enddate) 
                FROM EmploymentDetails edin 
                WHERE edin.aId = ed.aId)))




   create table EmploymentDetails(
  Id                  bigint         not null identity constraint PK_EmploymentDetails primary key,
  aId                 bigint         not null,
  Startdate           datetime,
  Enddate             datetime,
  Position            varchar(30),
  PositionLevelId     bigint         not null,
  SpecializationId    bigint         not null,
  PositionId          bigint         not null,
  StartSalary         varchar(50),
  EndSalary           varchar(50),
  DescriptionofDuties nvarchar(1000),
  ReasonforLeaving    nvarchar(200),
  CompanyName         nvarchar(100),
  TypeofBusiness      varchar(50),
  Address1            varchar(25),
  Address2            varchar(25),
  City                varchar(25),
  Province            varchar(25),
  StateorRegion       varchar(25),
  CountryId           bigint,
  PostalCode          varchar(10)
)
go






   INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) VALUES (34, 154, CAST(0x0000962900000000 AS DateTime), CAST(0x0000979F00000000 AS DateTime), N'Sr. .Net Developer', 4, 1, 1, N'P12,000', N'P12,000', N'Design websites for company using Adobe dreamweaver, Flash and other web applications.', N'sample reason for leaving', N'Appsource', N'Information Technology', N'Unit 1401 Robinsons Equit', N'ADB Ave. Corner Poveda Ro', N'Pasig', N'NCR', N'NCR', 177, N'')
INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) VALUES (35, 154, CAST(0x0000990900000000 AS DateTime), CAST(0x00009A7700000000 AS DateTime), N'Lead Software Developer', 4, 2, 1, N'P12,000', N'P12,000', N'Design websites for company using Adobe dreamweaver, Flash and other web applications.', N'sample reason for leaving', N'Corebuilt Technologies', N'Information Technology', N'24/F 88 Corporate Center', N'Valero St.,', N'Makati', N'NCr', N'NCr', 177, N'')
INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) VALUES (36, 154, CAST(0x00009A7700000000 AS DateTime), NULL, N'Web Developer', 4, 1, 1, N'P15,000', N'P15,000', N'sample description', N'sample description', N'IDCSI', N'Information Technology', N'15/F Summit One Tower', N'530 Shaw Blvd.,', N'Mandaluyong', N'', N'NCR', 177, N'')
INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) VALUES (38, 155, CAST(0x00009AB900000000 AS DateTime), CAST(0x00009C2600000000 AS DateTime), N'Analyst', 4, 3, 1, N'P15,000', N'P15,000', N'sample description', N'sample description', N'IDCSI', N'Information Technology', N'15/F Summit One Tower', N'530 Shaw Blvd.,', N'Mandaluyong', NULL, NULL, 177, NULL)
INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) VALUES (40, 156, CAST(0x000090EE00000000 AS DateTime), CAST(0x00009CF100000000 AS DateTime), N'Quality Controller', 4, 2, 1, N'P15,000', N'P15,000', N'sample description', N'sample description', N'IDCSI', N'Information Technology', N'15/F Summit One Tower', N'530 Shaw Blvd.,', N'Mandaluyong', N'', N'', 177, N'')
INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) VALUES (41, 165, CAST(0x0000722E00000000 AS DateTime), CAST(0x0000882800000000 AS DateTime), N'Analyst', 4, 2, 1, N'P15,000', N'P15,000', N'sample description', N'sample description', N'Microsoft', N'Information Technology', N'15/F Summit One Tower', N'530 Shaw Blvd.,', N'Mandaluyong', NULL, NULL, 177, NULL)
INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) VALUES (42, 165, CAST(0x0000885F00000000 AS DateTime), CAST(0x00009CF100000000 AS DateTime), N'Programmer', 4, 3, 1, N'P15,000', N'P15,000', N'sample description', N'sample description', N'Microsoft', N'Information Technology', N'15/F Summit One Tower', N'530 Shaw Blvd.,', N'Mandaluyong', N'', N'', 177, N'')
INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) VALUES (44, 166, CAST(0x0000902700000000 AS DateTime), CAST(0x00009CFE00000000 AS DateTime), N'Programmer', 4, 1, 1, N'P15,000', N'P15,000', N'sample description', N'sample description', N'Microsoft', N'Information Technology', N'15/F Summit One Tower', N'530 Shaw Blvd.,', N'Mandaluyong', NULL, NULL, 177, NULL)
INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) VALUES (45, 168, CAST(0x0000887E00000000 AS DateTime), CAST(0x00009B0B00000000 AS DateTime), N'Analyst', 4, 2, 1, N'P15,000', N'P15,000', N'sample description', N'sample description', N'Microsoft', N'Information Technology', N'15/F Summit One Tower', N'530 Shaw Blvd.,', N'Mandaluyong', NULL, NULL, 177, NULL)
INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) VALUES (47, 170, CAST(0x00009D3C00000000 AS DateTime), NULL, N'Web Developer', 4, 2, 1, N'P15,000', N'P15,000', N'sample description', N'samp开发者_运维问答le description', N'Microsoft', N'Information Technology', N'15/F Summit One Tower', N'530 Shaw Blvd.,', N'Mandaluyong', NULL, NULL, 177, NULL)
INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) VALUES (48, 156, CAST(0x00009A7300000000 AS DateTime), CAST(0x00009BE500000000 AS DateTime), N'', 4, 2, 1, N'P34,343', N'P3,434', N'fgf', N'fgfggf', N'fgfg', N'Information Technology', N'fgfg', N'fgfg', N'fgfg', N'fgfgf', N'fgfg', 177, N'4545')
INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) VALUES (49, 156, CAST(0x00009BDF00000000 AS DateTime), CAST(0x00009C4700000000 AS DateTime), N'', 4, 1, 1, N'P343', N'P344,343', N'dfdf', N'dfdf', N'dfdfd', N'Information Technology', N'ff', N'', N'dffd', N'', N'', 177, N'')
INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) VALUES (51, 168, CAST(0x00009D7200000000 AS DateTime), NULL, N'Business Analyst', 4, 1, 1, N'P3,434', N'P33,333', N'3434', N'3434', N'dsd', N'Information Technology', N'sdsds', N'', N'sdsd', N'', N'', 177, N'')
INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) VALUES (52, 168, CAST(0x00009D6B00000000 AS DateTime), CAST(0x00009D7200000000 AS DateTime), N'Analyst Programmer', 4, 1, 1, N'P43,434', N'P434,343', N'4', N'344', N'3434', N'Information Technology', N'434', N'', N'3434', N'', N'', 177, N'')

Assuming SQL Server 2005+

WITH E AS
(
SELECT 
   aId
   , Position
   , StartDate
   ,  Enddate
   ,ROW_NUMBER() OVER (PARTITION BY aId ORDER BY 
             CASE WHEN Enddate IS NULL THEN 0 ELSE 1 END ASC, 
             (CAST(Enddate as datetime)) DESC) AS RN
FROM EmploymentDetails ed 
)
LatestJob AS (SELECT  aId, Position, StartDate,  Enddate FROM E WHERE RN=1), 
EarliestStart AS (SELECT ...

By the way why are you storing dates as varchar rather than date (SQL2008) or datetime? Edit I see this last issue is now fixed in the question.