Iata o problema reala, de care m-am lovit in timpul unui proiect: avem niste documente cu serie si numar; aceste numere de cele mai multe ori sunt consecutive, si pot fi intre intervale mari, de ordinul miilor, zecilor de mii; aceste documente fac miscari, se transfera, se consuma, fapt ce se vede prin schimbarea starii lor; se cere un raport care sa reflecte miscarile efectuate de aceste documente intr-o perioada de timp.Sa presupunem ca in baza de date avem urmatoarea situatie:
| Serie |
Numar |
Stare |
| ABC |
1 |
Disponibil |
| ABC |
2 |
Disponibil |
| ABC |
3 |
Disponibil |
| EFG |
1 |
Disponibil |
| EFG |
2 |
Disponibil |
| EFG |
3 |
Disponibil |
| EFG |
4 |
Disponibil |
| EFG |
5 |
Transferat |
| EFG |
6 |
Disponibil |
| EFG |
7 |
Transferat |
| EFG |
8 |
Disponibil |
| EFG |
9 |
Disponibil |
| EFG |
10 |
Transferat |
| EFG |
11 |
Transferat |
Cerinta este, ca in raport sa apara asa:
| Total |
Disponibile |
Transferate |
| ABC 1-3, EFG 1-11 |
ABC 1-3, EFG 1-4, EFG 6, EFG 8-9 |
EFG 5, EFG 7, EFG 10-11 |
Probabil exista mai multe solutii, cum ar fi o functie CRL scris in C#, dar am vrut neaparat sa rezolvam problema printr-o functie T-SQL.Deoarece aplicatia din lumea reala ruleaza pe un server de SQL 2005, nu am putut profita de beneficiile oferite de SQL Server 2008. Din cate am auzit, in SQL Server 2008 va fi posibil transmiterea unui tabel ca si parametru. In acest caz n-as mai incarca datele filtrate in interiorul functiei, pentru ca prin asta se pierde din portabilitatea algoritmului: trebuie modificate tabelele, numele de campuri din interiorul functiei, daca vrem s-o folosim pentru alte date. In SQL 2008 as prelua un tabel ca si parametru, si l-as trata ca si datele pentru care vreau sa creez intervalele. In functia de mai jos am incercat sa concentrez codul care afecteaza portabilitatea intr-o portiune cat mai restransa, si astfel sa usurez modificarile ulterioare atunci, cand cineva va dori sa foloseasca functia pe un alt set de date.
alter Function [dbo].[F_RetDocIntervalDupaStareSiData]
(--Parametri de intrare pentru filtrarea datelor:
--Data de inceput
@DataStart datetime,
--Data sfarsit a perioadei care ne intereseaza
@DataSfarsit datetime,
--Starea care ne intereseaza
@Stare varchar(50)
)
RETURNS varchar(8000)
AS
BEGIN
--Variabila in care vom intoarce intervalele
declare @retIntervaleSeriiNumere varchar(8000),
--Variabila cu care vom parcurge lista de documente
@serie varchar(50)
set @retIntervaleSeriiNumere = space(0)
--Definim a variabila temporara de tip tabel
--In acesta vom tine datele deja filtrate
--din care vom construi stringul cu intervale
declare @tabelDateFiltrate table(
idPK int identity(1,1),
idSerie int,
serie varchar(50),
numar int
)
--O variabila temporare de tip tabel
--in care tinem lista seriilor distincte
--din datele deja filtrate
declare @tabelSerii table(
idPKSerie int identity(1,1),
idSerie int,
serie varchar(50)
)
insert into @tabelDateFiltrate
select
IdSerie,
sSerie,
nNumarFila
from
--Functie care returneaza datele filtrate
--Poate fi inlocuita cu orice functie
F_RetTabelaSursaDinBD(@DataStart, @DataSfarsit, @Stare)
--Este obligatoriu ca aceasta lista
--sa fie sortata dupa numar
order by IdSerie,nNumarFila ASC
--Inseram in tabela temporara @tabelSerii
--doar seriile distincte dintre datele filtrate
insert into @tabelSerii
select idSerie,serie from @tabelDateFiltrate
group by idSerie,serie
order by idSerie asc
--O lista de variabile ajutatoare
--cu ajutorul carora vom parcurge lista
--filtrata de date, simuland folosirea
--unor cursori
declare @intervalCursor int,
@serieCursor int,
@idSerie int,
@maxInterval int,
@maxSerie int,
@intervStart int,
@urmator int,
@precedent int select
--Initializam cursorul cu care vom parcurge
--lista seriilor si stabilim maximul
@serieCursor = MIN(idPKSerie),
--Initializam cursorul cu care vom parcurge
--lista seriilor si stabilim maximul
@maxSerie = MAX(IdPKSerie)
from @tabelSerii
--Primul while, cel exterior, pentru serii
while @serieCursor <= @maxSerie
begin
select @idSerie = idSerie,
@serie = serie
from
@tabelSerii
where idPKSerie = @serieCursor
select
--Initializam cursorul pentru numerele
--din seria selectata
@intervalCursor =MIN(idPK),
@maxInterval = MAX(idPK)
from
@tabelDateFiltrate
where
idSerie = @idSerie
--Punem in @intervStart primul numar din serie
select TOP 1
@intervStart = isnull(numar,0)
from
@tabelDateFiltrate
where
idSerie = @idSerie
--Setam ca si numar precedent primul numar
set @precedent = @intervStart
--Al doilea while, cel interior, pentru numere
while @intervalCursor < @maxInterval
begin
select
--@urmator = listaFiltrata[@intervalCursor]
@urmator = numar from
@tabelDateFiltrate
where idPK
= @intervalCursor+1
--Daca diferenta intre numarul precedent si cel curent
--e mai mare de 1, atunci s-a terminat un interval
if @urmator - @precedent > 1 BEGIN
--Se adauga la string-ul ce va fi returnat intervalul gasit: @intervStart-@precedent
--sau doar @intervStart, daca intervalul e compus dintr-un singur numar
set @retIntervaleSeriiNumere = @retIntervaleSeriiNumere + ',' + case when @intervStart = @precedent then @serie + ' ' + cast(@precedent as varchar) else @serie + ' ' + cast(@intervStart as varchar)+'-'+cast(@precedent as varchar) END
--Urmatorul interval va incepe de la numarul curent
set @intervStart = @urmator
END
--Daca diferenta este de doar 1
--Pregatim urmatorul ciclu
set @precedent = @urmator
set @intervalCursor = @intervalCursor + 1
END
--La sfarsitul ciclului adaugam ultimul interval gasit
set @retIntervaleSeriiNumere = @retIntervaleSeriiNumere + ',' + case when @intervStart = @precedent then @serie + ' ' + cast(@precedent as varchar) else @serie + ' ' + cast(@intervStart as varchar)+'-'+cast(@precedent as varchar) end
--Avansam la seria urmatoare si
--reluam gasirea intervalelor pentru acea serie
set @serieCursor = @serieCursor + 1
END
--Daca a fost gasit macar un interval, se sterge virgula de la
--inceputul stringului ce urmeaza a fi returnat
set @retIntervaleSeriiNumere = case when len(@retIntervaleSeriiNumere )>1 then substring(@retIntervaleSeriiNumere ,2,len(@retIntervaleSeriiNumere )-1) end
--Returnam rezultatul
return @retIntervaleSeriiNumere
END
Subscribe