Running Number Listing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Running Number Listing

Author

Topic

hayashiryo
Starting Member

46 Posts

Posted - 2011-10-26 : 23:51:36

Hi all,

I need to generate a report that displays a sequence of running numbers.

T_Invoice
1.ID
2.InvoiceNumber
3.Date
4.CustomerName


Sample Data
ID	InvoiceNumber	Date    	CustomerName
1	1001		15-Oct-11	CompanyA
2	1004		26-Oct-11	CompanyB
3	1006		13-Oct-11	CompanyC
4	1002		22-Oct-11	CompanyB

I need to generate a report as follows

Search Criteria:
a.Start Invoice Number
b.End Invoice Number

E.g. user key in 1001 to 1010
Note: There is no record for 1003,1005 and 1007 to 1010

Generate report should look like this


Inv#	Date    	Name
1001	15-Oct-11	CompanyA
1002	22-Oct-11	CompanyB
1003		
1004	26-Oct-11	CompanyB
1005
1006	13-Oct-11	CompanyC
1007
1008
1009
1010

For those invoices that have not been keyed into the system, the report should just show the invoice number and the other fields need to be blank. (e.g. 1003, 1005, 1007-1010)

I think I should use view to generate this report? Or a sql statement?

Thanks in advance.

Hayashi Ryo

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-27 : 00:19:54

use a number table or this to generate the series of invoice number and then LEFT JOIN to your T_Invoice table.

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 02:53:22

here's one way of generating number table on the fly


DECLARE @Start int,@End int
SELECT @Start=1001,@End=1010

;With Numbers (N)
AS
(
SELECT @Start
UNION ALL
SELECT N+1
FROM Numbers
WHERE N + 1 <=@End
)

SELECT n.N,i.[Date],i.CustomerName AS [Name]
FROM Numbers n
LEFT JOIN Invoice i 
ON i.InvoiceNumber = n.N

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hayashiryo
Starting Member

46 Posts

Posted - 2011-10-27 : 02:55:29

Hi khtan, do you have a sample code to do the inner join?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 02:57:37

quote:
Originally posted by hayashiryo

Hi khtan, do you have a sample code to do the inner join?

i've already given you a suggestion. try it and see if it fits your requirements

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hayashiryo
Starting Member

46 Posts

Posted - 2011-10-28 : 22:02:45

Hi all. I got it working. Thanks for the great suggestions!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-29 : 00:58:31

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources