САБ/К2Н 2023 — разлика између измена

Извор: SI Wiki
Пређи на навигацију Пређи на претрагу
(Prethodna rešenja se oslanjaju na činjenicu da nijedna od hospitalizacija nema datum otpusta i na činjenicu da ne postoje hospitalizacije koje se preklapaju jer u svakoj bolnici leži po jedna osoba. Ovo rešenje bi trebalo da pokriva obe situacije i zato mislim da bi trebalo da bude dodato.)
Ред 112: Ред 112:
END
END
GO
GO
</syntaxhighlight>
== Решење 3 ==
<syntaxhighlight lang="SQL">
create function brojPacijenata(
@IdBolnice int,
@Datum date
)
returns int
as
begin
return
(
select count(*)
from Hospitalizacija
where IdBol = @IdBolnice and @Datum >= DatumPri and (DatumOtp is null or @Datum < DatumOtp)
)
end
go
create function iskoriscenost(
@IdBolnice int
)
returns char(1)
as
begin
declare @PrviPrijem date, @poslednjiOtpust date, @Kapacitet int
set @PrviPrijem = (
select min(DatumPri)
from Hospitalizacija
where IDBol = @IdBolnice
)
set @poslednjiOtpust = (
select MAX(COALESCE(DatumOtp, CAST(GETDATE() AS DATE)))
from Hospitalizacija
where IDBol = @IdBolnice
)
set @Kapacitet = (
select Kapacitet
from Bolnica
where IDBol = @IdBolnice
)
declare @Datum date, @BrojPacijenata int, @MaxBrojPacijenata int
set @Datum = @PrviPrijem
set @MaxBrojPacijenata = 0
while @Datum < @poslednjiOtpust
begin
set @BrojPacijenata = dbo.brojPacijenata(@IdBolnice, @Datum)
if (@BrojPacijenata > @MaxBrojPacijenata) set @MaxBrojPacijenata = @BrojPacijenata
set @Datum = DATEADD(day, 1, @Datum)
end
declare @Iskoriscenost float
set @Iskoriscenost = cast(@MaxBrojPacijenata as float) / @Kapacitet
return case when @Iskoriscenost > 0.75 then 'V' when @Iskoriscenost > 0.25 then 'S' else 'N' end
end
go
create procedure spAzurirajIskoriscenost
as
begin
declare @Cursor cursor, @IdBolnice int, @Iskoriscenost varchar(1)
set @Cursor = cursor for (
select IDBol
from Bolnica
)
open @Cursor
fetch next from @Cursor into @IdBolnice
while @@FETCH_STATUS = 0
begin
set @Iskoriscenost = dbo.iskoriscenost(@IdBolnice)
print(@Iskoriscenost)
update Bolnica
set Iskoriscenost = @Iskoriscenost
where IDBol = @IdBolnice
fetch next from @Cursor into @IdBolnice
end
close @Cursor
deallocate @Cursor
end
go
</syntaxhighlight>
</syntaxhighlight>


Верзија на датум 9. јул 2026. у 04:55

Поставка

У систему болничког лечења прате се информације о лекарима који раде у болници и прегледима који су ти лекари обавили. Сваки пацијент може имати највише један картон у свакој од болница. Сваки картон се повремено оверава када се уписује назив послодавца преко кога је пацијент осигуран. У случајевима када је то потребно пацијенти могу бити хоспитализовани, тј. примљени на болничко лечење (датум отпуштања се попуњава тек након што се лечење заврши, а до тада је NULL).
Одговарајућа шема базе је:

Mesto(IDMes, Naziv)
Bolnica (IDBol, Naziv, Kapacitet, Iskoriscenost, IDMes)
Pacijent(IDPac, Ime, JMBG, GodineStarosti, IDMes)
Lekar(IDLek, Ime, JMBG, Specijalizacija)
Karton(IdKar, IDPac, IDBol)
Pregled(IdPre, Datum, ObjektivniNalaz, DIjagnoza, IDKar, IdLek)
Hospitalizacija(IDHos, DatumPri, DatumOtp, Dijagnoza, KlinickaSlika, IDPac, IDBol)
Overa(IDKar, RedBr, DatumOd, NazivPoslodavca)
Radi(IDLek, IDBol)

Iskoriscenost: N- Niska, S - Srednja, V - Viskoa

За посматрани систем саставити SQLServer процедуру AzurirajIskoriscenost(), која поставља атрибут искоришћеност у релацији Болница. Искоришћеност се поставља на основу врше (максималне) вредности попуњености болнице у било којем тренутку до сада. Конкретно, уколико је у било ком тренутку број хоспитализованих паицјената био преко 75% капацитета, она је висока. Уколико је мање или једнако од 75%, а више од 25% онда је средња. У супротном, уколико је мање или једнако 25% онда је њена искоришћеност ниска. У дане боравка у болници треба укључити датум пријема, али не и датум отпуштања из болнице.

Решење

USE [BolnickoLecenje]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Sergej Vukasovic
-- Create date: 27.4.2024.
-- Description:	K2N 2023
-- =============================================
CREATE PROCEDURE AzurirajIskoriscenost
	@IdBol int
AS
BEGIN
	SET NOCOUNT ON;

    declare @isk decimal(10,2)
	declare @kap int

	select @kap = Kapacitet from Bolnica where IDBol = @IdBol
	select @isk = count(*) from Hospitalizacija h where DatumOtp is null and h.IDBol = @IdBol

	set @isk = @isk / @kap
	print(@isk)

	UPDATE Bolnica
	SET Iskoriscenost = CASE WHEN @isk > 0.75 THEN 'V'
							 WHEN @isk > 0.25 THEN 'S'
							 ELSE 'N' END
	WHERE IDBol = @IdBol
END
GO

Решење 2

CREATE PROCEDURE AzurirajIskoriscenost
AS
BEGIN
	DECLARE @percent float
	DECLARE @count int
	DECLARE @MyCursor CURSOR
	DECLARE @idBol int, @Kapacitet int

	SET @MyCursor = CURSOR FOR
	SELECT IdBol, Kapacitet FROM dbo.Bolnica

	OPEN @MyCursor 
	FETCH NEXT FROM @MyCursor
	INTO @idBol, @Kapacitet

	while @@FETCH_STATUS = 0
	BEGIN
		SET @percent = 0.0

		SELECT @count = COUNT(*)
		FROM dbo.Hospitalizacija H
		WHERE H.DatumOtp is NULL AND H.IDBol = @idBol
		GROUP BY H.IDBol

		print(@count)
		print(@Kapacitet)
		SET @percent = CAST(@count as float)/CAST(@Kapacitet as float)
		
		print(@percent)

		UPDATE Bolnica
		SET Iskoriscenost = case
			when @percent >= 0.75 then N'V'
			when @percent < 0.75 and @percent >= 0.25 then N'M'
			when @percent < 0.25 then N'S'
		end
		WHERE IDBol = @idBol

		FETCH NEXT FROM @MyCursor
		INTO @IdBol, @Kapacitet
	END

	CLOSE @MyCursor
	DEALLOCATE @MyCursor
END
GO

Решење 3

create function brojPacijenata(
	@IdBolnice int,
	@Datum date
)
returns int
as
begin
	return 
	(
		select count(*)
		from Hospitalizacija
		where IdBol = @IdBolnice and @Datum >= DatumPri and (DatumOtp is null or @Datum < DatumOtp)
	)
end
go

create function iskoriscenost(
	@IdBolnice int
)
returns char(1)
as
begin
	declare @PrviPrijem date, @poslednjiOtpust date, @Kapacitet int

	set @PrviPrijem = (
		select min(DatumPri)
		from Hospitalizacija
		where IDBol = @IdBolnice
	)

	set @poslednjiOtpust = (
		select MAX(COALESCE(DatumOtp, CAST(GETDATE() AS DATE)))
		from Hospitalizacija
		where IDBol = @IdBolnice
	)

	set @Kapacitet = (
		select Kapacitet
		from Bolnica
		where IDBol = @IdBolnice
	)

	declare @Datum date, @BrojPacijenata int, @MaxBrojPacijenata int
	
	set @Datum = @PrviPrijem
	set @MaxBrojPacijenata = 0

	while @Datum < @poslednjiOtpust
	begin
		set @BrojPacijenata = dbo.brojPacijenata(@IdBolnice, @Datum)
		if (@BrojPacijenata > @MaxBrojPacijenata) set @MaxBrojPacijenata = @BrojPacijenata
		
		set @Datum = DATEADD(day, 1, @Datum)
	end

	declare @Iskoriscenost float

	set @Iskoriscenost = cast(@MaxBrojPacijenata as float) / @Kapacitet

	return case when @Iskoriscenost > 0.75 then 'V' when @Iskoriscenost > 0.25 then 'S' else 'N' end
end
go

create procedure spAzurirajIskoriscenost
as
begin
	declare @Cursor cursor, @IdBolnice int, @Iskoriscenost varchar(1)

	set @Cursor = cursor for (
		select IDBol
		from Bolnica
	)

	open @Cursor
	fetch next from @Cursor into @IdBolnice
	
	while @@FETCH_STATUS = 0
	begin
		set @Iskoriscenost = dbo.iskoriscenost(@IdBolnice)

		print(@Iskoriscenost)

		update Bolnica
		set Iskoriscenost = @Iskoriscenost
		where IDBol = @IdBolnice

		fetch next from @Cursor into @IdBolnice
	end

	close @Cursor
	deallocate @Cursor
end
go

Тестирање

-- Kreiranje baze podataka
CREATE DATABASE BolnickoLecenje;
GO

-- Korišćenje baze podataka BolnickoLecenje
USE BolnickoLecenje;
GO

-- Kreiranje tabele Mesto
CREATE TABLE Mesto (
    IDMes INT PRIMARY KEY,
    Naziv NVARCHAR(50)
);

-- Kreiranje tabele Bolnica
CREATE TABLE Bolnica (
    IDBol INT PRIMARY KEY,
    Naziv NVARCHAR(100),
    Kapacitet INT,
    Iskoriscenost CHAR(1),
    IDMes INT,
    FOREIGN KEY (IDMes) REFERENCES Mesto(IDMes)
);

-- Kreiranje tabele Pacijent
CREATE TABLE Pacijent (
    IDPac INT PRIMARY KEY,
    Ime NVARCHAR(50),
    JMBG NVARCHAR(13),
    GodineStarosti INT,
    IDMes INT,
    FOREIGN KEY (IDMes) REFERENCES Mesto(IDMes)
);

-- Kreiranje tabele Lekar
CREATE TABLE Lekar (
    IDLek INT PRIMARY KEY,
    Ime NVARCHAR(50),
    JMBG NVARCHAR(13),
    Specijalizacija NVARCHAR(100)
);

-- Kreiranje tabele Karton
CREATE TABLE Karton (
    IdKar INT PRIMARY KEY,
    IDPac INT,
    IDBol INT,
    FOREIGN KEY (IDPac) REFERENCES Pacijent(IDPac),
    FOREIGN KEY (IDBol) REFERENCES Bolnica(IDBol)
);

-- Kreiranje tabele Pregled
CREATE TABLE Pregled (
    IdPre INT PRIMARY KEY,
    Datum DATE,
    ObjektivniNalaz NVARCHAR(MAX),
    DIjagnoza NVARCHAR(MAX),
    IDKar INT,
    IdLek INT,
    FOREIGN KEY (IDKar) REFERENCES Karton(IdKar),
    FOREIGN KEY (IdLek) REFERENCES Lekar(IDLek)
);

-- Kreiranje tabele Hospitalizacija
CREATE TABLE Hospitalizacija (
    IDHos INT PRIMARY KEY,
    DatumPri DATE,
    DatumOtp DATE,
    Dijagnoza NVARCHAR(MAX),
    KlinickaSlika NVARCHAR(MAX),
    IDPac INT,
    IDBol INT,
    FOREIGN KEY (IDPac) REFERENCES Pacijent(IDPac),
    FOREIGN KEY (IDBol) REFERENCES Bolnica(IDBol)
);

-- Kreiranje tabele Overa
CREATE TABLE Overa (
    IDKar INT,
    RedBr INT,
    DatumOd DATE,
    NazivPoslodavca NVARCHAR(100),
    FOREIGN KEY (IDKar) REFERENCES Karton(IdKar)
);

-- Kreiranje tabele Radi
CREATE TABLE Radi (
    IDLek INT,
    IDBol INT,
    FOREIGN KEY (IDLek) REFERENCES Lekar(IDLek),
    FOREIGN KEY (IDBol) REFERENCES Bolnica(IDBol)
);

-- Popunjavanje tabela Mesto, Bolnica, Pacijent, Lekar - Primer podataka
INSERT INTO Mesto (IDMes, Naziv) VALUES
(1, 'Beograd'),
(2, 'Novi Sad'),
(3, 'Niš');

INSERT INTO Bolnica (IDBol, Naziv, Kapacitet, Iskoriscenost, IDMes) VALUES
(1, 'Klinički centar Srbije', 1000, 'V', 1),
(2, 'Klinički centar Novi Sad', 800, 'S', 2),
(3, 'Klinički centar Niš', 700, 'N', 3);

INSERT INTO Pacijent (IDPac, Ime, JMBG, GodineStarosti, IDMes) VALUES
(1, 'Marko Marković', '0101991734567', 45, 1),
(2, 'Ana Anić', '0202992834567', 35, 2),
(3, 'Petar Petrović', '0303993934567', 50, 3);

INSERT INTO Lekar (IDLek, Ime, JMBG, Specijalizacija) VALUES
(1, 'Milica Milenković', '0404994834567', 'Opšta medicina'),
(2, 'Stefan Stefanović', '0505995934567', 'Hirurgija'),
(3, 'Jovana Jovanović', '0606996034567', 'Pediatrija');

-- Dodavanje ostalih podataka u preostale tabele prema potrebi
-- Popunjavanje tabele Karton - Primer podataka
INSERT INTO Karton (IdKar, IDPac, IDBol) VALUES
(1, 1, 1),
(2, 2, 2),
(3, 3, 3);

-- Popunjavanje tabele Pregled - Primer podataka
INSERT INTO Pregled (IdPre, Datum, ObjektivniNalaz, DIjagnoza, IDKar, IdLek) VALUES
(1, '2024-04-20', 'Normalan nalaz.', 'Prehlada', 1, 1),
(2, '2024-04-22', 'Upala slepog creva.', 'Akutni abdomen', 2, 2),
(3, '2024-04-25', 'Visoka temperatura.', 'Grip', 3, 3);

-- Popunjavanje tabele Hospitalizacija - Primer podataka
INSERT INTO Hospitalizacija (IDHos, DatumPri, DatumOtp, Dijagnoza, KlinickaSlika, IDPac, IDBol) VALUES
(1, '2024-04-20', NULL, 'Prehlada', 'Pacijent ima simptome prehlade.', 1, 1),
(2, '2024-04-22', NULL, 'Upala slepog creva', 'Pacijent ima jake bolove u stomaku.', 2, 2),
(3, '2024-04-25', NULL, 'Grip', 'Pacijent ima visoku temperaturu i kašalj.', 3, 3);

-- Popunjavanje tabele Overa - Primer podataka
INSERT INTO Overa (IDKar, RedBr, DatumOd, NazivPoslodavca) VALUES
(1, 1, '2024-04-20', 'RFZO'),
(1, 2, '2024-04-22', 'RFZO'),
(2, 1, '2024-04-22', 'Dunav osiguranje'),
(3, 1, '2024-04-25', 'Univerzal osiguranje');

-- Popunjavanje tabele Radi - Primer podataka
INSERT INTO Radi (IDLek, IDBol) VALUES
(1, 1),
(2, 2),
(3, 3);
Преузето из „https://siwiki.rs/w/index.php?title=САБ/К2Н_2023&oldid=8409”