Pivoting tables

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