Опубликовано: 21 апр 03
Рейтинг:
Рейтинг:
Автор: Cat2
Прислал: Cat2
set nocount on
/*
Тестовая таблица
Дата совершения операции
Номер склада
Количество. Приход - плюс. Расход - минус
Необходимо получить оборотно-сальдовую ведомость движения количеств по складам
Использован синтаксис T-SQL для MS SQL server
*/
create table mytable (WorkDate datetime, Warehouse int, Quanity int)
insert into mytable values ('20020101',1,10)
insert into mytable values ('20020101',2,10)
insert into mytable values ('20030101',1,10)
insert into mytable values ('20030101',2,-10)
go
declare @FirstDate datetime
declare @LastDate datetime
set @FirstDate='20030101'
set @LastDate='20030201'
--Вариант 1. Использование временных таблиц
select Warehouse,
sum(Quanity) as QuanityStart, 0 as QuanityInCom, 0 as QuanityOutCom
into #t
from mytable
where WorkDate<@FirstDate
group by Warehouse
union
select Warehouse,0,
sum(case when Quanity>0 then Quanity else 0 end),
-sum(case when Quanity>=0 then 0 else Quanity end)
from mytable
where WorkDate between @FirstDate and @LastDate
group by Warehouse
select Warehouse, sum(QuanityStart) as QuanityStart,
sum(QuanityInCom) as QuanityInCom, sum(QuanityOutCom) as QuanityOutCom,
sum(QuanityStart+QuanityInCom-QuanityOutCom) as QuanityLast
from #t
group by Warehouse
--WITH ROLLUP --Если надо получить итоговою строку
drop table #t
--Вариант 2. Селект по селекту
select Warehouse, sum(QuanityStart) as QuanityStart,
sum(QuanityInCom) as QuanityInCom, sum(QuanityOutCom) as QuanityOutCom,
sum(QuanityStart+QuanityInCom-QuanityOutCom) as QuanityLast
from
(
select Warehouse,
sum(Quanity) as QuanityStart, 0 as QuanityInCom, 0 as QuanityOutCom
from mytable
where WorkDate<@FirstDate
group by Warehouse
union
select Warehouse,0,
sum(case when Quanity>0 then Quanity else 0 end),
-sum(case when Quanity>=0 then 0 else Quanity end)
from mytable
where WorkDate between @FirstDate and @LastDate
group by Warehouse
) as t
group by Warehouse
--WITH ROLLUP --Если надо получить итоговою строку
go
drop table mytable
/************* Cat2 *************/
Комментарии
А вот так быстрее (если более поздние периоды актуальнее прошлых):
create view dbo.v_mytable with schemabinding as
select Warehouse,sum(isnull(Quanity,0)) as Quanity,count_big(*) as CNT
from dbo.mytable
group by Warehouse
go
create unique clustered index ix_v_mytable on dbo.v_mytable (WareHouse)
go
declare @FirstDate datetime
declare @LastDate datetime
set @FirstDate='20030101'
set @LastDate='20030201'
select Warehouse,
sum(QuanityStart) as QuanityStart,
sum(QuanityInCom) as QuanityInCom,
sum(QuanityOutCom) as QuanityOutCom,
sum(QuanityLast) as QuanityLast
from (
select Warehouse,Quanity as QuanityStart,0 as QuanityInCom,0 as QuanityOutCom,Quanity as QuanityLast
from v_mytable with (noexpand)
union all
select Warehouse,
-Quanity,
case when WorkDate<=@LastDate and Quanity>0 then Quanity else 0 end,
case when WorkDate<=@LastDate and Quanity<0 then -Quanity else 0 end,
case when WorkDate>@LastDate then -Quanity else 0 end
from mytable
where WorkDate>=@FirstDate
) a
group by Warehouse
Не самые лучшие методы.
Вот так проще:
select Warehouse,
sum(case when WorkDate<@FirstDate then Quanity else 0 end) as QuanityStart,
sum(case when WorkDate>=@FirstDate and Quanity>0 then Quanity else 0 end) as QuanityInCom,
sum(case when WorkDate>=@FirstDate and Quanity<0 then -Quanity else 0 end) as QuanityOutCom,
sum(Quanity) as QuanityLast
from mytable
where WorkDate<=@LastDate
group by Warehouse