June 27, 2012 at 6:58 am
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
June 27, 2012 at 7:28 am
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)
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
Lowell
June 27, 2012 at 7:29 am
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 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 27, 2012 at 7:45 am
Its going to be invoice by day , By Store . And for second invoice they will have correction column for that
June 27, 2012 at 7:57 am
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.
June 27, 2012 at 8:08 am
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 .
June 27, 2012 at 8:23 am
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
June 27, 2012 at 8:24 am
RIGHT('0000'+STORE,4)
Or you could look at the Stuff function.
June 27, 2012 at 8:44 am
Thank you
SSCrazy
June 27, 2012 at 9:43 am
Still getting error in SSSS (Invoice format)
June 27, 2012 at 10:41 am
3935 (6/27/2012)
Still getting error in SSSS (Invoice format)
This is like taking your car to the shop and telling the mechanic, "My car doesn't work" and expecting them to know what to do to fix it.
You need to provide details about your tables and the issues. What is the error message? What is the structure of the table? What is the query? etc...
_______________________________________________________________
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 27, 2012 at 11:14 am
Insert into #Ashah (Invoice_Number)
values RIGHT ('0000'+Store#,4)+ RIGHT('0'+CAST(DATEPART(DAY,GETDATE()) AS varchar),2)
+ RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS varchar),2)
+ RIGHT(DATEPART(YEAR,GETDATE()),1)
I am trying to insert Invoice_Number , Format for that will be (SSSSDDMMY)
SSSS-means store number , Which can be 2 or 3 digit but for invoice i have to maintain 4 digit EXP.(0012,0121)
DD- Days
MM-Month
Y- Last digit of year
How can i do that ? Is my query is right ?
Because the error i am getting is like this
" Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'RIGHT'."
June 27, 2012 at 11:20 am
3935 (6/27/2012)
Insert into #Ashah (Invoice_Number)values RIGHT ('0000'+Store#,4)+ RIGHT('0'+CAST(DATEPART(DAY,GETDATE()) AS varchar),2)
+ RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS varchar),2)
+ RIGHT(DATEPART(YEAR,GETDATE()),1)
I am trying to insert Invoice_Number , Format for that will be (SSSSDDMMY)
SSSS-means store number , Which can be 2 or 3 digit but for invoice i have to maintain 4 digit EXP.(0012,0121)
DD- Days
MM-Month
Y- Last digit of year
How can i do that ? Is my query is right ?
Because the error i am getting is like this
" Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'RIGHT'."
You can't do an insert quite like that. When you use values in an insert it indicates you have hard coded values. You do not have hardcoded values, you are using functions and a column. Not really sure what Store# is or where it comes from.
Best guess since you still haven't really provided much detail...
Insert into #Ashah (Invoice_Number)
select RIGHT ('0000'+[Store#],4)+ RIGHT('0'+CAST(DATEPART(DAY,GETDATE()) AS varchar),2)
+ RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS varchar),2)
+ RIGHT(DATEPART(YEAR,GETDATE()),1)
from YourTable
_______________________________________________________________
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 27, 2012 at 11:51 am
I Got it guys , It was my mistake .
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply