Hi
What about something like this.
declare @inc int = 0
create table #BATCH (
Number int,
BATCH varchar(2)
)
while (@inc < 36000)
begin
insert into #BATCH
select @inc,
case
WHEN @inc BETWEEN 10000 AND 10999 THEN 'A'
WHEN @inc BETWEEN 11000 AND 11999 THEN 'B'
WHEN @inc BETWEEN 12000 AND 12999 THEN 'C'
WHEN @inc BETWEEN 13000 AND 13999 THEN 'D'
WHEN @inc BETWEEN 14000 AND 14999 THEN 'E'
WHEN @inc BETWEEN 15000 AND 15999 THEN 'F'
WHEN @inc BETWEEN 16000 AND 16999 THEN 'G'
WHEN @inc BETWEEN 17000 AND 17999 THEN 'H'
WHEN @inc BETWEEN 18000 AND 18999 THEN 'I'
WHEN @inc BETWEEN 19000 AND 19999 THEN 'J'
WHEN @inc BETWEEN 20000 AND 20999 THEN 'K'
WHEN @inc BETWEEN 21000 AND 21999 THEN 'L'
WHEN @inc BETWEEN 22000 AND 22999 THEN 'M'
WHEN @inc BETWEEN 23000 AND 23999 THEN 'N'
WHEN @inc BETWEEN 24000 AND 24999 THEN 'O'
WHEN @inc BETWEEN 25000 AND 25999 THEN 'P'
WHEN @inc BETWEEN 26000 AND 26999 THEN 'Q'
WHEN @inc BETWEEN 27000 AND 27999 THEN 'R'
WHEN @inc BETWEEN 28000 AND 28999 THEN 'S'
WHEN @inc BETWEEN 29000 AND 29999 THEN 'T'
WHEN @inc BETWEEN 30000 AND 30999 THEN 'U'
WHEN @inc BETWEEN 31000 AND 31999 THEN 'V'
WHEN @inc BETWEEN 32000 AND 32999 THEN 'W'
WHEN @inc BETWEEN 33000 AND 33999 THEN 'X'
WHEN @inc BETWEEN 34000 AND 34999 THEN 'Y'
WHEN @inc BETWEEN 35000 AND 35999 THEN 'Z'
WHEN @inc <= 9999 THEN '' end
set @inc = @inc +1;
end
Create index idx_Number on #BATCH (Number)include (BATCH)
INSERT INTO #TMP1A
(ACCTCORP,
HOUSE,
CUST,
BADATE,
AMOUNT,
ADJRSN,
BATCH,
CTLGRP,
POSTFLAG)
SELECT
ACCTCORP,
HOUSE,
CUST,
BADATE,
AMOUNT,
ADJRSN,
CASE WHEN b.BATCH is not null then b.BATCH + RIGHT(a.BATCH,3)
WHEN b.BATCH)<= 9999 THEN a.BATCH
ELSE '' END AS 'DDP_BATCH_#',
BATCH AS [ORIG_BATCH_#],
--BATCH,
CTLGRP,
CASE WHEN POSTFLAG = ' ' THEN 'No'
ELSE 'Yes' END AS POSTFLAG
from <Table> a
left JOIN #BATCH b on a.BATCH =b.BATCH
Please feel free to ask for more detail in the explanation.
--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za