SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Logic creation


Logic creation

Author
Message
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5390 Visits: 2767
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/
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5390 Visits: 2767
Please help me on this...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6083 Visits: 5283
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/
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5390 Visits: 2767
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/
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73257 Visits: 40960
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5390 Visits: 2767
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/
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73257 Visits: 40960
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6083 Visits: 5283
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search