Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Logic creation Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 12:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:09 AM
Points: 1,916, Visits: 2,337
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/
Post #1443115
Posted Wednesday, April 17, 2013 5:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:09 AM
Points: 1,916, Visits: 2,337
Please help me on this...



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1443188
Posted Wednesday, April 17, 2013 5:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 2,661, Visits: 4,734
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



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1443191
Posted Wednesday, April 17, 2013 5:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:09 AM
Points: 1,916, Visits: 2,337
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/
Post #1443192
Posted Wednesday, April 17, 2013 5:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:34 PM
Points: 12,903, Visits: 32,144
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1443197
Posted Wednesday, April 17, 2013 5:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:09 AM
Points: 1,916, Visits: 2,337
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/
Post #1443200
Posted Wednesday, April 17, 2013 5:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:34 PM
Points: 12,903, Visits: 32,144
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1443204
Posted Wednesday, April 17, 2013 5:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 2,661, Visits: 4,734
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



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1443206
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse