ReSQL.ru
     
powered by simpleCommunicator - 2.0.39     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / FIFO от последнего остатка
5 сообщений из 5, страница 1 из 1
FIFO от последнего остатка
    #39067596
Spl@sH
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Spl@sH
Гость
Ув.Эксперты, поиск по форуму, конечно, дал некоторую пищу для размышления, но полного понимания нет и я прошу помочь советом.

Задача такая: нужна реализация партиционного учета по модели FIFO для таблицы приходов/расходов.
т.е., нужно сопоставить операции прихода/расхода (в обратном порядке), разбить их по партиям и посчитать остатки после каждой операции.
Akinaпрямой связи прихода с расходом нет - то есть один приход может быть поделен на несколько расходов, причём два из них поделены между ним и другим приходом
и все это на Microsoft SQL Server 2008 R2 (SP2) - 10.50.4321.0 (Build 7601: Service Pack 1)

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
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. и есть информация об остатке этого товара (в др.таблице):
Код: sql
1.
2.
create table #ostatki (id int, cod char(9), quantity int)
insert into #ostatki values (1, '123456789', 1000)



в первом приближении получается такая конструкция:
Код: sql
1.
2.
3.
4.
5.
6.
7.
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



в конечном итоге нужно получить следующее:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
| 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, но и тут проблема в условиях отбора и максимум чего удалось добиться:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
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



Поделитесь, пожалуйста, рабочими идеями как решить эту задачу?
...
Рейтинг: 0 / 0
03.10.2015, 13:50
| Ответить | Цитировать | Написать  
FIFO от последнего остатка
    #39067608
Serg_77m
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Serg_77m
Участник
Spl@sH, можно рекурсивным CTE и вспомогательной функцией. В таблице t - создать индекс по полям cod, dt, id.
А вообще-то, было бы быстрее, если бы там вместо dt и id было одно поле, уникальное и упорядоченное по дате - его и брать в индекс. В крайнем случае, можно сделать вычисляемое.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
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.
...
Рейтинг: 0 / 0
03.10.2015, 14:17
| Ответить | Цитировать | Написать  
FIFO от последнего остатка
    #39067618
Serg_77m
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Serg_77m
Участник
А вообще-то, в таком виде задача напоминает сумму с накоплением (если только нет усложняющих условий). Для которой есть много отработанных решений. Вот, например, так ещё можно:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
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
...
Рейтинг: 0 / 0
03.10.2015, 14:56
| Ответить | Цитировать | Написать  
FIFO от последнего остатка
    #39067619
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
http://www.sql.ru/forum/1018035/zadachka-pro-yabloki-ili-sliyanie-2h-tablic
...
Рейтинг: 0 / 0
03.10.2015, 14:58
| Ответить | Цитировать | Написать  
FIFO от последнего остатка
    #39067621
prihod,
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
prihod,
Гость
Serg_77mВ исходных данных ошибка? У меня в результате начальная цифра остатка получается 3000, а у Вас - 0.+1
у меня тоже

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
;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


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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)
...
Рейтинг: 0 / 0
03.10.2015, 15:07
| Ответить | Цитировать | Написать  
5 сообщений из 5, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / FIFO от последнего остатка
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&gotonew=1&tid=1696818]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 23ms
get topic data:
 10ms
get first new msg:
 7ms
get forum data:
 2ms
get page messages:
 46ms
get tp. blocked users:
 2ms
others: 278ms
total:  396ms
созд. / загр.: 396ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]