Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 FIFO от последнего остатка  [new]
Spl@sH
Member

Откуда:
Сообщений: 50
Ув.Эксперты, поиск по форуму, конечно, дал некоторую пищу для размышления, но полного понимания нет и я прошу помочь советом.

Задача такая: нужна реализация партиционного учета по модели FIFO для таблицы приходов/расходов.
т.е., нужно сопоставить операции прихода/расхода (в обратном порядке), разбить их по партиям и посчитать остатки после каждой операции.
Akina
прямой связи прихода с расходом нет - то есть один приход может быть поделен на несколько расходов, причём два из них поделены между ним и другим приходом

и все это на Microsoft SQL Server 2008 R2 (SP2) - 10.50.4321.0 (Build 7601: Service Pack 1)

Дано (упрощенно):
1. таблица прихода/расхода - примерно, такого содержания:

create table #t (id int, cod char(9), dt datetime, op char(1), quantity int)
insert into #t values (1, '123456789', '2014-06-18 00:00:00.000', 'r', 1330)
insert into #t values (2, '123456789', '2014-06-27 00:00:00.000', 'p', 4330)
insert into #t values (3, '123456789', '2014-07-09 00:00:00.000', 'r', 2000)
insert into #t values (4, '123456789', '2014-07-17 00:00:00.000', 'r', 2330)
insert into #t values (5, '123456789', '2014-07-21 00:00:00.000', 'r', 1500)
insert into #t values (6, '123456789', '2014-08-15 00:00:00.000', 'r', 750)
insert into #t values (7, '123456789', '2014-09-23 00:00:00.000', 'r', 750)
insert into #t values (8, '123456789', '2015-01-19 00:00:00.000', 'p', 2165)
insert into #t values (9, '123456789', '2015-01-19 00:00:00.000', 'r', 1165) 


2. и есть информация об остатке этого товара (в др.таблице):
create table #ostatki (id int, cod char(9), quantity int)
insert into #ostatki values (1, '123456789', 1000)


в первом приближении получается такая конструкция:
select cod, dt, 
  case when op = 'p' then quantity end as prihod, 
  case when op = 'r' then quantity end as rashod,
  0 as ostatok
 from #t
union all
select cod, getdate(), 0 as prihod, 0 as rashod, quantity as ostatok from #ostatki


в конечном итоге нужно получить следующее:
| cod       | dt                      | partia | prihod | rashod | ostatok |
| 123456789 | 2014-06-18 00:00:00.000 | 3      |        | 1330   | 0          |
| 123456789 | 2014-06-27 00:00:00.000 | 2      | 4330   |        | 7330    |
| 123456789 | 2014-07-09 00:00:00.000 | 2      |        | 2000   | 5330    |
| 123456789 | 2014-07-17 00:00:00.000 | 2      |        | 2330   | 3000    |
| 123456789 | 2014-07-21 00:00:00.000 | 2      |        | 1500   | 1500    |
| 123456789 | 2014-08-15 00:00:00.000 | 2      |        | 750    | 750     |
| 123456789 | 2014-09-23 00:00:00.000 | 2      |        | 750    | 0       |
| 123456789 | 2015-01-19 00:00:00.000 | 1      | 2165   |        | 2165    |
| 123456789 | 2015-01-19 00:00:00.000 | 1      |        | 1165   | 1000    | --начинаем "раскручивать" с последнего известного остатка 100


От курсоров отказался (т.к., это должна быть view и важна производительность) в пользу CTE, но и тут проблема в условиях отбора и максимум чего удалось добиться:
with cte as (
  select cod, dt, prihod, rashod, ostatok, row_number() over (order by cod, dt desc) as rank
  from ( select cod, dt, 
		   case when op = 'p' then quantity end as prihod, 
		   case when op = 'r' then quantity end as rashod,
		   NULL as ostatok
		 from #t
		union all
		select cod, getdate(), NULL as prihod, NULL as rashod, quantity as ostatok from #ostatki
	) sub
)
select a.cod, a.dt, a.rank, a.prihod, c.rashod, case when (a.prihod is null or a.rashod is null) then b.ostatok end
from cte a
		left join cte b on a.cod = b.cod and a.rank = b.rank + 1
		left join cte c on a.cod = c.cod and a.rank = c.rank - 1
order by a.rank desc


Поделитесь, пожалуйста, рабочими идеями как решить эту задачу?
3 окт 15, 13:50    [18230707]     Ответить | Цитировать Сообщить модератору
 Re: FIFO от последнего остатка  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
Spl@sH, можно рекурсивным CTE и вспомогательной функцией. В таблице t - создать индекс по полям cod, dt, id.
А вообще-то, было бы быстрее, если бы там вместо dt и id было одно поле, уникальное и упорядоченное по дате - его и брать в индекс. В крайнем случае, можно сделать вычисляемое.
use tempdb
go
create table t (id int, cod char(9), dt datetime, op char(1), quantity int)
insert into t values (1, '123456789', '2014-06-18 00:00:00.000', 'r', 1330)
insert into t values (2, '123456789', '2014-06-27 00:00:00.000', 'p', 4330)
insert into t values (3, '123456789', '2014-07-09 00:00:00.000', 'r', 2000)
insert into t values (4, '123456789', '2014-07-17 00:00:00.000', 'r', 2330)
insert into t values (5, '123456789', '2014-07-21 00:00:00.000', 'r', 1500)
insert into t values (6, '123456789', '2014-08-15 00:00:00.000', 'r', 750)
insert into t values (7, '123456789', '2014-09-23 00:00:00.000', 'r', 750)
insert into t values (8, '123456789', '2015-01-19 00:00:00.000', 'p', 2165)
insert into t values (9, '123456789', '2015-01-19 00:00:00.000', 'r', 1165) 
go
create index ix_t on t (cod, dt, id)
go
create table ostatki (id int, cod char(9), quantity int)
insert into ostatki values (1, '123456789', 1000)
go
create function f_t(@cod char(9),@dt datetime,@id int)
returns table as return
      select top 1 id,dt,
        case when op = 'p' then quantity else 0 end as prihod, 
		case when op = 'r' then quantity else 0 end as rashod
      from t b
      where b.cod=@cod
        and b.dt <= @dt
        and (b.dt < @dt or b.id < @id)
      order by dt desc,id desc
go
with cte (cod,id,dt,prihod,rashod,ostatok) as (
  select cod,b.id,b.dt,b.prihod,b.rashod,a.quantity
  from ostatki a
    outer apply f_t(a.cod,'99991231',0) b
  union all
  select a.cod,b.id,b.dt,b.prihod,b.rashod,
    a.ostatok+a.rashod-a.prihod
  from cte a
    cross apply f_t(a.cod,a.dt,a.id) b
)
select *
from cte
order by dt,id
option (maxrecursion 0)

В исходных данных ошибка? У меня в результате начальная цифра остатка получается 3000, а у Вас - 0.
3 окт 15, 14:17    [18230770]     Ответить | Цитировать Сообщить модератору
 Re: FIFO от последнего остатка  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
А вообще-то, в таком виде задача напоминает сумму с накоплением (если только нет усложняющих условий). Для которой есть много отработанных решений. Вот, например, так ещё можно:
select a.cod,b.dt,b.id,
  case when op='p' then b.quantity end as prihod,
  case when op='r' then b.quantity end as rashod,
  a.quantity + isnull(c.sq,0) as ostatok
from #ostatki a
  left join #t b on b.cod=a.cod
  outer apply (
    select
      sum(case op when 'p' then -quantity when 'r' then quantity end) as sq
    from #t c
    where c.cod=b.cod
      and c.dt >= b.dt
      and (c.dt > b.dt or c.id > b.id)
  ) c
order by b.cod,b.dt,b.id
3 окт 15, 14:56    [18230838]     Ответить | Цитировать Сообщить модератору
 Re: FIFO от последнего остатка  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
https://www.sql.ru/forum/1018035/zadachka-pro-yabloki-ili-sliyanie-2h-tablic
3 окт 15, 14:58    [18230846]     Ответить | Цитировать Сообщить модератору
 Re: FIFO от последнего остатка  [new]
prihod,
Guest
Serg_77m
В исходных данных ошибка? У меня в результате начальная цифра остатка получается 3000, а у Вас - 0.
+1
у меня тоже

;with cte as (
	select
		 row_number()over(order by dt desc, case when op = 'r' then 0 else 1 end) as rn
		,cod
		,dt
		,case when op = 'p' then t.quantity end as prihod 
		,case when op = 'r' then t.quantity end as rashod
		,case when row_number()over(order by dt desc, case when op = 'r' then 0 else 1 end)=1 then a.quantity else 0 end as ostatok
	 from #t t
	 cross apply  (select quantity from #ostatki) a
	 )

select 
	cod 
	,dt
	,prihod
	,rashod
	,ostatok + isnull(a.S,0) as ostatok
	
from cte t1
outer apply (select sum(isnull(ostatok,0)-isnull(prihod,0)+isnull(rashod,0)) as S from cte t2 where t1.rn>t2.rn) a

order by rn desc

cod       dt                      prihod      rashod      ostatok
--------- ----------------------- ----------- ----------- -----------
123456789 2014-06-18 00:00:00.000 NULL        1330        3000
123456789 2014-06-27 00:00:00.000 4330        NULL        7330
123456789 2014-07-09 00:00:00.000 NULL        2000        5330
123456789 2014-07-17 00:00:00.000 NULL        2330        3000
123456789 2014-07-21 00:00:00.000 NULL        1500        1500
123456789 2014-08-15 00:00:00.000 NULL        750         750
123456789 2014-09-23 00:00:00.000 NULL        750         0
123456789 2015-01-19 00:00:00.000 2165        NULL        2165
123456789 2015-01-19 00:00:00.000 NULL        1165        1000

(9 row(s) affected)
3 окт 15, 15:07    [18230862]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить