sql - Get rid of duplicate record and calculated field

Question

The post is linked to the following Sum and Substract column in same table

I’ve the following table "TableOfDelivery" in access 2016

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];

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 would very appreciate your help.

I précised the calculation rules:

Answer 1

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 |
+----------------+---------------+---------------+-----+

Yields the following result:

+---------------+---------------+---------+
|      ref      | delivery week | qtydiff |
+---------------+---------------+---------+
| DTR0000182433 | 2018-31       |       1 |
| DTR0000182433 | 2018-33       |     -37 |
| DTR0000182433 | 2018-35       |     -50 |
| DTR0000182433 | 2018-39       |    -100 |
| DTR0000182433 | 2018-43       |     -13 |
+---------------+---------------+---------+

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 |
+----------------+---------------+---------------+-----+

Yields the following result:

+---------------+---------------+---------+
|      ref      | delivery week | qtydiff |
+---------------+---------------+---------+
| DTR0000182433 | 2018-31       |       1 |
| DTR0000182433 | 2018-33       |     -37 |
| DTR0000182433 | 2018-35       |      50 |
| DTR0000182433 | 2018-39       |     100 |
| DTR0000182433 | 2018-43       |      13 |
+---------------+---------------+---------+