27 lines
881 B
Transact-SQL
27 lines
881 B
Transact-SQL
#Parameter1:Jahr:Text:currentyear
|
|
#Paraemter2:Sortierung:Table:Nach Name;Nach Datum letzter Rechnung
|
|
-SQL-
|
|
|
|
declare @nrp int
|
|
declare @dt datetime
|
|
select distinct *, cast('1900-01-01' as datetime) as lastfaktura into #tmp1 from privat a where a.nrprivat not in (select nrpatient from behandlu where year(behandlungsbeginn)<=#Parameter1)
|
|
declare xc cursor for
|
|
|
|
select nrprivat from #tmp1
|
|
open xc
|
|
fetch next from xc into @nrp
|
|
while @@fetch_status=0 begin
|
|
select top 1 @dt=datum from faktura where aktiv=1 and status<>9 and nrdebitor=@nrp order by datum desc
|
|
if @@ROWCOUNT>0 begin
|
|
update #tmp1 set lastfaktura=@dt where nrprivat=@nrp
|
|
end
|
|
fetch next from xc into @nrp
|
|
end
|
|
close xc
|
|
deallocate xc
|
|
if '#Parameter2'='Nach Name' begin
|
|
select * from #tmp1 order by name, vorname, ort
|
|
end else begin
|
|
select * from #tmp1 order by lastfaktura desc
|
|
end
|
|
drop table #tmp1 |