0

I have an assignment to make an inventory report only based on the transactional data table.

For example, I have the table like this, where ProductType determines whether the stock is going in or out (+ for stock in and - for out)

ProductID Quantity ProductType Date
1 630 1 2021-10-11
1 630 -1 2021-10-11
1 630 1 2021-10-12
1 630 1 2021-10-12
1 430 -1 2021-10-13

And based on the data I want to make it look like this based on the parameter inputted which is productid, beginning date, and end date. Assuming the beginning quantity is 0.

ProductID Date Beginning Quantity Quantity In Quantity Out Ending Quantity
1 2021-10-11 0 630 0 630
1 2021-10-11 630 0 630 0
1 2021-10-12 630 630 0 630
1 2021-10-12 630 630 0 1260
1 2021-10-13 1260 0 430 830

Any help will be much appreciated, thank you!

Dale K
  • 25,246
  • 15
  • 42
  • 71
ZZZZZZZZZ
  • 11
  • Please tag the DBMS you are using, an answer to your question could be product-specific. – ahmed Jun 19 '23 at 03:30
  • Okay i will, thankyou! – ZZZZZZZZZ Jun 19 '23 at 03:50
  • 1
    MySQL <> SQL Server, which one you are using? Also for this kind of data, it is used to use DateTime not date, as you can see, on 2021-10-12 you have two rows with ProductType = 1, so which one of them is coming first? – ahmed Jun 19 '23 at 04:15
  • Anyway, you could use a [running sum](https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server) of `Quantity*ProductType` to find the values of `Beginning Quantity` and `Ending Quantity`. – ahmed Jun 19 '23 at 04:21
  • My bad ,it's SQL server. The data that i got using datetime but all the time is 00:00:00. – ZZZZZZZZZ Jun 19 '23 at 04:27

1 Answers1

1

One example of how to do this. If you have transaction ID then use it instead of row_number() because it is just a way how to split the amounts within one day.

create table Products (ProductID int, Quantity int, ProductType int, [Date] datetime)
insert into Products
values
(1, 630,    1,  '2021-10-11'),
(1, 630,    -1, '2021-10-11'),
(1, 630,    1,  '2021-10-12'),
(1, 630,    1,  '2021-10-12'),
(1, 430,    -1, '2021-10-13')

declare @productID int = 1,
        @Beg_Dte date = '2021-10-01',
        @End_Dte date = '2021-10-31';

with input_data as(
select 
  row_number()over(partition by ProductID order by [Date]) as ID, 
  ProductID, 
  [Date], 
  Quantity*ProductType as Qty,
  iif(ProductType>0,Quantity,0) as [Quantity In],
  iif(ProductType<0,Quantity,0) as [Quantity Out],
  lag(Quantity*ProductType,1,0)over(partition by ProductID order by ProductID,[Date]) as Beg
from Products
)
select ID, ProductID, [Date], 
       sum(Beg)over(partition by ProductID order by ID) as [Beginning Quantity], 
       [Quantity In], 
       [Quantity Out], 
       sum(Beg)over(partition by ProductID order by ID)+Qty as [Ending Quantity] 
from input_data
where ProductID = @productID
  and [Date] between @Beg_Dte and @End_Dte

enter image description here

In addition, recursive CTE or CASE statements also should work.

If you do not have transaction ID, think about the another representation of the result set. Maybe it is better to show only one row for each day. It should be easier to organise. Something like this:

ProductID Date Beginning Quantity Quantity In Quantity Out Ending Quantity
1 2021-10-11 0 630 630 0
1 2021-10-12 0 1260 0 1260
1 2021-10-13 1260 0 430 830

By the way, there is an error in this line. Beginning Quantity should be zero.

ProductID Date Beginning Quantity Quantity In Quantity Out Ending Quantity
1 2021-10-12 630 630 0 630
Olesia Dudareva
  • 331
  • 1
  • 1
  • 6