Podstawy T-SQL

Komendy:

1) Napisać procedurę (z parametrami) która dla wskazanego nr_samochodu i wskazanego roku wyświetli wszystkie wypożyczenia tego samochodu w tym roku (lista pól : nr_samochodu,rok, data_wyp,data_odd, liczba dni trwania danego wypożyczenia w tym roku)
—– Do każdego wyniku jest doliczony jeden dzień tak by np. wypożyczenie w dniach 28-29 dawało wartość 2! (w przypadku użycia funkcji datediff wartość domyślnie dałaby 1)

GO
create proc dbo.wypozyczenia_danego_auta_w_roku @rok int, @nr_samochodu int
as
select NR_SAMOCHODU as „Numer samochodu”, datepart(year,data_wyp) as „Rok wypozyczenia”, DATA_WYP, DATA_ODD,
(CASE
—– Warunek bierze pod uwagę samochody nieoddane, wypożyczone w aktualnym roku (ilośc dni obliczana jest do dnia dzisiejszego)
WHEN datepart(year,data_odd) is null and datepart(year,data_wyp) = datepart(year,getdate()) THEN datediff(day,data_wyp,GETDATE()) +1
—– Warunek bierze pod uwagę samochody nieoddane, w poprzednich latach (ilośc dni obliczana jest do OSTATNIEGO dnia danego roku)
WHEN datepart(year,data_odd) is null and datepart(year,data_wyp) != datepart(year,getdate()) THEN datediff(day,data_wyp,EOMONTH(DATA_WYP, (12 – datepart(month,data_wyp)))) +1
—– Warunek bierze pod uwagę samochody wypożyczone na przełomie różnych lat (ilość dni liczona jest tylko do końca roku wypożyczenia)
WHEN datepart(year,data_odd) is not null and datepart(year,data_wyp) != datepart(year,data_odd) THEN datediff(day,data_wyp,EOMONTH(DATA_WYP, (12 – datepart(month,data_wyp)))) +1
—– Wszystkie pozostałe przypadki tzn. samochody wypożyczone i oddane w tym samym roku
ELSE datediff(day,data_wyp,data_odd) + 1
END) AS „Ilosc dni”
from wypaut2012.dbo.WYP
where nr_samochodu = @nr_samochodu and datepart(year,data_wyp) = @rok;

WYKONANIE

GO
EXEC dbo.wypozyczenia_danego_auta_w_roku @rok = 2010, @nr_samochodu= 2

2) Zbudować funkcję skalarną w której argumentem (zmienną) będzie numer samochodu a zwracaną wartością będzie łączny czas trwania wypożyczenia tego samochodu.

GO
create function dbo.suma_dni_wypozyczenia_auta (@nr_samochodu int)
returns int
as
begin
declare @ilosc_dni int
return
(
select
sum(CASE
WHEN data_odd is not NULL THEN cast(datediff(day,data_wyp,data_odd) as int)
—– Warunek zakłada że jeśli samochód ma pole „DATA_ODD” puste, czyli że samochód NIE ZOSTAŁ oddany, czyli ilość dni liczona jest do dnia wykonania skryptu! (przez co wartości czasami są bardzo duże(różnica kilku lat))
ELSE cast(GETDATE() – data_wyp as int)
END)
from wypaut2012.dbo.WYP
where DATEPART(yyyy,DATA_ODD) is not null and NR_SAMOCHODU=@nr_samochodu
)
end

WYKONANIE

GO
SELECT dbo.suma_dni_wypozyczenia_auta(2) AS „Ilosc dni”

3) Zbudować funkcję tabelaryczną w której argumentem (zmienną) będzie rok a zwracana będzie tabela zawierająca numery samochodu i łączny czas wypożyczenia każdego z nich w tym roku.
—– Do każdego wyniku jest doliczony jeden dzień tak by np. wypożyczenie w dniach 28-29 dawało wartość 2! (w przypadku użycia funkcji datediff wartość domyślnie dałaby 1)

GO
create function dbo.ilosc_dni_wyp_auta_w_roku (@rok int)
returns table as return
(
select NR_SAMOCHODU,
sum(CASE
—– Warunek bierze pod uwagę samochody nieoddane, wypożyczone w aktualnym roku (ilośc dni obliczana jest do dnia dzisiejszego)
WHEN datepart(year,data_odd) is null and datepart(year,data_wyp) = datepart(year,getdate()) THEN datediff(day,data_wyp,GETDATE()) +1
—– Warunek bierze pod uwagę samochody nieoddane, w poprzednich latach (ilośc dni obliczana jest do OSTATNIEGO dnia danego roku)
WHEN datepart(year,data_odd) is null and datepart(year,data_wyp) != datepart(year,getdate()) THEN datediff(day,data_wyp,EOMONTH(DATA_WYP, (12 – datepart(month,data_wyp)))) +1
—– Warunek bierze pod uwagę samochody wypożyczone na przełomie różnych lat (ilość dni liczona jest tylko do końca roku wypożyczenia)
WHEN datepart(year,data_odd) is not null and datepart(year,data_wyp) != datepart(year,data_odd) THEN datediff(day,data_wyp,EOMONTH(DATA_WYP, (12 – datepart(month,data_wyp)))) +1
—– Wszystkie pozostałe przypadki tzn. samochody wypożyczone i oddane w tym samym roku
ELSE datediff(day,data_wyp,data_odd) +1
END)
as „Ilosc dni” from wypaut2012.dbo.WYP where datepart(year,data_wyp)=@rok
group by NR_SAMOCHODU
)

WYKONANIE

GO
SELECT * FROM dbo.ilosc_dni_wyp_auta_w_roku(2010) order by nr_samochodu

Baza danych: Microsoft SQL Server