YYMM ####如何生成发票号

时间：2021-09-20 07:20:07

i have to generate invoice number like YYMM#### YY and MM are easy and we can get them using today date but how to add custom 4 digit numbers starts from 001 and end 999 i am using sql for storing invoice numbers

我必须生成发票编号，如YYMM #### YY和MM很容易，我们可以使用今天的日期，但如何添加自定义4位数字从001和999结束我使用sql存储发票号码

2 个解决方案

#1

2  

If you only have at most 999 invoices per month, you probably don't need to worry two much about the inefficiencies involved in two invoices in quick succession, so each you need to generate an invoice:

如果您每月最多只能获得999张发票，那么您可能无需担心两张快速连续发票中的低效率，因此您需要生成发票：

  • Work out the prefix to use (be careful with time zones here...)
  • 计算出要使用的前缀（这里要小心时区......）
  • Find the highest invoice ID with a query to find all invoice IDs starting with your prefix (or between the min and max possible values if your using a numeric type), ordering by ID (descending), taking just the first one 1
  • 查找带有查询的最高发票ID，以查找以您的前缀开头的所有发票ID（如果使用数字类型，则在最小和最大可能值之间），按ID排序（降序），仅取第一个1
  • Parse this and add one to generate the new ID
  • 解析此并添加一个以生成新ID
  • Try to insert a new invoice with the given ID
  • 尝试插入具有给定ID的新发票
  • If you get a constraint violation (duplicate ID), go through the process again
  • 如果您遇到约束违规（重复ID），请再次执行该过程

If you had to handle lots of invoices, potentially generated from lots of different clients, and didn't need contiguous invoice IDs, you could use a lo/hi algorithm with each client effectively "reserving" IDs. That sounds like overkill for this situation though.

如果您必须处理许多可能由许多不同客户生成的发票，并且不需要连续的发票ID，您可以使用lo / hi算法，每个客户有效地“保留”ID。虽然这听起来有点矫枉过正。

Oh, and you should work out what you want to happen if there are more than 999 invoices in one single month...

哦，如果一个月内有超过999张发票，你应该弄清楚你想要发生什么......

1 You could potentially avoid the filtering here, and assume that everything else will follow the same convention, but personally I'd filter

1你可以在这里避免过滤，并假设其他一切都遵循相同的约定，但我个人会过滤

#2

2  

If you want to do this in SQL Server side , you would need to create a SEQUENCE object in sql server and you can do something like ..

如果你想在SQL Server端这样做，你需要在sql server中创建一个SEQUENCE对象，你可以做类似的事情。

SEQUENCE

CREATE SEQUENCE dbo.GetInvoiceNumber
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1 
    MAXVALUE 999
    CYCLE
    CACHE 10
;

SQL Server Call

SELECT CONVERT(VARCHAR(4), GETDATE(), 12) 
       + RIGHT('0000' 
       + CAST( NEXT VALUE FOR dbo.GetInvoiceNumber AS VARCHAR(3)),4)

Result

The query will return values like

查询将返回值，如

15080001
15080002
15080003
15080004
.......

Note

注意

Are you sure your customer will never produce more than 999 Invoices in one month, I think the number should be fairly high just in case.

您确定您的客户在一个月内永远不会生产超过999张发票，我认为这个数字应该相当高，以防万一。

#1

2  

If you only have at most 999 invoices per month, you probably don't need to worry two much about the inefficiencies involved in two invoices in quick succession, so each you need to generate an invoice:

如果您每月最多只能获得999张发票，那么您可能无需担心两张快速连续发票中的低效率，因此您需要生成发票：

  • Work out the prefix to use (be careful with time zones here...)
  • 计算出要使用的前缀（这里要小心时区......）
  • Find the highest invoice ID with a query to find all invoice IDs starting with your prefix (or between the min and max possible values if your using a numeric type), ordering by ID (descending), taking just the first one 1
  • 查找带有查询的最高发票ID，以查找以您的前缀开头的所有发票ID（如果使用数字类型，则在最小和最大可能值之间），按ID排序（降序），仅取第一个1
  • Parse this and add one to generate the new ID
  • 解析此并添加一个以生成新ID
  • Try to insert a new invoice with the given ID
  • 尝试插入具有给定ID的新发票
  • If you get a constraint violation (duplicate ID), go through the process again
  • 如果您遇到约束违规（重复ID），请再次执行该过程

If you had to handle lots of invoices, potentially generated from lots of different clients, and didn't need contiguous invoice IDs, you could use a lo/hi algorithm with each client effectively "reserving" IDs. That sounds like overkill for this situation though.

如果您必须处理许多可能由许多不同客户生成的发票，并且不需要连续的发票ID，您可以使用lo / hi算法，每个客户有效地“保留”ID。虽然这听起来有点矫枉过正。

Oh, and you should work out what you want to happen if there are more than 999 invoices in one single month...

哦，如果一个月内有超过999张发票，你应该弄清楚你想要发生什么......

1 You could potentially avoid the filtering here, and assume that everything else will follow the same convention, but personally I'd filter

1你可以在这里避免过滤，并假设其他一切都遵循相同的约定，但我个人会过滤

#2

2  

If you want to do this in SQL Server side , you would need to create a SEQUENCE object in sql server and you can do something like ..

如果你想在SQL Server端这样做，你需要在sql server中创建一个SEQUENCE对象，你可以做类似的事情。

SEQUENCE

CREATE SEQUENCE dbo.GetInvoiceNumber
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1 
    MAXVALUE 999
    CYCLE
    CACHE 10
;

SQL Server Call

SELECT CONVERT(VARCHAR(4), GETDATE(), 12) 
       + RIGHT('0000' 
       + CAST( NEXT VALUE FOR dbo.GetInvoiceNumber AS VARCHAR(3)),4)

Result

The query will return values like

查询将返回值，如

15080001
15080002
15080003
15080004
.......

Note

注意

Are you sure your customer will never produce more than 999 Invoices in one month, I think the number should be fairly high just in case.

您确定您的客户在一个月内永远不会生产超过999张发票，我认为这个数字应该相当高，以防万一。

标签：

相关文章