Функция, возвращающая табличное значение. Каждоя строка занимает свое место в иерархии, определенное полем parentid. В функции собираются суммы по документам за определенный период. Использоваться может в отчетах по продажах, нагрузкам.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER                FUNCTION [dbo].[Fin_report](@db datetime, @de datetime)
RETURNS
 @tab table (id int, parentid int, name varchar(500),summa money, kol money, ccount int)

AS
BEGIN
Insert  @tab
Select 1,0,'Финансы',              Isnull(sum(D.summa),0), Isnull(sum(D.kol),0),count(D.id) from Document D with(nolock) where D.pr = 0 and D.data between @db and @de

union all

Select 2,1,'Поминутный загар',     Isnull(sum(D.summa),0), Isnull(sum(D.kol),0),count(D.id) from Document D with(nolock) where D.pr = 0 and D.vid = 'Время' and D.data between @db and @de

union all

Select 3,1,'Товары',               Isnull(sum(D.summa),0), Isnull(sum(D.kol),0),count(D.id) from Document D with(nolock) where D.pr = 0 and D.vid = 'Товар' and D.data between @db and @de

union all

Select 4,1,'Услуги',               Isnull(sum(D.summa),0), Isnull(sum(D.kol),0),count(D.id) from Document D with(nolock) where D.pr = 0 and D.vid = 'Услуги' and D.data between @db and @de

union all

Select 5,1,'Абонементы',           Isnull(sum(D.summa),0), Isnull(sum(D.kol),0),count(D.id) from Document D with(nolock) where D.pr = 0 and D.vid = 'Абонемент' and D.data between @db and @de

union all

Select 6,1,'Баланс',               Isnull(sum(D.summa),0), Isnull(sum(D.kol),0),count(D.id) from Document D with(nolock) where D.pr = 0 and D.vid = 'Баланс' and D.data between @db and @de

union all

Select 7,1,'Аренда',               Isnull(sum(D.summa),0), Isnull(sum(D.kol),0),count(D.id) from Document D with(nolock) where D.pr = 0 and D.vid = 'Аренда' and D.data between @db and @de

union all

Select 10+S.ID,max(2),S.name,      Isnull(sum(D.summa),0), Isnull(sum(D.kol),0),count(D.id) from Solar S with(nolock) left join Document D On S.ID = D.key2 and D.pr = 0 and D.vid = 'Время' and D.data between @db and @de
group by 10+S.ID,S.name

union all

Select 100+T.id,max(3),T.name,     Isnull(sum(D.summa),0), Isnull(sum(D.kol),0),count(D.id) from Tovar T with(nolock) left join Document D On T.ID = D.key2 and D.pr = 0 and D.vid = 'Товар' and D.data between @db and @de where D.id is not null
group by 100+T.ID,T.name

union all

Select 1000+T.id,max(4),T.name,    Isnull(sum(D.summa),0), Isnull(sum(D.kol),0),count(D.id) from Usluga T with(nolock) left join Document D On D.anul = 0 and T.ID = D.key2 and D.pr = 0 and D.vid = 'Услуги' and D.data between @db and @de where D.id is not null
group by 1000+T.ID,T.name

union all

Select 10000+VA.id,max(5),VA.name, Isnull(sum(D.summa),0), Isnull(sum(D.kol),0),count(D.id) from abonement T with(nolock) left join vidab VA On VA.id = T.key2 left join Document D On T.ID = D.key2 and D.pr = 0 and D.vid = 'Абонемент' and D.data between @db and @de where D.id is not null
group by 10000+VA.ID,VA.name

union all

Select 100000+T.id,max(6),T.name,  Isnull(sum(D.summa),0), Isnull(sum(D.kol),0),count(D.id) from client T with(nolock) left join Document D On T.ID = D.clientid and D.pr = 0 and D.vid = 'Баланс' and D.data between @db and @de where D.id is not null
group by 100000+T.ID,T.name

union all

Select 1000000+T.id,max(7),T.name,  Isnull(sum(D.summa),0), Isnull(sum(D.kol),0),count(D.id) from Tovar T with(nolock) left join arenda A on A.Tovarid = T.ID le