June 25, 2014 at 11:15 am
Hi,
I need help deciding the best way to accomplish this.
I need to have a script where it ask the user for a value, the script will search for all records that match the value. Then it will display the numbers of records found and ask the user to enter a different value. The rest of the script will use this new value and increment by 1 n times as the number of records found.
I started the script where it will ask for "HANDLE" and display the number of records found with that "HANDLE"
declare @HANDLE as varchar(30)
declare @COUNT as varchar(10)
declare @STARTINV as varchar(20)
set @HANDLE = ?C --This is the parameter to search for records with this value
set @STARTINV = ?C --User will input the starting invoice number
SELECT COUNT as OrderCount FROM SHIPHIST
where HANDLE = @HANDLE
I just can't figure out how to proceed to use the entered invoice # and increment by 1 until it reach the number of records found.
This will be the end results:
Count=5 --results from query
STARTINV=00010 --Value entered by user
Handle,Inv_Num
AAABBB,00010
AAABBB,00011
AAABBB,00012
AAABBB,00013
AAABBB,00014
Any help will be greatly appreciated.
June 25, 2014 at 11:24 am
Please read the article in my signature about posting these types of questions. After you have supplied the table definitions and sample data please provide the code that you currently have and the desired output.
June 25, 2014 at 2:01 pm
Please see attached file for testing purposes.
For results, I would like to prompt the user to enter the "HANDLE" and the "Start Invoice #", the script will
search for and COUNT all the records with the HANDLE submitted and insert the "Start Invoice #" in the INV_NUM field and
increment by 1 for all the other records until it reached the total record COUNT.
from the example submitted. if the user enter HANDLE "handle25" and invoice # "340", the script should be able to count
5 records, insert "340" in the first record and increment by 1 five times having the last record at 344
I think I have provide enough information, If I am missing something, please let me know.
Thank you
RITS
June 25, 2014 at 2:09 pm
reliableitservice (6/25/2014)
Please see attached file for testing purposes.For results, I would like to prompt the user to enter the "HANDLE" and the "Start Invoice #", the script will
search for and COUNT all the records with the HANDLE submitted and insert the "Start Invoice #" in the INV_NUM field and
increment by 1 for all the other records until it reached the total record COUNT.
from the example submitted. if the user enter HANDLE "handle25" and invoice # "340", the script should be able to count
5 records, insert "340" in the first record and increment by 1 five times having the last record at 344
I think I have provide enough information, If I am missing something, please let me know.
Thank you
RITS
So you have 2 parameters Handle and StartInvoice. Not sure what you mean exactly by insert "340" in the first record. Are you trying to write a update statement or create new rows in a table? Either way, you can easily use ROW_NUMBER for this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 25, 2014 at 2:10 pm
I think I just found out how to insert the code
--===== If the test table already exists, drop it
CREATE DATABASE TEST_DB
--=====Create table
use TEST_DB
CREATE TABLE SHIPHIST
(HANDLE varchar(20),INV_NUM varchar(20));
--=====Create values
INSERT INTO SHIPHIST
(HANDLE,INV_NUM)
VALUES ('handle23',''),
('handle24',''),
('handle25',''),
('handle25',''),
('handle25',''),
('handle25',''),
('handle25',''),
('handle26',''),
('handle26',''),
('handle26',''),
('handle27',''),
('handle27',''),
('handle28',''),
('handle28',''),
('handle28',''),
('handle28',''),
('handle28',''),
('handle28',''),
('handle29',''),
('handle29',''),
('handle29',''),
('handle29',''),
('handle30',''),
('handle30','');
June 25, 2014 at 2:17 pm
Hi Sean,
Thank you for your response.
The ROW_NUMBER can't be used. The "340" is a value that will change every time this script is used, furthermore this will be entered by the user.
That number is actually the invoice number.
We have a ship history table that we need to update with an invoice number. The HANDLE identify the group of similar orders or records and
the invoice value entered by the user will populate the INV_NUM field. THe invoice number will increase by 1 n times as there are records for the
HANDLE.
Let me know if I don't make any sense.
Thank you
RITS
June 25, 2014 at 2:32 pm
reliableitservice (6/25/2014)
Hi Sean,Thank you for your response.
The ROW_NUMBER can't be used. The "340" is a value that will change every time this script is used, furthermore this will be entered by the user.
That number is actually the invoice number.
We have a ship history table that we need to update with an invoice number. The HANDLE identify the group of similar orders or records and
the invoice value entered by the user will populate the INV_NUM field. THe invoice number will increase by 1 n times as there are records for the
HANDLE.
Let me know if I don't make any sense.
Thank you
RITS
Of course you can use ROW_NUMBER. Why do you think you can't? It returns a sequential number right? All you have to do is apply a little math and it works perfectly for this.
declare @Handle varchar(20)
declare @StartInvoice int
set @Handle = 'handle25'
set @StartInvoice = 340
select *
from SHIPHIST
where HANDLE = @Handle
Update ShipHistUpdate
set INV_NUM = RowNum
from
(
select INV_NUM, ROW_NUMBER() over (order by (select null)) + @StartInvoice - 1 as RowNum
from SHIPHIST h
where HANDLE = @Handle
) ShipHistUpdate
select *
from SHIPHIST
where HANDLE = @Handle
Or if you want to turn this into a procedure it is pretty simple.
create procedure UpdateShipHist
(
@Handle varchar(20),
@StartInvoice int
) as
Update ShipHistUpdate
set INV_NUM = RowNum
from
(
select INV_NUM, ROW_NUMBER() over (order by (select null)) + @StartInvoice - 1 as RowNum
from SHIPHIST h
where HANDLE = @Handle
) ShipHistUpdate
One thing I would HIGHLY recommend is to change your datatype for INV_NUM to be (big)int instead of varchar.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 25, 2014 at 3:36 pm
I am sorry, I misunderstood the use of Row_Number.
So you will use the row-number to count the number of records found, right?
I just don't see how the invoice number "340" will increase by 5 for all the records found for handle25?
handle25,340
handle25,341
handle25,342
handle25,343
handle25,344
I was thinking more of something like this:
declare @HANDLE as varchar(30)
declare @COUNT as varchar(10)
declare @STARTINV as varchar(20)
set @HANDLE = ?C --this wil prompt the user for the HANDLE
set @STARTINV = ?C --this wil prompt the user for the STARTINV
SELECT COUNT as OrderCount FROM SHIPHIST where HANDLE = @HANDLE
UPDATE SHIPHIST
WHILE (INV_NUM <= @STARTINV + @COUNT)
BEGIN
SET INV_NUM = @STARTINV
SET @STARTINV = @STARTINV + 1
END
Go
where HANDLE = @HANDLE
I was also thinking of using the COUNT to alert or notify the user the number of records that were altered.
June 25, 2014 at 3:40 pm
Never mind, I see your code working!!!!!
😀
June 25, 2014 at 5:02 pm
I want to thank you for your help!!
I already apply this to the LIVE environment and is working like a charm!!!!!
THANK YOU SO MUCH!!!!
RITS
June 26, 2014 at 7:10 am
reliableitservice (6/25/2014)
I want to thank you for your help!!I already apply this to the LIVE environment and is working like a charm!!!!!
THANK YOU SO MUCH!!!!
RITS
Glad you got it working. More importantly, do you understand it?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2014 at 7:00 pm
reliableitservice (6/25/2014)
I want to thank you for your help!!I already apply this to the LIVE environment and is working like a charm!!!!!
THANK YOU SO MUCH!!!!
RITS
How are you preventing duplicate invoice numbers across multiple handles?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply