Special SQL - SA12

[former_SQLA_member1694869]

I have a table that logs stock movements. I want to bring out which incoming delivery price that consumptions had. In this example there three two receipts and three consumptions. Type 1 is the receipt and type 2 are consumption. I need to figure out via a SQL, which is the receipt price the consumption had. Is the consumption from two or more receipts, it will be the average price from those that apply. That which binds together a receipt with a consumption is the batch number. Purchase order and batch number is always equal.

CREATE TABLE stock_log (        
                material nvarchar(20),
                row_type smallint,
                balance_participate numeric(16,6),
                stock_balance numeric(16,6),
                batch_number nvarchar(10),
                order_number nvarchar(10),
                sl_price numeric(16,6),
                sl_date datetime,
                sl_id int not null default autoincrement, 
                constraint pk_stock_log primary key( sl_id asc)
) IN SYSTEM;

insert into stock_log (material, row_type, balance_participate, stock_balance, batch_number, order_number, sl_price, sl_date) values ('test1', 1, 80, 80, 110288, 110288, 1480.40, '2016-11-15 04:18:31.123');
insert into stock_log (material, row_type, balance_participate, stock_balance, batch_number, order_number, sl_price, sl_date) values ('test1', 1, 76, 156, 110288, 110288, 1420.32, '2016-12-08 13:28:33.456');
insert into stock_log (material, row_type, balance_participate, stock_balance, batch_number, order_number, sl_price, sl_date) values ('test1', 2, -24, 132, 110288, 337333, Null, '2016-12-22 19:43:10.231');
insert into stock_log (material, row_type, balance_participate, stock_balance, batch_number, order_number, sl_price, sl_date) values ('test1', 2, -90, 42, 110288, 337334, Null, '2016-12-26 08:55:51.121');
insert into stock_log (material, row_type, balance_participate, stock_balance, batch_number, order_number, sl_price, sl_date) values ('test1', 2, -30, 12, 110288, 337335, Null, '2016-12-27 23:16:29.441');
insert into stock_log (material, row_type, balance_participate, stock_balance, batch_number, order_number, sl_price, sl_date) values ('test1', 1, 20, 32, 110288, 110288, 1427.52, '2016-12-28 11:23:19.981');


select material, row_type, balance_participate, stock_balance, batch_number, order_number, sl_price, date(sl_date) as sl_date from stock_log order by sl_date;

material--row_type--balance_participate--stock_balance--batch_number---order_number---sl_price---sl_date
test1_____1_________80___________________80_____________110288_________110288_________1480.40____2016-11-15
test1_____1_________76___________________156____________110288_________110288_________1420.32____2016-12-08
test1_____2________-24___________________132____________110288_________337333_________NULL_______2016-12-22
test1_____2________-90___________________42_____________110288_________337334_________NULL_______2016-12-26
test1_____2________-30___________________12_____________110288_________337335_________NULL_______2016-12-27
test1_____1_________20___________________32_____________110288_________110288_________1427.52____2016-12-28

//The result I want to make is this, that is, it calculates sl_price on each consumed row.

material--row_type--balance_participate--stock_balance--batch_number---order_number---sl_price---sl_date
test1_____1_________80___________________80_____________110288_________110288_________1480.40____2016-11-15
test1_____1_________76___________________156____________110288_________110288_________1420.32____2016-12-08
test1_____2________-24___________________132____________110288_________337333_________1480.40____2016-12-22
test1_____2________-90___________________42_____________110288_________337334_________1457.703___2016-12-26 
test1_____2________-30___________________12_____________110288_________337335_________1420.32____2016-12-27
test1_____1_________20___________________32_____________110288_________110288_________1427.52____2016-12-28

//Second cosnsumption (56 * 1480.40 + 34 * 1420.32) / 90 = 1457.703

[VolkerBarth]

Purchase order and batch number is always equal.

Is "Purchase order" = order_number? Because in your test data, batch_number and order_number seem only equal for those entries with row_type 1.

For further understanding:

Is the consumption from two or more receipts, it will be the average price from those that apply.

So from your 2nd sample consumption, I conclude that you just build the average of the according delivery prices, independent of the number of materials taken from those? (I had expected you would count 56 pieces from the first delivery and 34 from the second, so the weighted average price per piece would be (56 * 1480.40 + 34 * 1420.32) / 90 = 1.457,703?

[former_SQLA_member1694869]

Yes, order_number is Purchase order when row_type is 1. If row_type = 2 the order_number can be manufacture order or stock order etc.

You are absolutely right in terms of the average of the prices. In my example, I have done wrong.

[former_SQLA_member1694869]

order_number is a field that we do not need to take into account. In this case,it's just a field that follows in the SQL.

I'm stuck. I know it can be solved, but the question is how? Do you have any solution?

[former_SQLA_member1694869]

Someone who can help me with this SQL?

[former_SQLA_member1694869]

Someone who can help me?

[former_SQLA_member1694869]

I may have been unclear, but this calculation will be on each unique material. Several different materials can occur, but it should always be calculated per material. Have you come up with something brilliant? I haven't gotten anywhere.

[former_SQLA_member1694869]

Help me someone. A start so I have something to begin with...

[VolkerBarth]

I guess I have something to share tomorrow.

[VolkerBarth]

ANSWER PART TWO:

Based on the query from ANSWER PART ONE, I will now join the consumptions (i.e. rows with row_type = 2) with the according receipts (i.e. those with a smaller sl_id). As stated, for your real situation, you might have to adapt that for fitting materials/batch_numbers etc.

As the query so far uses a WHERE clause to specify the consumption up to a particular sl_id, I will now use the CROSS APPLY operator to connect those, i.e. to join the consumptions ("cons") with those receipts ("rcpt") and their remaining pieces up to that according consumption:

-- use "cons_" prefix for columns belonging to the consumption, default names for the receipt
select cons.sl_id as cons_sl_id, cons.row_type as cons_row_type, -cons.balance_participate as cons_consumption,
   rcpt.*
from stock_log cons 
cross apply
  (select sl_id, row_type, balance_participate, stock_balance, sl_price, sl_date,
      -- for receipt: How much is left?
      if row_type = 1
         then greater(
                 lesser(
                    isnull(
                       stock_balance +
                          sum (if row_type = 1 then 0 else balance_participate end if)
                          over (partition by material
                                order by sl_date
                                rows between 1 following and unbounded following),
                       balance_participate),
                   balance_participate),
               0) end if as remaining_rcpt_balance,
      if row_type = 1 then balance_participate - remaining_rcpt_balance end if as consumed_rcpt
   from stock_log SL
   where SL.sl_id <= cons.sl_id) rcpt
where cons_row_type = 2
order by cons_sl_id, rcpt.sl_id

returns:

cons_sl_id;cons_row_type;cons_consumption;sl_id;row_type;balance_participate;stock_balance;sl_price;sl_date;remaining_rcpt_balance;consumed_rcpt 3;2;24.000000;1;1;80.000000;80.000000;1480.400000;2016-11-15 04:18:31.123;56.000000;24.000000 3;2;24.000000;2;1;76.000000;156.000000;1420.320000;2016-12-08 13:28:33.456;76.000000;0.000000 3;2;24.000000;3;2;-24.000000;132.000000;;2016-12-22 19:43:10.231;; 4;2;90.000000;1;1;80.000000;80.000000;1480.400000;2016-11-15 04:18:31.123;0.000000;80.000000 4;2;90.000000;2;1;76.000000;156.000000;1420.320000;2016-12-08 13:28:33.456;42.000000;34.000000 4;2;90.000000;3;2;-24.000000;132.000000;;2016-12-22 19:43:10.231;; 4;2;90.000000;4;2;-90.000000;42.000000;;2016-12-26 08:55:51.121;; 5;2;30.000000;1;1;80.000000;80.000000;1480.400000;2016-11-15 04:18:31.123;0.000000;80.000000 5;2;30.000000;2;1;76.000000;156.000000;1420.320000;2016-12-08 13:28:33.456;12.000000;64.000000 5;2;30.000000;3;2;-24.000000;132.000000;;2016-12-22 19:43:10.231;; 5;2;30.000000;4;2;-90.000000;42.000000;;2016-12-26 08:55:51.121;; 5;2;30.000000;5;2;-30.000000;12.000000;;2016-12-27 23:16:29.441;;

That means consumption 4 has left 0 pieces of receipt 1 and 42 of receipt 2.

But apparently that query lists how many pieces are left "up to the current consumption" but does not still tell how many pieces have been consumed "only by" that consumption.

That will be solved by putting that query into a derived table and additionally

• get the value for "consumed_rcpt" of the according receipt for the previous consumption (handling no previous rows accordningly)
• calculate the difference between the previous and the total consumption "so far" (and thereby, calculate the current consumption, named "consumed_rcpt_per_cons") and
• calculate the total price of the consumed pieces per receipt per consumption ("consumed_rcpt_price"):

select *, 
   isnull(first_value(consumed_rcpt)
          over (partition by sl_id
          order by cons_sl_id
          rows between 1 preceding and 1 preceding), 0) as prev_consumed_rcpt,
   consumed_rcpt - prev_consumed_rcpt as consumed_rcpt_per_cons,
   consumed_rcpt_per_cons * sl_price as consumed_rcpt_price
from
  (select cons.sl_id as cons_sl_id, cons.row_type as cons_row_type, -cons.balance_participate as cons_consumption,
      rcpt.*
   from stock_log cons 
   cross apply
     (select sl_id, row_type, balance_participate, stock_balance, sl_price, sl_date,
         -- for receipt: How much is left?
         if row_type = 1
            then greater(
                    lesser(
                       isnull(
                          stock_balance +
                             sum (if row_type = 1 then 0 else balance_participate end if)
                             over (partition by material
                                   order by sl_date
                                   rows between 1 following and unbounded following),
                          balance_participate),
                      balance_participate),
                  0) end if as remaining_rcpt_balance,
         if row_type = 1 then balance_participate - remaining_rcpt_balance end if as consumed_rcpt
      from stock_log SL
      where SL.sl_id <= cons.sl_id) rcpt
   where cons_row_type = 2
   order by cons_sl_id, rcpt.sl_id) DT
where DT.row_type = 1
order by cons_sl_id, DT.sl_id

This returns:

cons_sl_id;cons_row_type;cons_consumption;sl_id;row_type;balance_participate;stock_balance;sl_price;sl_date;remaining_rcpt_balance;consumed_rcpt;prev_consumed_rcpt;consumed_rcpt_per_cons;consumed_rcpt_price 3;2;24.000000;1;1;80.000000;80.000000;1480.400000;2016-11-15 04:18:31.123;56.000000;24.000000;0.000000;24.000000;35529.600000 3;2;24.000000;2;1;76.000000;156.000000;1420.320000;2016-12-08 13:28:33.456;76.000000;0.000000;0.000000;0.000000;0.000000 4;2;90.000000;1;1;80.000000;80.000000;1480.400000;2016-11-15 04:18:31.123;0.000000;80.000000;24.000000;56.000000;82902.400000 4;2;90.000000;2;1;76.000000;156.000000;1420.320000;2016-12-08 13:28:33.456;42.000000;34.000000;0.000000;34.000000;48290.880000 5;2;30.000000;1;1;80.000000;80.000000;1480.400000;2016-11-15 04:18:31.123;0.000000;80.000000;80.000000;0.000000;0.000000 5;2;30.000000;2;1;76.000000;156.000000;1420.320000;2016-12-08 13:28:33.456;12.000000;64.000000;34.000000;30.000000;42609.600000

This tells that for consumption 4, 56 pieces of receipt 1 for a total of 82,902 and 34 of receipt 2 for a total of 48,290 have been consumed.

---

Finally, we want to group that result set by consumptions (i.e. by "cons_sl_id" and "cons_consumption") to calculate and the total and average price per pieces. So we again turn the previous query into a derived one (here DT2):

select cons_sl_id, sum(consumed_rcpt_price) as total_consumed_price, total_consumed_price / cons_consumption as cons_price
from
  (select *, 
      isnull(first_value(consumed_rcpt)
             over (partition by sl_id
                   order by cons_sl_id
                   rows between 1 preceding and 1 preceding), 0) as prev_consumed_rcpt,
      consumed_rcpt - prev_consumed_rcpt as consumed_rcpt_per_cons,
      consumed_rcpt_per_cons * sl_price as consumed_rcpt_price
   from
     (select cons.sl_id as cons_sl_id, cons.row_type as cons_row_type, -cons.balance_participate as cons_consumption,
         rcpt.*
      from stock_log cons 
      cross apply
        (select sl_id, row_type, balance_participate, stock_balance, sl_price, sl_date,
            -- for receipt: How much is left?
            if row_type = 1
               then greater(
                       lesser(
                          isnull(
                             stock_balance +
                                sum (if row_type = 1 then 0 else balance_participate end if)
                                over (partition by material
                                      order by sl_date
                                      rows between 1 following and unbounded following),
                             balance_participate),
                         balance_participate),
                     0) end if as remaining_rcpt_balance,
            if row_type = 1 then balance_participate - remaining_rcpt_balance end if as consumed_rcpt
         from stock_log SL
         where SL.sl_id <= cons.sl_id) rcpt
      where cons_row_type = 2
      order by cons_sl_id, rcpt.sl_id) DT
   where DT.row_type = 1
   order by cons_sl_id, DT.sl_id) DT2
group by cons_sl_id, cons_consumption
order by cons_sl_id;

And here we are:

cons_sl_id;total_consumed_price;cons_price
3;35529.600000;1480.400000
4;131193.280000;1457.703111
5;42609.600000;1420.320000

As desired, for consumption 4, it returns the average price of 1,457,703.

(I'd love to show an image of the result set, but alas, the image upload says no.)

Now you might turn that query into something that is used within a AFTER UPDATE trigger to calculate the desired "sl_price" for the freshly inserted consumption.

[former_SQLA_member1694869]

I'm extremely impressed by your comprehensive answer. Many thanks for that! I've tried it, and it almost works as I expect. However, we must take into account batch number.

What I mean by batch_number is that each receipt (purchase order) gets a unique batch_number. In my example was batch_number same as purchase orders. It becomes so automatic if you not choose to specify a unique batch_number manually. To remember is that a receipt of a purchase order can be part delivered. In these cases, the purchase order gets same batch_number as first receipt .

When consumption happens, it will be against a batch_number. That is, a batch number created through a receipt. Each consumption is bound to a receipt via batch_number. Hope you understand what I mean regarding batch_number.

I want to do this, on each material. The normal is, that I enter a material in the where clause.

Can you help me with batch_number too? And also

[VolkerBarth]

As stated several times, your sample data do not show varying values for batch_number (and material), so I do not really understand their usage. - The above is what I could come up with based on your input.

But it should be easy for you to adapt the query by adding a fitting WHERE clause to limit the link between receipts and consumptions to those with the same batch_number and material. (CAVEAT: If it not that easy for you, that I would suspect that this really complex query is not what you want to adapt/maintain generally, and you should really look for a different approach.)

See, I'm done with my help. The rest should be up to you.

[VolkerBarth]

ANSWER PART ONE:

Here are my attempts with a query based on several WINDOW functions and derived tables. IMHO, it is rather complex, and I can't tell whether it's comprehensible for you.

Preface: Like Volker has already stated, that situation might be better solved with a different table schema and/or an additional helper table that would contain entries for each contribution of one receipt to the according consumption. I.e. you would simply store for the forst receipt (with sl_date 2016-11-15 04:18:31.123) that 24 pieces have been used by consumption 1 and 56 pieces have been used by consumption 2. So I would not usually prefer such a complex solution as presented here...

As stated elsewhere, I still do not fully understand your data model. As the values for "material" and "batch_number" do not vary within your sample data, I have ignored them here - you'll possibly have to add them to some queries/partitions - I simply don't know.

Here's a query that lists how many pieces of certain receipt (ie. row_type = 1) are consumed up to a certain sl_id.

select sl_id, row_type, balance_participate, stock_balance, sl_price, sl_date,
   -- for receipt: How much is left?
   if row_type = 1
      then greater(
              lesser(
                 isnull(
                    stock_balance +
                       sum (if row_type = 1 then 0 else balance_participate end if)
                       over (partition by material
                             order by sl_date
                             rows between 1 following and unbounded following),
                    balance_participate),
                balance_participate),
            0) end if as remaining_rcpt_balance,
   if row_type = 1 then balance_participate - remaining_rcpt_balance end if as consumed_rcpt
from stock_log
where sl_id <= 3
order by sl_id, sl_date

For sl_id <= 1 this lists just the forst receipt (no consumption):

sl_id;row_type;balance_participate;stock_balance;sl_price;sl_date;remaining_rcpt_balance;consumed_rcpt 1;1;80.000000;80.000000;1480.400000;2016-11-15 04:18:31.123;80.000000;0.000000

For sl_id <= 3 this shows that receipt sl_id 1 has been consumed with 24 pieces whereas sl_id 2 is fully preserved:

sl_id;row_type;balance_participate;stock_balance;sl_price;sl_date;remaining_rcpt_balance;consumed_rcpt 1;1;80.000000;80.000000;1480.400000;2016-11-15 04:18:31.123;56.000000;24.000000 2;1;76.000000;156.000000;1420.320000;2016-12-08 13:28:33.456;76.000000;0.000000 3;2;-24.000000;132.000000;;2016-12-22 19:43:10.231;;

The calculation of "remaining_rcpt_balance" needs some explanantion:

• For each receipt, I sum over the "balance_participate" of the following rows and add those (in case they are consumptions) to the current "stock_balance". That's the SUM () OVER() expression. For consumptions, I do nothing.
• If there are no following rows, the ISNULL() handles that by assuring the receipt is fully preserved.
• That "running consumption" needs to consider two further aspects:
• I subtract those consumptions from the "stock_balance", not from the "balance_participate" because clearly, for later receipts, I have to make sure former receipts are primarily consumed. Nevertheless, the "remaining_rcpt_balance" must not be greater than the "balance_participate" itself, so there's a LESSER(...) call to assure that.
• One cannot consume more of a receipt than it has as "balance_participate", so there's a GREATER(..., 0) call that prevents "negative" balances.

---

Next step: Put that into a derived query to just return the receipt rows. (Note, you can't simply use a WHERE clause to filter out those rows in the above query as they are needed for the WINDOW function.

select * from
(
   select sl_id, row_type, balance_participate, stock_balance, sl_price, sl_date,
      -- for receipt: How much is left?
      if row_type = 1
         then greater(
                 lesser(
                    isnull(
                       stock_balance +
                          sum (if row_type = 1 then 0 else balance_participate end if)
                          over (partition by material
                                order by sl_date
                                rows between 1 following and unbounded following),
                       balance_participate),
                   balance_participate),
               0) end if as remaining_rcpt_balance,
      if row_type = 1 then balance_participate - remaining_rcpt_balance end if as consumed_rcpt
   from stock_log
   where sl_id <= 6
   order by sl_id, sl_date) DT1
where DT1.row_type = 1
order by 1;

This lists the consumption of your complete sample data (i.e. up to sl_id 6) and shows that the first receipt is fully consumed, the second with 64 pieces and the last is fully preserved:

sl_id;row_type;balance_participate;stock_balance;sl_price;sl_date;remaining_rcpt_balance;consumed_rcpt 1;1;80.000000;80.000000;1480.400000;2016-11-15 04:18:31.123;0.000000;80.000000 2;1;76.000000;156.000000;1420.320000;2016-12-08 13:28:33.456;12.000000;64.000000 6;1;20.000000;32.000000;1427.520000;2016-12-28 11:23:19.981;20.000000;0.000000

...Cliff-hanger...:)

[former_SQLA_member1694885]

I'm trying to think of a window aggregate to achieve this, but it probably will be very tricky at least.
I think it might help to track the remaining balance_participate value for each receipt, so you can start with the earliest row with remaining_balance_participate > 0 and add more receipts until sum (remaining_balance_participate) >= consumption.balance_participate. in the case of overrun ( the > part of >=), you'll have to calculate the correction.
I'm aware that this is redundant information, but not more redundant than stock_balance.

I'll let this trickle through my mind and be back if and when I can think of more details. Until then, everybody feel invited to comment or evolute on this.

Cheers,
Volker

[former_SQLA_member1694869]

Thank you for your response and your proposed solution. My guess is that you solve my problem with your knowledge. 🙂

When you or anyone have a solution, I need an example of how to do this.

[VolkerBarth]

@Rolle, I have tried some queries with rather complex/tricky OLAP functions but that's really difficult to create - and probably even more difficult to understand afterwards. I'll present some of them (but it will last some days, I think).

Like Volker, I think your table schema is not really well suited for that purpose. Please tell us more about these aspects:

• Is your table schema "a given", or can you modify it (by adding columns and/or more tables)? (Hint: I would suggest to relate the "consumed" materials from the receipts with the according consumption.)

• Or is it more or less just the wanted output of some report?

• What about primary keys? Can we assume the "sl_date" is unique per material? Can a receipt and a consumption of the same material (or batch_number) have the same sl_date? If so, how are they ordered, i.e. will the stock increase always precede the consumption if on the same date?

• Per uniqueness: In your sample, the columns "material" and "batch_number" have always the same value. What is the "unique" value here to connect receipts and consumptions?

And as usual, we don't prefer to do someone's homework, so it would be helpful if you tell about the thoughts and attempts you have tried so far.

[former_SQLA_member1694869]

Thank you for taking the time to look at my problem.

In fact, there is another field, sl_id (autoinrement) who have primary key. Date field is a datetime field. Those are the things I missed when I sent in the SQL.

Everything is logged with date and time.

There may be many consumptions per day on a material. It also may be many receipts per day on a material.

I want to be able to select a material and get the receipts price of the consumption rows.

The table schema is "a given" and can not be changed.

A receipt and a consumption of same material can't have the same sl_datetime because it logs on format 'yyyy-mm-dd hh.mm.ss.000'.

You could create a local temporary table with data from this table. I've been thinking about it too, but I haven't solved it

Alternatively, I have tried through the CTE, create this on different levels, but it has not succeeded.

Batch_number holds together the receipt and consumption.

In my example, I have only taken one material with a bachtnr.

The example shows how it might look on a material in a batch for a limited period. Just as an example.

This is how the table looks correct:

CREATE TABLE stock_log (        
                material nvarchar(20),
                            row_type smallint,
                            balance_participate numeric(16,6),
                            stock_balance numeric(16,6),
                            batch_number nvarchar(10),
                            order_number nvarchar(10),
                            sl_price numeric(16,6),
                            sl_date datetime,
                sl_id not null default autoinrement, 
                primary key( sl_id )
) IN SYSTEM;

insert into stock_log values ('test1', 1, 80, 80, 110288, 110288, 1480.40, '2016-11-15 04:18:31.123');
insert into stock_log values ('test1', 1, 76, 156, 110288, 110288, 1420.32, '2016-12-08 13:28:33.456');
insert into stock_log values ('test1', 2, -24, 132, 110288, 337333, Null, '2016-12-22 19:43:10.231');
insert into stock_log values ('test1', 2, -90, 42, 110288, 337334, Null, '2016-12-26 08:55:51.121');
insert into stock_log values ('test1', 2, -30, 12, 110288, 337335, Null, '2016-12-27 23:16:29.441');
insert into stock_log values ('test1', 1, 20, 32, 110288, 110288, 1427.52, '2016-12-28 11:23:19.981');

[VolkerBarth]

Isn't that the same sample as in the question (except for the date including the time component)?

Besides that, so a receipt can contain different materials? - I still don't get the primary key/unique key situation...

[former_SQLA_member1694869]

The example is the same. Better so, otherwise it gets so messy.

[VolkerBarth]

That sample code does not work. The following line

sl_id not null default autoinrement

misses a data type (and it's "autoincrement"), and the insert statement will require either an explicit column list or the addition of "default" as input value for the added column sl_id.

[former_SQLA_member1694869]

I have updated my original question with a working SQL...

[VolkerBarth]

I'm aware that this is redundant information, but not more redundant than stock_balance.

FWIW, to explain Volker's statement: The contents of "stock_balance" seems to be a simple "running sum", which can easily be calculated with a WINDOW function, such as:

select *,
   sum(balance_participate)
      over (partition by material
            order by sl_date
            rows between unbounded preceding and current row)
      as calculated_stock_balance 
from stock_log
order by 1, sl_date;

That will show that "stock_balance" and "calculated_stock_balance" show identical values.

I.e. you could omit the "stock_balance" column in the base table and use a view with a WINDOW like the above to automatically generate that value.

(As specified otherwise, I'm not sure the partition by material is fitting, you may have to "partition by batch_number, material" if that is relevant.)