我想计算一下公司在某些特定日期的股票。由于我们没有股票表，我需要根据股票走势来计算。ENTRAN=增加，SALEN=减少，从该日期之前的最后一个库存开始。
我必须检查的日期是销售前股票走势的最后几天。
我想把我的销售表和库存表连接起来。
股票走势表(MOVSTOCKS(如下所示：
SKU entran salen codalm fecdoc tipdoc
ART1 50 10 2 01-03-2021 IN
ART1 100 2 03-03-2021 AC
ART1 30 2 05-03-2021 AV
ART2 100 50 2 01-03-2021 IN
ART2 50 2 02-03-2021 AV
ART2 30 50 2 03-03-2021 IN
ART2 20 2 03-03-2021 AV
ART2 50 2 04-03-2021 AC
ART2 30 2 05-03-2021 AV
每个IN都是一个库存。当有库存时，我必须取ENTRAN值，并从中不断计算，添加或减少单位。
AC是采购，AV是销售。
因此，销售表(LINEALBA(应该显示这些数据以及其他列：
Date SKU QTY Cumulative
05-03-2021 ART1 30 150
02-03-2021 ART2 50 100
03-03-2021 ART2 20 30
05-03-2021 ART2 30 60
每一行都是一个销售(AV(，cumulative列查看该销售之前的最后一个库存01-03-2021或03-03-2021，它添加和减去数量，并显示销售前一天的计算库存。
这是我的查询(不起作用(：
select *
from (select m*,
LastInventory + sum(m.entran - m.salen)
over (partition by m.codart order by m.fecdoc) as cumulative,
max(m.fecdoc) over (partition by m.codart) as max_fecdoc,
m.fecdoc
from MovStocks m
left join LINEALBA l on l.codart = m.codart
left join (select m2.codart as SKU,
m2.entran as LastInventory,
max(m2.fecdoc) over (partition by m2.codart, m2.tipdoc) as LastDateinventory,
m2.fecdoc
from MovStocks m2
where m2.codalm in (2)
and m2.tipdoc = 'IN') m2
on m2.CODART = m.codart
where m.codalm in (2)
and m2.fecdoc = LastDateinventory
and m.fecdoc < v.fecha
and m.fecdoc > LastDateinventory
and LastDateinventory < v.fecha
and m.tipdoc <> 'IN') m
where max_fecdoc = m.fecdoc
我的想法是创建一个子查询，或者CTE，Idk，它可以查看销售前的最后一个库存是什么时候，以及销售前的最近一次移动是什么时候。然后以库存为基础计算库存(就像它是0一样(。
出于某种原因，我的查询没有正确计算此累积值。我不知道是过滤器有问题，还是我的子查询不正确。。。
任何帮助都将不胜感激。非常感谢。
分而治之！
-- Add sequence number with SKU.
-- Rows may be uniquely identify by SKU & seq number.
With Qry1 As (
SELECT SKU,
entran,
salen,
codalm,
fecdoc,
tipdoc,
ROW_NUMBER() OVER(Partition By SKU Order By fecdoc) As Seq
FROM MOVSTOCKS
),
-- Get seq # of most recent inventory for each SKU.
Qry2 As (
SELECT qry11.sku,
qry11.seq,
max(qry12.seq) as prev_inv_seq
FROM Qry1 qry11
inner join Qry1 qry12
on qry11.sku = qry12.sku
and qry11.fecdoc >= qry12.fecdoc
where qry12.tipdoc = 'IN'
group by qry11.sku,
qry11.seq
),
-- Get quantity of most recent inventory for each SKU.
Qry3 As (
SELECT qry2.sku,
qry2.seq,
qry1.entran as prev_inv_entran
FROM Qry2
inner join Qry1
on qry2.sku = qry1.sku
and qry2.prev_inv_seq = qry1.seq
),
-- Get sum of purchase quantities since most recent inventory for each SKU.
Qry4 As (
SELECT qry2.sku,
qry2.seq,
sum(qry1.entran) as sum_entran
FROM Qry2
inner join Qry1 qry1
on qry2.sku = qry1.sku
where qry1.seq > qry2.prev_inv_seq
and qry1.seq < qry2.seq
AND qry1.tipdoc = 'AC'
group by qry2.sku,
qry2.seq
),
-- Get sum of sales quantities since most recent inventory for each SKU.
Qry5 As (
SELECT qry2.sku,
qry2.seq,
sum(qry1.salen) as sum_salen
FROM Qry2
inner join Qry1 qry1
on qry2.sku = qry1.sku
where qry1.seq > qry2.prev_inv_seq
and qry1.seq < qry2.seq
AND qry1.tipdoc = 'AV'
group by qry2.sku,
qry2.seq
)
-- Pull it all together...
SELECT qry1.fecdoc As Date,
qry1.SKU,
qry1.salen As Quantity,
qry3.prev_inv_entran + IsNull(qry4.sum_entran, 0) - IsNull(qry5.sum_salen, 0) as Cumulative
FROM qry1
left JOIN Qry3
on qry3.sku = qry1.sku
and qry3.seq = qry1.seq
left JOIN Qry4
on qry4.sku = qry1.sku
and qry4.seq = qry1.seq
left JOIN Qry5
on qry5.sku = qry1.sku
and qry5.seq = qry1.seq
WHERE qry1.tipdoc = 'AV'