Hi
If value is 21000030 it should return 4 not 5 . In first record 21000030 exists 2 times
21000032 then it should return 3 not 4.
USE [Live]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OCRD](
[CardCode] [nvarchar](15) NOT NULL,
[Emp_1] [nvarchar](10) NULL,
[Emp_2] [nvarchar](10) NULL,
[Emp_3] [nvarchar](10) NULL,
CONSTRAINT [OCRD_PRIMARY] PRIMARY KEY CLUSTERED
(
[CardCode] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[OCRD] ([CardCode],[Emp_1], [emp_2], [Emp_3]) Values (N'C01',N'21000030', N'21000030', NULL)
INSERT [dbo].[OCRD] ([CardCode],[Emp_1], [emp_2], [Emp_3]) Values (N'C02',N'21000032', N'21000030', NULL)
INSERT [dbo].[OCRD] ([CardCode],[Emp_1], [emp_2], [Emp_3]) Values (N'C03', N'21000038', N'21000032', NULL)
INSERT [dbo].[OCRD] ([CardCode],[Emp_1], [emp_2], [Emp_3]) Values (N'C04', N'21000040', N'21000033', N'21000030')
INSERT [dbo].[OCRD] ([CardCode],[Emp_1], [emp_2], [Emp_3]) Values (N'C05', N'21000030', N'21000032', N'21000032')
Thanks
