Invoice

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Invoice

14 posts, Page 1 of 2

1 2 »»

Invoice

Rate Topic

Display Mode

Topic Options

Author

Message

3935

Posted Wednesday, June 27, 2012 6:58 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 22, 2013 12:25 PM
Points: 17, Visits: 84

Hey Guys,

How can i unique invoice using Query ?

Because invoice formate is kind of Weird.

Format : SSSSDDMMY
011127062

SSSS(sTORE NUMBER)
DD(DAY)
MM(MONTH)
Y(LAST DIGIT OF YEAR)

This is dummy Question but I Do need help. What will be the Syntax ?

Any idea guys

Post #1321841

Lowell

Posted Wednesday, June 27, 2012 7:28 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:56 PM
Points: 11,613, Visits: 27,669

based on your proposed format, [storenumber][ddmmy], you are assuming a store can only have one invoice a day? what if they want to have two?

3935 (6/27/2012)

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 #1321860

Sean Lange

Posted Wednesday, June 27, 2012 7:29 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:45 PM
Points: 8,567, Visits: 8,218

You need to provide a few more details before anybody can be much help here. You will need to concatenate some strings but I couldn't begin to provide the syntax. Don't know the table structure at all. Probably just need ddl for the table and we can figure it out. Take a look at the first link in my signature.

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1321862

3935

Posted Wednesday, June 27, 2012 7:45 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 22, 2013 12:25 PM
Points: 17, Visits: 84

Its going to be invoice by day , By Store . And for second invoice they will have correction column for that

Post #1321874

anthony.green

Posted Wednesday, June 27, 2012 7:57 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

This is one way

SELECT
	Store+
	RIGHT('0'+CAST(DATEPART(DAY,GETDATE()) AS varchar),2)+
	RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS varchar),2)+
	RIGHT(DATEPART(YEAR,GETDATE()),1)
FROM
	sometable

Now what happens when you get to year 2022? As you will get the same invoice number again.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1321880

3935

Posted Wednesday, June 27, 2012 8:08 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 22, 2013 12:25 PM
Points: 17, Visits: 84

Number Date CustomerID OnlyI Total_Invoice_Cost

1500 6/12/12 12:00 AM ALFKI I 104.25
1500 6/13/12 12:00 AM ANATR I 106.3
1500 6/15/12 12:00 AM ANTON I 200.3
1500 6/15/12 12:00 AM AROUT I 325.45
1500 6/14/12 12:00 AM BERGS I 125.26

"1500" and "I" its defaul value .

Post #1321895

3935

Posted Wednesday, June 27, 2012 8:23 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 22, 2013 12:25 PM
Points: 17, Visits: 84

Thank you for reply But I am creating this invoice for only past 2 years.

And if i have store Number 12 or 121 how can i maintain "SSSS"

In my fromat if store is 12 or 121 , then in invoice it should be 0012 or 0121 . How i maintain my store number atleast 4 digit ?

Thank you once again for reply

Post #1321912