Generating Sequenced Line Numbers For Each Invoice Number
May 8, 2008
Can you help me with SQL issue I€™m stuck on?
I wish to take source data that looks like this:
Invoic_num
Line_num
6658
0
6658
2
6658
8
7721
2
7721
3
And rebuild the line numbers like this:
Invoic_num
Line_num
6658
1
6658
2
6658
3
7721
1
7721
2
This seems completely impossible to me. So I was thinking that maybe a second procedure using update could be run against the table after the initial build.
I need a bit of generic advice about a project I am currently working on which is slightly out of my comfort zone. I€™m using SQL 2005 and VB.Net to develop an application that produces invoices for a number of different countries. The issue is that the data is arriving from an AS400 system so the tables I am working from are a little strange! This results in a fair degree of work tweaking the data into a format that is useful for my application.
To simplify the explanation each invoice in its basic form consists of the invoice data and the line items (so there is nothing new here). What is unusual is that the invoice data has an invoice number but then an additional invoice number specific to the country it is destined for. The initial invoice number arrives with the data from the AS400 but the secondary number is generated according to a number of business rules.
I have written an update query in a stored procedure which updates the invoice data with a number of values from a secondary table and a number of calculated values. There are several functions, one in particular which looks up the new invoice number from a secondary table based on specific criteria (country of destination being one of them) and I had intended to write an update query, called from within this function which then increments the invoice number but I have since found out that you cant do an update/delete from a function?
I can€™t add an update query to increment the value after the initial update query as this results in the stored procedure running through and adding all the invoice numbers (which are the same) and then the number being incremented at the end.
I assume I can€™t call an update query from within the initial update query so that the increment takes place after each loop of the initial update (if I can what would the syntax be?).
I have tried writing a trigger which completes the increment when the invoice data is updated. I haven€™t used triggers before but once again it appears as if the first update query is running through and completing all the updates and then the trigger fires incrementing the number. Or have I just got this wrong?
I guess what I need is a trigger which fires after each row of the table is updated which was how I assumed it should work!
Ironically I could write this in VB.Net with ease but for speed we need it within the database.
What is the best way to increment my invoice number?
I€™m sorry of this is general in nature but im really looking for some advice as to the best approach to deal with this issue.
I am developing a ASP.NET application using SQL Server as my database. I want to create a number that is unique.
The number will have 8 digits.
first 2 digits correspond to year. ex:04 or 03
the next 6 digits start with 000001 and it should get added for each new entry of data.
i am not able to generate number in the way i said. I am relatively new to SQL. so any suggestions as how to go about solving the problem???. Are there any samples/codes available for this.
A simple question! I would like to create a database that creates say serial numbers and saves them to a table. If poss I would like to create them on demend and not duplicate.....
I am using sybase aSE12.5. I have a table with only one column withdatatype char(2). when i query this table to select all the records, ishould get these records in the ascending order and they should be numbered, i.e, the o/p should look something like thiscolumn_name------ --------1 AB2 AC3 ADand so on.I cannot add an extra column and i need this to be done in a single query.--Message posted via http://www.sqlmonster.com
I want to generate a resultset that is just a series of numbers inascending order or perhaps a series of dates.. What I mean is, isthere a way to generate a temporary table of dates given an input of astart date and an end date.. This table would then contain an entryfor each date in ascending order from the start date to and includingthe end date..Or perhaps with numbers, given a start of 5 and and end of 7the resulting table would be567Would appreciate any help with this one.. ThanksChris
I need to build TSQL query to return the Last unit Cost from my table of movement of goods SL (on CTE) but the MAX(Datalc) must be Less or Equal to my HeaderInvoice.
This is my script:
With MaxDates as ( SELECT ref, MAX(epcpond)[Unitcostprice], MAX(datalc) MaxDate FROM sl
[code]....
the problem I have right now is that the Unitcostprice of my table of goods movements has a top date greather than the date of my bill.
Example:
invoice date : 29.01.2015 unitcost on invoice line = 13,599722 Maxdate (CTE) : 19.03.2015 unitCost from my table of movement of goods = 14,075
That ´s not correct because the MAxdates > invoice date and the unitCost of 14,075 is the cost on 19.03.2015 and not just before my invoice date.
What I am wanting to do in another column is keep track of the sequence number for each distinct invoice like:
SeqNo 1 2 3 1 2 1
I am working in a stored proc and i cant get past adding the numbers up at each line as a whole and not reseting when the next invoice number is present. Any help would be so greatly appreciated.
I have a report in which the user community would like line numbers associated with each row. Has anyone done this or have any ideas how to accomplish this?
Hi, I am having chart which has positive and negative numbers.I need dark line(gridline) at 0.00th location.So that it differentiates between positive and negative levels.So please help me out in solving this asap.
When a self-developed CLR stored procedure throws an Exception and the Exception is caught in the code and for example logged, there only are class and method names in the stack trace lines, but not the line numbers. Can the line numbers somehow be included in the stack trace?
I'm not familiar with .NET framework stack traces, but when I tried to find out more information I hit concepts like PDB files, are such things somehow needed to enable line numbers in stack traces?
Can anyone tell me if you can display line numbers in the query windowof SQL 2005 Management Studio and if so how do I go about doing it?Thanks a bunch. TFD
Hi all, I have a question about generating membership numbers on the fly when someone registers to my website.
Rather than using the auto increment field as a membership number, I would rather keep it as just as the ID for the record and I would like to have a seperate membership number that looks something similar to this...
SR357324J
This will then stay with them for the lifetime of their membership and be on their printed loyalty card.
My questions are... 1) Is there a 'good practice' for membership number format and generation?
2) If this was used as a unique field, is there a degradation in performance when looking up records due to it being alphanumeric.
I may be well off base here, however these are my thoughts so far and your opinion/help is greatly appreciated.
I need to know how to generating a sequence number, for example, from 300,000 to 900,000 without skipping any number due to failure. For example, if user 1 request a number then he/she will get 300000 in a transaction. User 2 will get 300001. How ever user 1's transaction fails, then the next request should get 300000. Is it possible to do this in SQL2K? If so, how do I create a table that and stored procedure that can do this.
Hi, I got stuck with a trivial issue. I have a table named T1 having column as Col1 (INT). I have another table named T2 having columns Col1(Int), COl2(Int). Following are the reacords in my table T1.
Col1 ---- 1 1 1 1 2 2 3 3 3 3 3 4 5 6 6 7
And I want to migrate the data from T1 to T2 where the Col1 data of T1 will get migrated to Col1 of T1 and after the migration is done the T2 should have the data like this.........
Hello folks, I have a table where the records are like followings.
ID Value --------------- 1 aa 1 aa 1 aa 1 bb 1 bb 1 bb 1 bb 1 cc 2 pp 2 dd 2 dd 3 qq 4 aa
I need to include one column "SeqId" which will be having value based on the 1st 2 columns (ID + Value). I am struglling with this since last few days. Can I request you guys to help me out in this reagard.
ID Value SeqId ----------------------------- 1 aa 1 1 aa 2 1 aa 3 1 bb 1 1 bb 2 1 bb 3 1 bb 4 1 cc 1 2 pp 1 2 dd 1 2 dd 2 3 qq 1 4 aa 1
hi, i am a newcomer and a freshman in asp.net. i am now writing a web-based system for SME as my final year project. i am going to use sql server and asp.net in C# to perform my final year project. as asp.net is new for me, i would have some simple problems to ask. 1. in the project, i would like the system can automatically generate the enquiry number for each new order input to the system. for example today is 05 July 2006, the enquiry number would like 2006211xxxx, where 2006 is year, 211 is the day count start from 1 Jan and xxxx is the random number/ ordered number. how can i implement this? i even don't know how to generate the ordered number. could anyone help me 2. if there is an unknown test sample in each order input. as the sample number for each order is different, how can i set a flexible table that can have different number of rows for user to input the test result. thanks Rgds, universe
I'm having a brain not functioning day - well who am I kidding - more like a year :rolleyes:
I need some help with some sequence numbering and cannot even get my head around the logic I want to use, let alone the actual code.
I have a dataset with 3 fields:
Area ID RefNo
This table contains a list of employee ID's by Area and each employee has a RefNo (counter) in each area.
The data comes from 2 different sources and is combined in this table. Some employees had no RefNo already assigned to them so I have entered their RefNo as 10000 in order to ensure they are sorted at the bottom of the list.
The ID's that have RefNo's have to keep the one they have. Therefore, I need to create RefNo's for the ones that currently have RefNo 10000.
These numbers I create have to follow on from the highest RefNo for the Area.
For example:
Area ID RefNo A Z 1 A Y 2 A X 3 A W 10000 A V 10000 B N 1 B O 10000 B P 10000
So, for Area A, ID's W and V would have to be assigned RefNo 4 and 5, and for Area B, ID's O and P would have to be assigned RefNo 2 and 3.
Other than right-clicking on each individual table in SSMS and generating a CREATE script, is there a simple way to generate CREATE TABLE scripts for tables within a given database?
Background: I have a bunch of tables in one database, and I would like to add tables to a second database that have the same names and basic structures of some of the tables from the first database.
I do not need to transfer any data from the tables, this is a seperate project that will use a similar data structure. I just want to generate the CREATE TABLE scripts for 30ish tables within the first database, and then I'll tweak the scripts as appropriate and run them against the new database.
I've got a string of numbers, say 123456 (the actually number is 12 digitslong). I need to calculate the sum of each individual number in the stringof numbers, so in the example of 123456 the sum would be 21 (1+2+3+4+5+6).Thanks
What I want to have is that every month will sum up the previous month and him self so in the end I will be able the create a graph wuth those numbers:
I have a large query that I am trying to debug in query analyzer.However, the errors I get have no line number or reference to wherethey are failing. How can I find out what line in the query is causinga particular error message to appear?ThanksJohn IveySouth Pike School District
When you get a server error message referring to a line number in ascript, is there an easy way to jump to the correct line? It's noteasy to count down in a big script or when there is a lot of whitespace.In WordPerfect we had a linenum macro that allowed you to enter theline number and it would jump to the line using the same countingmethod as the interpreter that generated the error message. Is theresomething similar with TSQL, or another way?Kerry
