如何创建用递增填充字段的SQL函数

I have a table of tickets with a columns called ticket_number and date, which holds the date of creation.

我有一张带有名为ticket_number和date的列的票证表，其中包含创建日期。

The data in that table :

该表中的数据：

ticket_number   |   date 
150910.001          2015/09/10 
150910.002          2015/09/10 
150911.001          2015/09/11 
150911.002          2015/09/11 
150911.003          2015/09/11

I want to make a SQL function that create ticket_number based on the date. If the date of day is different, the last 3 digit reset to 001 again.

我想创建一个基于日期创建ticket_number的SQL函数。如果日期不同，则最后3位数字再次重置为001。

I am having trouble writing a SQL query to return such information.

我在编写SQL查询以返回此类信息时遇到问题。

So far,I have already tried :

到目前为止，我已经尝试过：

    CREATE FUNCTION FcNoTicket(@date as datetime)
    RETURNS CHAR(10)
    AS
    BEGIN
        DECLARE @newNumber char(10),@date_k char(7), @ticketNo as int
        SELECT @date_k =    RIGHT(Year(@date),2)+RIGHT('00'+CONVERT(NVARCHAR(2),DATEPART(MONTH,@date)),2)
    +RIGHT('00'+CONVERT(NVARCHAR(2),DATEPART(day,@date)),2)+'.' from tickets
        SELECT @ticketNo=ROW_NUMBER() OVER(PARTITION BY @date ORDER BY @date)
    from ticket
        SET @newNumber=@date_k+right('00'+cast(@ticketNo+1 as varchar(3)),3)
    RETURN @newNumber
END

That code return continuous increment.

该代码返回连续增量。

Does anyone have any ideas on how to do this?

有没有人对如何做到这一点有任何想法？

3 个解决方案

#1

What about something like this:

这样的事情怎么样：

all tickes in a single query. Just add a Create table AS
单个查询中的所有标记。只需添加一个Create table AS

SqlFiddle Demo

SqlFiddle演示

WITH tickets as (
    SELECT [date], 
           row_number() over( partition by [date] order by [date]) as rn
    FROM YourTable
)
SELECT [date],  RIGHT(Year([date]),2)
              + RIGHT('00' + CONVERT(NVARCHAR(2),DATEPART(MONTH,[date])),2)
              + RIGHT('00' + CONVERT(NVARCHAR(2),DATEPART(DAY  ,[date])),2)
              + '.'
              + RIGHT('000'+ CONVERT(NVARCHAR(3), rn), 3) as ticket_id
FROM tickets

OUTPUT

OUTPUT

|                        date |  ticket_id |
|-----------------------------|------------|
| September, 10 2015 00:00:00 | 150910.001 |
| September, 10 2015 00:00:00 | 150910.002 |
| September, 11 2015 00:00:00 | 150911.001 |
| September, 11 2015 00:00:00 | 150911.002 |
| September, 11 2015 00:00:00 | 150911.003 |

#2

SQL Fiddle

SQL小提琴

SELECT Replace(CONVERT(VARCHAR, ydate, 11), '/', '') 
       + '.' 
       + RIGHT('000'+Cast(Row_number() OVER(partition BY ydate 
                                            ORDER BY ydate)
                          AS VARCHAR(10)), 3) RN, 
       ydate 
FROM   yourtable

#3

Try this,

尝试这个，

DECLARE @t TABLE (
    ticket_number VARCHAR(20)
    ,ticketdate DATE
    )

INSERT INTO @t
VALUES ('150910.001','2015/09/10'),('150910.002','2015/09/10')
    ,('150911.001','2015/09/11'),('150911.002','2015/09/11')
    ,('150911.003','2015/09/11')

--select * from @t
DECLARE @ParameterDate DATETIME = '2015/10/11' -- input of UDF
DECLARE @ticket INT
DECLARE @i INT = 3 --how many zero to replicate

SELECT @ticket = max(SUBSTRING(ticket_number, CHARINDEX('.', ticket_number) + 1, len(ticket_number) 
- CHARINDEX('.', ticket_number)))
FROM @t
WHERE ticketdate = @ParameterDate

SET @ticket = isnull(@ticket, 0) + 1

--return this result from function
SELECT stuff(convert(VARCHAR(8), @ParameterDate, 112), 1, 2, '') + replicate('0', @i
 - len(@ticket)) + cast(@ticket AS VARCHAR)

#1