Hello,

For the sake of the example I will simplify like this: I have one table (ArticleTransactions) .

- ArticleId
- TransactionDate
- Operation (In/Out)
- Quantity

Inputs increase the Article Stock and outputs decrease it.

and, for a date interval I need a list like:

| ArticleId | Stock before the interval | Inputs during the interval |Outputs during the interval | Stock at the end of the interval |

Is that possible using SELECT queries?

Thank you,

Daniel

Not much to go on, and no sample data.

I assumed Quantity was always a positive value, i.e., that Out qtys had to be explicitly set to negative to subtract. If that's not true, adjust the code to reflect that.

```
DECLARE @interval_start datetime
DECLARE @interval_end datetime
SELECT AT.ArticleId,
MAX(AT_Before.BeforeQuantity) AS BeforeQuantity,
SUM(CASE WHEN AT.Operation = 'In' THEN AT.Quantity ELSE 0 END) AS Inputs,
SUM(CASE WHEN AT.Operation = 'Output' THEN -AT.Quantity ELSE 0 END) AS Outputs,
MAX(AT_Before.BeforeQuantity) +
SUM(CASE WHEN AT.Operation = 'In' THEN AT.Quantity ELSE 0 END) +
SUM(CASE WHEN AT.Operation = 'Output' THEN -AT.Quantity ELSE 0 END) AS EndingQuantity
FROM dbo.ArticleTransactions AT
LEFT OUTER JOIN (
SELECT ArticleId, SUM(Quantity * CASE WHEN Operation = 'Out' THEN -1 ELSE 1 END) AS BeforeQuantity
FROM dbo.ArticleTransactions
WHERE TransactionDate < @interval_start
GROUP BY ArticleId
) AS AT_Before ON AT_Before.ArticleId = AT.ArticleId
WHERE AT.TransactionDate >= @interval_start AND AT.TransactionDate <= @interval_end
```

1 Like

With the help from Scott and some adding from me, the running result is this:

DECLARE @interval_start date

DECLARE @interval_end date

SELECT AT.ArticleId,

Coalesce(MAX(AT_Before.BeforeQuantity),0) AS BeforeQuantity,

Coalesce(SUM(CASE WHEN AT.Operation = 'In' THEN AT.Quantity ELSE 0 END),0) AS Inputs,

Coalesce(SUM(CASE WHEN AT.Operation = 'Out' THEN AT.Quantity ELSE 0 END),0) AS Outputs,

Coalesce(Coalesce(MAX(AT_Before.BeforeQuantity),0) +

SUM(CASE WHEN AT.Operation = 'In' THEN AT.Quantity ELSE 0 END) -

SUM(CASE WHEN AT.Operation = 'Out' THEN AT.Quantity ELSE 0 END),0) AS EndingQuantity

FROM dbo.ArticleTransactions AT

LEFT OUTER JOIN (

SELECT ArticleId, SUM(Quantity * CASE WHEN Operation = 'Out' THEN -1 ELSE 1 END) AS BeforeQuantity

FROM dbo.ArticleTransactions

WHERE TransactionDate < @interval_start

GROUP BY ArticleId

) AS AT_Before ON AT_Before.ArticleId = AT.ArticleId

WHERE AT.TransactionDate >= @interval_start AND AT.TransactionDate <= @interval_end

Group By AT.ArticleId

Union Select ATI.ArticleId,SUM(ATI.Quantity * CASE WHEN ATI.Operation = 'In' THEN 1 ELSE -1 END) AS BeforeQuantity,

Inputs=0, Outputs=0,

SUM(ATI.Quantity * CASE WHEN ATI.Operation = 'In' THEN 1 ELSE -1 END) AS EndingQuantity

FROM dbo.ArticleTransactions ATI

Left Join ( Select Distinct ArticleId From ArticleTransactions Where TransactionDate Between @interval_start And @interval_end ) As TrzPer

On ATI.ArticleId=TrzPer.ArticleId

WHERE ATI.TransactionDate < @interval_start And TrzPer.ArticleId IS NULL

Group By ATI.ArticleId

Having SUM(ATI.Quantity * CASE WHEN ATI.Operation = 'In' THEN 1 ELSE -1 END) !=0

Thank you!

Sorry for not getting back to you sooner, I'm **extraordinarily** busy at work (and at home).

As to having to use

MAX(AT_Before.BeforeQuantity)

It's because the query is GROUPing, so an unaggregated column can't appear unless it's also in the GROUP BY. Now, we know the BeforeQuantity is a single value, **always** the same value, and presumably SQL could in theory figure that out, but it has its rules, and rules are rules (to insure SQL doesn't return invalid results).

To avoid the overhead of GROUPing by that value too, we can just use the MAX() of it, which satisfies SQL's requirement to have it either be: (1) part of the GROUP BY or (2) an aggregate value.

As an example, something like this is not valid:

SELECT state, city, sum(population)

from dbo.table_name

group by state

Because if there are multiple cities, which one should SQL list? And since the sum is for the entire state, that makes the result look false because it implies that the city has that population.

It's for those reasons that SQL has those rules/requirements about GROUPing.

1 Like