MS SQL Indywidualny licznik dla danego ID

Witam,
poszukuję rozwiązania do poniższego zadania.
Chcę stworzyć licznik dla danego Id oparty o WARTOŚĆ.
Licznik ma się zerować co 4 lub jeśli wartość jest inna niż 10, jak w załączonym przykładzie.
W tabeli jest kilkaset ID i dla każdego ID licznik musi działać osobno.
Czy ktoś ma jakiś pomysł?

trigger, ale i tak będzie problem jak będzie dużo insertów w krótkim czasie. Aby to było w 100% pewne to trzeba by mieć dodatkową tabelkę, a w niej id i aktualny_licznik. Pobierać z blokowaniem stan licznika dla danego id, wyliczać nową wartość, wstawiać rekord i updejtować ten w tabelce z licznikiem

Która wersja SQL Servera?
Co determinuje kolejność?

Panczo napisał(a):

Która wersja SQL Servera?
Co determinuje kolejność?

2014, kolejność determinuje data

Jeśli to coś pomoże to licznik może się zerować tylko wtedy kiedy wartość jest różna od 10.

Potrzebujesz tego w czasie rzeczywistym? Jeżeli nie, to co jakiś czas możesz robić update na kolumnie i problem staje się całkiem prosty do rozwiązania.

To jest tabela, która jest zasilana danymi codziennie.
Codziennie wpada kilkadziesiąt wierszy danych. Liczba wierszy cały czas rośnie.
W pewnym momencie osiagnie kilka tysięcy wierszy więc update'owanie tej tabeli przy kilkuset ID nie tym o czym marze :)
wolałbym ustawić tutaj automat.

Możesz stworzyć joba, który będzie codziennie robił update na tabeli. Nie korzystam z MS serwera, ale ktoś na pewno Ci podpowie jak to zrobić.

To też jakiś pomysł, potrafię zrobić joba. dzięki.

A jaka jest pewność ze zawsze dostajesz większe daty, co w przypadku gdy ktoś wprowadzi daty wczesniejsze i będzie to wymagalo zmiany w innych rekordach?
Jak tabela jest zasilana?

To jest zrzut z CRMa po wizytach handlowców. Data wstecz nie jest możliwa.

Not skoro to jest zrzut z jakiegoś systemu, to jaki masz wpływ na wykonanie tego importu.
Mnie się wydaje, że najrozsądniej jest paczkę danych dać do jakiejś tempowej tabeli, wyliczyć te id i wrzucić do tabeli docelowej.

Chyba, że na docelowej tabeli działają inni użytkownicy i dodają usuwają dane. Z ciekawości, do czego służyć ma takie numerowanie?

p.s. jak słyszę że ktoś mi mówi, że coś jest niemożliwe to na pewno się przydarzy ;)

Numerowanie jest potrzebne do weryfikowania poprawnosci podpisanego kontraktu pomiedzy moja firma a klientami. Jezeli klient podczas czterech wizyt przedstawiciela z rzędu spelni warunki kontraktu to jest mu wyplacany bonus. Bonus jest wyplacany ci 4 poprawne wizyty. U danego klienta jest jedna wizyta w tygodniu. Jesli na ktorejs z wizyt nie spelnia warunkow kontraktu to licznik wraca do 1.

Computed column.

Pytanie czy faktycznie potrzebujesz numerku przy kazdym rekordzie/wizycie, czy wystarczy informacja w ramach id ile tych bonusów powinno być?

Nie potrzebuje numerka :)

No to skoro nie potrzebujesz numerka, to wystarczy sprawdzić ile rekordów jest pomiędzy zmianami i podzielić przez 4 bez reszty, ale pokolei

Dla twoich danych

CREATE TABLE T
    ([id] int, [wartosc] int, [data] int)
;
    
INSERT INTO T
    ([id], [wartosc], [data])
VALUES
    (354, 10,1),
    (354, 10,2),
    (354, 10, 3),
    (354, 10, 4),
    (354, 10, 5),
    (354, 10, 6),
    (354, 8,  7),
    (354, 10, 8),
    (354, 10, 9),
    (354, 10, 10),
    (354, 10, 11)

Date zrobiłem int, ale to bez znaczenia bo ta kolumna służy tylko do sprawdzenia kolejności.
Pierwsze co to musimy zidentyfikować początek i koniec przedziałów czwórek, w naszym przypadku to są dwa przedziały:
data: 1-6 i 7-11
Do tego posłuży zapytanie

select 
	* ,
	CASE 
		WHEN (LAG(WARTOSC, 1,-1) OVER (PARTITION BY ID ORDER BY DATA) <> WARTOSC AND WARTOSC <> 10)
			OR  LAG(WARTOSC, 1,-1) OVER (PARTITION BY ID ORDER BY DATA) = -1 THEN 
				'START' 
		WHEN LEAD(WARTOSC, 1,0) OVER (PARTITION BY ID ORDER BY DATA) <> 10 THEN 
			'STOP' 
		ELSE '' 
	END AS STARTSTOP
	,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATA) R 
from 
	t

Które da w wyniku:

Teraz potrzebujemy tylko wierszy z wpisami w kolumnie startstop:

Pozostaje matematyka: czyli dla każdego wpisu stop, odejmujemy od r poprzednie r dodajmy 1 i dzielimy bez reszty przez 4:

select 
	*
	,case when startstop = 'stop' then 
		cast(r+1-LAG(r, 1,0) OVER (PARTITION BY ID ORDER BY DATA) as integer)/4
	else
		0
	end iloscbonusow
from f

Wynik

Reszta to podsumowanie kolumny iloscbonusow
Całe rozwiązanie:

WITH cteT as (
select 
	* ,
	CASE 
		WHEN (LAG(WARTOSC, 1,-1) OVER (PARTITION BY ID ORDER BY DATA) <> WARTOSC AND WARTOSC <> 10)
			OR  LAG(WARTOSC, 1,-1) OVER (PARTITION BY ID ORDER BY DATA) = -1 THEN 
				'START' 
		WHEN LEAD(WARTOSC, 1,0) OVER (PARTITION BY ID ORDER BY DATA) <> 10 THEN 
			'STOP' 
		ELSE '' 
	END AS STARTSTOP
	,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATA) R 
from 
	t)
, f as (
select 
	* 
from 
	cteT
where 
	startstop in ('start','stop')
)

SELECT
	ID
	,SUM(ILOSCBONUSOW) B
FROM (
	select 
		id
		,case when startstop = 'stop' then 
			cast(r+1-LAG(r, 1,0) OVER (PARTITION BY ID ORDER BY DATA) as integer)/4
		else
			0
		end iloscbonusow
	from 
		f) DT
GROUP BY
	ID

Wynik:

Dostałem za zadanie rozwiązać to za pomocą kursora.

Stworzyłem kursor ale licznik nie zeruje mi się kiedy zmienia się ID klienta. Czy ktoś może mi powiedzieć gdzie popełniłem błąd?

===============================================================================================================
DECLARE @row int
DECLARE @kli int
DECLARE @poprzedni_kli int
DECLARE kursor SCROLL cursor for
SELECT rowid, rep_id FROM crm_target_kampanie WHERE campaign = 'akcja_lato_licznik' and target=10 ORDER BY rowid
DECLARE @counter int=1

Open kursor

FETCH NEXT FROM kursor
INTO @row, @kli
WHILE @@FETCH_STATUS=0
BEGIN

SET @poprzedni_kli=@kli

IF @poprzedni_kli=@kli
BEGIN 
update crm_target_kampanie set attrib1=@counter WHERE campaign = 'akcja_lato_licznik' and rep_id=@kli and rowid=@row
SET @counter=@counter+1
END

ELSE

BEGIN
SET @counter=1 
update crm_target_kampanie set attrib1=@counter WHERE campaign = 'akcja_lato_licznik' and rep_id=@kli and rowid=@row
SET @counter=@counter+1
END

FETCH NEXT FROM kursor 
INTO @row, @kli 

END
CLOSE kursor
DEALLOCATE kursor

=================================================================================================================

A jak ci ma się zerować, skoro @poprzedni_kli=@kli zawsze będzie prawdziwe, bo PRZED porównaniem przypisujesz bieżącą wartość do poprzedniej?

Panczo napisał(a):

A jak ci ma się zerować, skoro @poprzedni_kli=@kli zawsze będzie prawdziwe, bo PRZED porównaniem przypisujesz bieżącą wartość do poprzedniej?

Domyśliłem się, że to nie działa ale właśnie nie wiem w którym miejscu w takim razie umieścić "SET @poprzedni_kli=@kli"

Najprościej wtedy kiedy się zmienia...

DECLARE @row int
DECLARE @kli int
DECLARE @poprzedni_kli int=-1
DECLARE kursor SCROLL cursor for
SELECT rowid, rep_id FROM crm_target_kampanie WHERE campaign = 'akcja_lato_licznik' and target=10 ORDER BY rowid
DECLARE @counter int=1

Open kursor
FETCH NEXT FROM kursor INTO @row, @kli 

WHILE @@FETCH_STATUS=0
	BEGIN
		IF @poprzedni_kli=@kli
			BEGIN 
				update crm_target_kampanie set attrib1=@counter WHERE campaign = 'akcja_lato_licznik' and rep_id=@kli and rowid=@row
				SET @counter=@counter+1
			END
		ELSE
			BEGIN
				SET @poprzedni_kli=@kli
				SET @counter=1 
				update crm_target_kampanie set attrib1=@counter WHERE campaign = 'akcja_lato_licznik' and rep_id=@kli and rowid=@row
				SET @counter=@counter+1
			END
		FETCH NEXT FROM kursor INTO @row, @kli 
	END

CLOSE kursor
DEALLOCATE kursor

Dużo czytelniej jest jednak uprościć samą pętle:

WHILE @@FETCH_STATUS=0
BEGIN

	IF @poprzedni_kli<>@kli
	BEGIN
		SET @poprzedni_kli=@kli
		SET @counter=1 
	END		

	update crm_target_kampanie set attrib1=@counter WHERE campaign = 'akcja_lato_licznik' and rep_id=@kli and rowid=@row			

	SET @counter=@counter+1

	FETCH NEXT FROM kursor INTO @row, @kli 

END

Wielkie dzięki :)

druga opcja jednak nie działa - nie zeruje licznika przy nowym kliencie