Microsoft SQL Server
Transact-SQL

Оборотно-сальдовая ведомость

Опубликовано: 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



Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Microsoft SQL Server / Transact-SQL / Оборотно-сальдовая ведомость