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 NOT NULL, [Emp_1] nvarchar NULL, [Emp_2] nvarchar NULL, [Emp_3] nvarchar 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