April 17, 2013 at 12:51 am
Hi all,
I have to write a script for a scenario but don't know how to implement it...
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)
Above is the table structure.
Let me explain the scenario:
I have to generate a STN number which should be generate in this manner: StoreCode(4digit) + series of number '000001' e.g 0001000001
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.
Hope this scenario is clear to you!!
Please tell me how to accomplish this..
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 17, 2013 at 5:09 am
Please help me on this...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 17, 2013 at 5:13 am
kapil_kk (4/17/2013)
Please help me on this...
The scenario that you have mentioned is not very clear.
It would be good if you can explain your issue with some DDL and sample data along with the expected result
That will help people understand your issues better
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 17, 2013 at 5:17 am
Kingston Dhasian (4/17/2013)
kapil_kk (4/17/2013)
Please help me on this...The scenario that you have mentioned is not very clear.
It would be good if you can explain your issue with some DDL and sample data along with the expected result
That will help people understand your issues better
I have mentioned DDL and also the scenarios with examples.. what I am not getting is how to start implementing this thing
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 17, 2013 at 5:24 am
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
April 17, 2013 at 5:28 am
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.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 17, 2013 at 5:49 am
you copied and pasted the exact same description, which apparently i misunderstood the first time.
copying the same thing and pasting it again doesn't help at all.
As I read it, the TWO elements involved are [From_StoreCode] and the identity field of the table [STNID]
am i wrong?
NOTHING you stated makes it obvious that the [AWBNo] has any impact on the number generation.
so:
what specific elements make up the STN number you want?
Lowell
April 17, 2013 at 5:54 am
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.
Sometimes, it is difficult to understand the problem when you describe it in words
It is helpful if some sample data is provided along with the desired results
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply