IF OBJECT_ID(N'[dbo].[ky_NETVAssociateNumericCode]', N'V') IS NOT NULl
BEGIN
    DROP VIEW [dbo].[ky_NETVAssociateNumericCode]
END
GO

CREATE VIEW [dbo].[ky_NETVAssociateNumericCode] WITH SCHEMABINDING
AS
/***********************************************************************************
 *
 * [ky_NETVNextFreeAssociateNumericCode]
 * 
 * Returns the next available numeric associate code, checking for gaps between codes
 *
 * Modification History
 * 2019-11-08   John Ginnane   Created
 *
 ***********************************************************************************/
SELECT [CAN].[CODE] AS [ID],
       CASE WHEN PATINDEX('%[^0-9]%', [CAN].[CODE]) > 0
                 THEN NULL
            ELSE RIGHT('000000' + CONVERT(VARCHAR(6), CONVERT(INT, [CAN].[CODE]) + 1), 6) END AS [NextID]
  FROM [dbo].[CaseAssociatesNames] AS [CAN]
GO

IF EXISTS (SELECT TOP 1 1
             FROM [sys].[indexes] AS [si]
            WHERE [si].[name] = 'IDX_AssociateNumericCode'
              AND [si].[object_id] = OBJECT_ID(N'[dbo].[ky_NETVAssociateNumericCode]'))
BEGIN
    DROP INDEX [IDX_AssociateNumericCode] ON [dbo].[ky_NETVAssociateNumericCode]
END
GO

CREATE UNIQUE CLUSTERED INDEX [IDX_AssociateNumericCode]
    ON [dbo].[ky_NETVAssociateNumericCode]([ID], [NextID])
GO

IF OBJECT_ID(N'ky_NETVNextFreeAssociateNumericCode',N'V') IS NOT NULL
BEGIN
    DROP VIEW [dbo].[ky_NETVNextFreeAssociateNumericCode]
END
GO

CREATE VIEW [dbo].[ky_NETVNextFreeAssociateNumericCode]
AS
/***********************************************************************************
 *
 * [ky_NETVNextFreeAssociateNumericCode]
 * 
 * Returns the next available numeric associate code, checking for gaps between codes
 *
 * Modification History
 * 2018-09-24   John Ginnane   Created
 * 2018-10-01   John Ginnane   Added ORDER BY
 * 2019-11-07   John Ginnane   Rewrote view to be much faster
 *
 ***********************************************************************************/
SELECT TOP 1 [ANC].[NextID] AS [NewID]
  FROM [dbo].[ky_NETVAssociateNumericCode] AS [ANC]
       LEFT OUTER JOIN [dbo].[CaseAssociatesNames] AS [CAN2]
                    ON [CAN2].[CODE] = [ANC].[NextID]
 WHERE [ANC].[ID] IS NOT NULL
   AND [ANC].[NextID] IS NOT NULL
   AND [CAN2].[CODE] IS NULL
       ORDER BY [ANC].[ID] ASC
GO