摆脱重复记录和计算字段 [英] Get rid of duplicate record and calculated field
问题描述
该帖子链接到以下内容 同一表中的求和与求和"列
The post is linked to the following Sum and Substract column in same table
我在Access 2016中具有下表"TableOfDelivery"
I’ve the following table "TableOfDelivery" in access 2016
基于stackflow团队成员的帮助，我制定了一个查询
Based on stackflow team member help, I worked out a query
SELECT t1.ref, t1.[delivery week], (t2.qty-t1.qty) AS QtyDiff, iif(t1.[delivery week] <> t1.[delivery week],t1.qty *-1, t1.qty) AS Diff
FROM TableOfDelivery AS t1 LEFT JOIN TableOfDelivery AS t2 ON (t1.[delivery week] = t2.[delivery week]
AND (t1.[reporting week] <> t2.[reporting week])) AND (t1.ref = t2.ref)
GROUP BY t1.[reporting week], t1.ref, t1.[delivery week], (t2.qty-t1.qty), t1.qty
ORDER BY t1.[reporting week];
这接近我的最终结果，但我希望得到以下结果:
仅3列(请参见粗框)
摆脱重复的参考/送货组合(请参见交叉线)
This is close to my final outcome but I would like to have this result:
Only 3 columns (see bold box)
Get rid off the duplicate ref/delivery combination (see crossed lines)
非常感谢您的帮助.
我确定了计算规则:
I précised the calculation rules:
推荐答案
编辑
使用您现在在问题中提供的其他信息，我建议使用以下SQL:
EDIT
With the additional information you have now provided in your question, I would suggest the following SQL:
select
t0.ref,
t0.[delivery week],
nz(t3.q1,0) - nz(t6.q2,0) as QtyDiff
from
(
tableofdelivery t0
left join
(
select
t1.ref, t1.[delivery week], sum(t1.qty) as q1
from
tableofdelivery t1
where
t1.[reporting week] =
(
select max(t2.[reporting week])
from tableofdelivery t2
where t2.ref = t1.ref
)
group by
t1.ref, t1.[delivery week]
) t3 on t0.ref = t3.ref and t0.[delivery week] = t3.[delivery week]
) left join
(
select
t4.ref, t4.[delivery week], sum(t4.qty) as q2
from
tableofdelivery t4
where
t4.[reporting week] <
(
select max(t5.[reporting week])
from tableofdelivery t5
where t5.ref = t4.ref
)
group by
t4.ref, t4.[delivery week]
) t6 on t0.ref = t6.ref and t0.[delivery week] = t6.[delivery week]
group by
t0.ref, t0.[delivery week], nz(t3.q1,0) - nz(t6.q2,0)
原始答案
假设我已经正确理解了您想要获得的结果，我建议使用以下代码:
Original Answer
Assuming I've correctly understood the result that you are looking to obtain, I would suggest the following code:
select
t0.ref,
t0.[delivery week],
nz(t2.qty, 0) - t0.qty as qtydiff
from
(
tableofdelivery t0 inner join
(
select t.ref, t.[delivery week] as dw, min(t.[reporting week]) as rw
from tableofdelivery t
group by t.ref, t.[delivery week]
) t1 on
t0.ref = t1.ref and
t0.[delivery week] = t1.dw and
t0.[reporting week] = t1.rw
)
left join tableofdelivery t2 on
t1.ref = t2.ref and
t1.dw = t2.[delivery week] and
t1.rw <> t2.[reporting week]
order by
t0.ref,
t0.[delivery week]
对于您提供的示例数据，
Which, for your supplied sample data:
+----------------+---------------+---------------+-----+
| reporting week | ref | delivery week | qty |
+----------------+---------------+---------------+-----+
| 2018-37 | DTR0000182433 | 2018-31 | 19 |
| 2018-41 | DTR0000182433 | 2018-31 | 20 |
| 2018-37 | DTR0000182433 | 2018-33 | 50 |
| 2018-41 | DTR0000182433 | 2018-33 | 13 |
| 2018-37 | DTR0000182433 | 2018-35 | 50 |
| 2018-37 | DTR0000182433 | 2018-39 | 100 |
| 2018-41 | DTR0000182433 | 2018-43 | 13 |
+----------------+---------------+---------------+-----+
产生以下结果:
+---------------+---------------+---------+
| ref | delivery week | qtydiff |
+---------------+---------------+---------+
| DTR0000182433 | 2018-31 | 1 |
| DTR0000182433 | 2018-33 | -37 |
| DTR0000182433 | 2018-35 | -50 |
| DTR0000182433 | 2018-39 | -100 |
| DTR0000182433 | 2018-43 | -13 |
+---------------+---------------+---------+
在这里，最里面的子查询首先为每个delivery week和ref组合获得最早的reporting week记录.然后，对于给定的delivery week和ref组合，从与其他(非最小)记录关联的qty中减去与此最小记录关联的qty.
Here, the innermost subquery first obtains the earliest reporting week record for each delivery week and ref combination. The qty associated with this minimum record is then subtracted from the qty associated with the other (non-minimum) records for the given delivery week and ref combination.
或者，根据您的后续注释反转计算，您可以尝试以下操作:
Alternatively, reversing the calculation based on your subsequent comments, you could try the following:
select
t0.ref,
t0.[delivery week],
t0.qty - nz(t2.qty, 0) as qtydiff
from
(
tableofdelivery t0 inner join
(
select t.ref, t.[delivery week] as dw, max(t.[reporting week]) as rw
from tableofdelivery t
group by t.ref, t.[delivery week]
) t1 on
t0.ref = t1.ref and
t0.[delivery week] = t1.dw and
t0.[reporting week] = t1.rw
)
left join tableofdelivery t2 on
t1.ref = t2.ref and
t1.dw = t2.[delivery week] and
t1.rw > t2.[reporting week]
order by
t0.ref,
t0.[delivery week]
对于您提供的示例数据，
Which, for your supplied sample data:
+----------------+---------------+---------------+-----+
| reporting week | ref | delivery week | qty |
+----------------+---------------+---------------+-----+
| 2018-37 | DTR0000182433 | 2018-31 | 19 |
| 2018-41 | DTR0000182433 | 2018-31 | 20 |
| 2018-37 | DTR0000182433 | 2018-33 | 50 |
| 2018-41 | DTR0000182433 | 2018-33 | 13 |
| 2018-37 | DTR0000182433 | 2018-35 | 50 |
| 2018-37 | DTR0000182433 | 2018-39 | 100 |
| 2018-41 | DTR0000182433 | 2018-43 | 13 |
+----------------+---------------+---------------+-----+
产生以下结果:
+---------------+---------------+---------+
| ref | delivery week | qtydiff |
+---------------+---------------+---------+
| DTR0000182433 | 2018-31 | 1 |
| DTR0000182433 | 2018-33 | -37 |
| DTR0000182433 | 2018-35 | 50 |
| DTR0000182433 | 2018-39 | 100 |
| DTR0000182433 | 2018-43 | 13 |
+---------------+---------------+---------+
这篇关于摆脱重复记录和计算字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
