kapil_kk (4/17/2013)

---

Lowell (4/17/2013)

---

well based on how i read your description, it sounds like a simple calcuated column could be used;

ALTER TABLE [dbo].[GV_STNDetails]ADD STN AS RIGHT('0000' + CONVERT(VARCHAR,[From_StoreCode]),4) +RIGHT('000000' + CONVERT(VARCHAR,[STNID]),6) PERSISTED

if you run this example in tempdb, is this giving you the results you are looking for?

/*

--Results

STNID CourierName AWBNo DispatchedDate STNNO From_StoreCode TO_StoreCode STN

----------- ------------------------------ ----------- ----------------------- -------------------- -------------- ------------ ----------

1 FedEx 14 2013-04-17 07:22:26.040 PO-14 5 6 0005000001

2 UPS 42 2013-04-17 07:22:26.040 PO-15 1 1 0001000002

*/

CREATE TABLE [dbo].[GV_STNDetails] (

[STNID] INT IDENTITY(1,1) NOT NULL,

[CourierName] VARCHAR(30) NULL,

[AWBNo] INT NULL,

[DispatchedDate] DATETIME NULL,

[STNNO] VARCHAR(20) NULL,

[From_StoreCode] INT NULL,

[TO_StoreCode] INT NULL,

STN AS RIGHT('0000' + CONVERT(VARCHAR,[From_StoreCode]),4) +RIGHT('000000' + CONVERT(VARCHAR,[STNID]),6) PERSISTED)

INSERT INTO GV_STNDetails(CourierName,AWBNo,DispatchedDate,STNNO,From_StoreCode,TO_StoreCode)

SELECT 'FedEx',14,getdate(),'PO-14',5,6 UNION ALL

SELECT 'UPS',42,getdate(),'PO-15',1,1

SELECT * FROM GV_STNDetails

Lowell, but am generating on certain different scenarios which I mentioned in my problem...

scenarios:

if a store is assigning a voucher to another store e.f store1 (0001) assign some voucher to another store(0002) then STN no would be 0001000001.

If a same store 0001 is assigning to another store 0003 then STN no be 0001000002

If a store 0002 is assigning to store 0004 then STN be 0002000001

There is AWB number which is entered by a user manually on the aplication screen. Suppose 0001 store is again assigning vocuher to store code 0002 then in this condition no new STN will be generated and in this case STN will be 0001000001. Once a AWB no gets entered for this then if again 0001 assigned some voucher to 0002 thn in this STN will be 0001000002.