Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 создать последовательность значений поля с обнулением по истечению временного интервала  [new]
arhey85
Member

Откуда:
Сообщений: 6
Всем Здравствуйте!

Задача, похоже, простая, но увы...

Есть таблица a, которая, по сути, является отчетной формой, в которой, помимо прочего, для каждой записи есть дата записи (date, тип данных date, задается пользователем в приложении), есть поле для присвоения порядкового номера (regNumber, тип данных varchar) этой записи (речь не про identity) вида "ааа/число", то есть в итоге значения поля должны выглядеть так:

aaa/1
aaa/2
....
aaa/n

нюанс в том, что числовая последовательность должна обнуляться с наступлением нового года (технически - как только в БД появится запись с датой, где год будет больше на один предыдущего), то есть для первой записи за 2020 номер опять должен быть вида "aaa/1";

код приложения подразумевает, что остальные поля (в том числе date) к моменту генерации порядкового номера заполнены;
префикс (ааа), в принципе, могу вынести в код приложения, соответственно, тип данных в поле могу поменять на простой int

загвоздка сейчас в том, чтобы сгенерить это поле номера исходя из трех условий:


1. текущий год, если нет записей - присвоение стартового значения (оно будет больше 1);
2. текущий год, если есть записи - присвоение значения (предыдущее+1)
3. наступил следующий год - присвоение стартового значения (1) и далее по пункту 2



что делал: пробовал через declare выцеплять дату

        declare @year varchar (4);
	declare @prefix varchar (4); --установка префикса номера
	set @prefix='aaa';
	declare @Number varchar (5); --установка стартового значения номера (в текущем году оно будет > 1)
	--declare @Number int;
	set @Number='3';
	declare @currentyear varchar (4);  --пробую выцепить год
	set @currentyear='2019';

select @year = (select year(date) from a where regNumber is null) -- выбираю год из той строки, где не присвоен номер

if cast (@year as date)=cast(@currentyear as date) 


begin


update a set regNumber = @prefix+@Number;  --это присвоение первого номера в текущем году

end;

else 
begin 
set @Number='1'
update a set regNumber = @prefix+@Number; --это присвоение первого номера в  следующем году

end;

return 0
end



а вот как прописать присвоение номера к следующей записи (@prefix+(@Number+1)) ?

Также вопрос - возможно, лучше использовать просто update с when ... case?
31 окт 19, 13:51    [22007140]     Ответить | Цитировать Сообщить модератору
 Re: создать последовательность значений поля с обнулением по истечению временного интервала  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20181
Схематично. Берём макс. дату из таблицы и сравниваем её год с текущим годом. Равны? плюс 1 к максимальному номеру за текущий год. Иначе - просто 1.
31 окт 19, 14:07    [22007156]     Ответить | Цитировать Сообщить модератору
 Re: создать последовательность значений поля с обнулением по истечению временного интервала  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
arhey85,

звучит как сиквенс, с reset раз в год
31 окт 19, 14:16    [22007169]     Ответить | Цитировать Сообщить модератору
 Re: создать последовательность значений поля с обнулением по истечению временного интервала  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4535
TaPaK
arhey85,
звучит как сиквенс, с reset раз в год

С той лишь разничей, что документы у него (судя по описанию) могут проводиться "задним числом"... и да "пациент" будет переживать, если вдруг у него в нумерации появятся "дырки"
31 окт 19, 14:23    [22007178]     Ответить | Цитировать Сообщить модератору
 Re: создать последовательность значений поля с обнулением по истечению временного интервала  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
arhey85,

UPDATE a
SET regNumber = a.rn
FROM (

    SELECT a.*, ISNULL(a2.regNumber, 0) + ROW_NUMBER() OVER (PARTITION BY YEAR(a.date) ORDER BY a.date) AS rn
    FROM 
        a
        OUTER APPLY (SELECT MAX(regNumber) regNumber FROM a a2 WHERE YEAR(a.date) = YEAR(a2.date)) a2
    WHERE 
        a.regNumber is NULL
    ) AS a
31 окт 19, 14:26    [22007183]     Ответить | Цитировать Сообщить модератору
 Re: создать последовательность значений поля с обнулением по истечению временного интервала  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20181
buser
документы у него (судя по описанию) могут проводиться "задним числом"
Так это ни на что не влияет.
31 окт 19, 14:26    [22007184]     Ответить | Цитировать Сообщить модератору
 Re: создать последовательность значений поля с обнулением по истечению временного интервала  [new]
msLex
Member

Откуда:
Сообщений: 7727
Akina
buser
документы у него (судя по описанию) могут проводиться "задним числом"
Так это ни на что не влияет.

как не влияет, а как же после reset-а генерить номер в прошлом, закрытом периоде?
31 окт 19, 14:28    [22007188]     Ответить | Цитировать Сообщить модератору
 Re: создать последовательность значений поля с обнулением по истечению временного интервала  [new]
arhey85
Member

Откуда:
Сообщений: 6
Сорри, забыл указать, СУБД - mssql 2008r2, нет там секвенса (с 2012, вроде только) - увы, mssql не postegres
31 окт 19, 14:32    [22007194]     Ответить | Цитировать Сообщить модератору
 Re: создать последовательность значений поля с обнулением по истечению временного интервала  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20181
msLex
как же после reset-а генерить номер в прошлом, закрытом периоде?
Берём макс. номер за год, за который вставляется запись, и плюс один. Какие проблемы-то?
31 окт 19, 14:47    [22007220]     Ответить | Цитировать Сообщить модератору
 Re: создать последовательность значений поля с обнулением по истечению временного интервала  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20181
msLex
как же после reset-а генерить номер в прошлом, закрытом периоде?

Грубо
CREATE TRIGGER 
ON table
BEFORE UPDATE
SET INSERTED.number = 1 + COALESCE((SELECT MAX(table.number)
                                   FROM table
                                   WHERE YEAR(table.date) = YEAR(INSERTED.date))
                                  , 0)
31 окт 19, 14:52    [22007228]     Ответить | Цитировать Сообщить модератору
 Re: создать последовательность значений поля с обнулением по истечению временного интервала  [new]
msLex
Member

Откуда:
Сообщений: 7727
Akina
msLex
как же после reset-а генерить номер в прошлом, закрытом периоде?
Берём макс. номер за год, за который вставляется запись, и плюс один. Какие проблемы-то?

Видимо в том, что это уже не решение через сиквенс, которое комментировал buser
31 окт 19, 14:52    [22007230]     Ответить | Цитировать Сообщить модератору
 Re: создать последовательность значений поля с обнулением по истечению временного интервала  [new]
msLex
Member

Откуда:
Сообщений: 7727
Akina
msLex
как же после reset-а генерить номер в прошлом, закрытом периоде?

Грубо
CREATE TRIGGER 
ON table
BEFORE UPDATE
SET INSERTED.number = 1 + COALESCE((SELECT MAX(table.number)
                                   FROM table
                                   WHERE YEAR(table.date) = YEAR(INSERTED.date))
                                  , 0)




BEFORE UPDATE - это что?

вы веткой ошиблись
31 окт 19, 14:53    [22007233]     Ответить | Цитировать Сообщить модератору
 Re: создать последовательность значений поля с обнулением по истечению временного интервала  [new]
arhey85
Member

Откуда:
Сообщений: 6
Minamoto,

Огромное спасибо! То, что нужно!

Всем спасибо за помощь!
31 окт 19, 16:16    [22007338]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить