CREATE OR ALTER FUNCTION [dbo].[nakaza_po_dnech]
(	
	@datum_od as date, 
	@datum_do as date
)
RETURNS TABLE 
AS
RETURN 
(
	select 
	0 as my_order,
	NULL as datum,
	sum(case n.typ_hlaseni when 'N' then 1 else 0 end) as nakaza,
	sum(case n.typ_hlaseni when 'U' then 1 else 0 end) as umrti,
	sum(case n.typ_hlaseni when 'V' then 1 else 0 end) as vyleceni
	from [dbo].[nakaza]  n
	where n.nak_datum_hlaseni<@datum_od
	UNION
	select 
	1 as my_order,
	n.nak_datum_hlaseni,
	sum(case n.typ_hlaseni when 'N' then 1 else 0 end) as nakaza,
	sum(case n.typ_hlaseni when 'U' then 1 else 0 end) as umrti,
	sum(case n.typ_hlaseni when 'V' then 1 else 0 end) as vyleceni
	from [dbo].[nakaza]  n
	where n.nak_datum_hlaseni>=@datum_od and n.nak_datum_hlaseni<=@datum_do
	group by n.nak_datum_hlaseni
	UNION
	select
	2 as my_order,
	NULL as datum,
	sum(case n.typ_hlaseni when 'N' then 1 else 0 end) as nakaza,
	sum(case n.typ_hlaseni when 'U' then 1 else 0 end) as umrti,
	sum(case n.typ_hlaseni when 'V' then 1 else 0 end) as vyleceni
	from [dbo].[nakaza]  n
	where n.nak_datum_hlaseni>@datum_do
)
GO


