JimiJ13
asked on
Transform transactions
Dear Expert,
I have this transaction table
control stock_code action_date qty price
IA 001 1001 2014-03-31 -1 10
RR 001 1001 2014-03-01 5 10
RR 002 1001 2014-04-03 5 9
WIR 001 1001 2014-03-05 -2 10
How can Transform its presentation into this?:
control stock_code action_Date qty value run_qty run_val price
---------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
RR 001 1001 2014-03-01 5 50 5 50 10
WIR 001 1001 2014-03-05 -2 -20 3 30 10
IA 001 1001 2014-03-31 -1 -10 2 20 10
RR 002 1001 2014-04-03 5 45 7 65 9
Any offered solution would be highly appreciated.
I have this transaction table
control stock_code action_date qty price
IA 001 1001 2014-03-31 -1 10
RR 001 1001 2014-03-01 5 10
RR 002 1001 2014-04-03 5 9
WIR 001 1001 2014-03-05 -2 10
How can Transform its presentation into this?:
control stock_code action_Date qty value run_qty run_val price
---------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
RR 001 1001 2014-03-01 5 50 5 50 10
WIR 001 1001 2014-03-05 -2 -20 3 30 10
IA 001 1001 2014-03-31 -1 -10 2 20 10
RR 002 1001 2014-04-03 5 45 7 65 9
Any offered solution would be highly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent!
Isn't this a duplicate of this question? https://www.experts-exchange.com/questions/28422940/Inventory-Stock-movement.html
(Since you used the output I gave you in that question here :) )
(Since you used the output I gave you in that question here :) )
ASKER
Hi Steve,
I need an Inventory Tracking solution, and I am very grateful that your partial solution leads me to completion by looking for somebody to provide the remaining part (the transform in MS SQL). I can't wait, but I need quick solution to meet my delivery schedule. I don't want to get stuck so I need to take an immediately action, and I got my solution on time.
Thanks.
I need an Inventory Tracking solution, and I am very grateful that your partial solution leads me to completion by looking for somebody to provide the remaining part (the transform in MS SQL). I can't wait, but I need quick solution to meet my delivery schedule. I don't want to get stuck so I need to take an immediately action, and I got my solution on time.
Thanks.
If you check the original response, a full solution was given for SQL Server at the beginning of the post (it just had extra bits at the bottom).
The output I showed you was output from an actual query written on SQL Server.
Regardless, glad you got your solution.
The output I showed you was output from an actual query written on SQL Server.
Regardless, glad you got your solution.
ASKER
Hi Steve,
I'm very sorry for my wrong presumption. Your last post mixed me up and made me to believe that the it was supposed to be my solution not the latter, and I did not even test because it gave me an error when I pasted on the view window. Please see image.
InventoryTracking.jpg
I'm very sorry for my wrong presumption. Your last post mixed me up and made me to believe that the it was supposed to be my solution not the latter, and I did not even test because it gave me an error when I pasted on the view window. Please see image.
InventoryTracking.jpg
where's the error?
ASKER
Hi Brian,
Do you have any idea why this is having a problem?
CREATE TABLE #Transaction
(
[control] VARCHAR(10),
stock_code VARCHAR(10),
action_date DATE,
qty INT,
price MONEY
)
INSERT INTO #Transaction
(
control,
stock_code,
action_date,
qty,
price
)
VALUES
('IA 001', '1001', '3/31/2014', -1, 10),
('IA 002', '1001', '4/31/2014', -4, 10),
('IA 003', '1001', '5/31/2014', 5, 10),
('IA 004', '1001', '6/31/2014', -1, 10),
('RR 001', '1001', '3/1/2014', 5, 10),
('RR 002', '1001', '4/3/2014', 5, 9),
('RR 003', '1001', '5/1/2014', 5, 10),
('RR 004', '1001', '5/3/2014', 5, 9),
('WIR 001', '1001', '3/5/2014', -2, 10),
('WIR 002', '1001', '4/5/2014', -2, 10),
('WIR 003', '1001', '5/5/2014', -4, 10),
('WIR 004', '1001', '6/5/2014', -5, 10)
SELECT [control], stock_code, action_date, qty, qty*price AS value,
(SELECT SUM(qty) FROM #Transaction WHERE action_date <= T.action_date) AS run_qty,
(SELECT SUM(qty * price) FROM #Transaction WHERE action_date <= T.action_date) AS run_val,
price
FROM #Transaction AS T
ORDER BY action_date
This is the error:
Msg 241, Level 16, State 1, Line 10
Conversion failed when converting date and/or time from character string.
Thanks.
Do you have any idea why this is having a problem?
CREATE TABLE #Transaction
(
[control] VARCHAR(10),
stock_code VARCHAR(10),
action_date DATE,
qty INT,
price MONEY
)
INSERT INTO #Transaction
(
control,
stock_code,
action_date,
qty,
price
)
VALUES
('IA 001', '1001', '3/31/2014', -1, 10),
('IA 002', '1001', '4/31/2014', -4, 10),
('IA 003', '1001', '5/31/2014', 5, 10),
('IA 004', '1001', '6/31/2014', -1, 10),
('RR 001', '1001', '3/1/2014', 5, 10),
('RR 002', '1001', '4/3/2014', 5, 9),
('RR 003', '1001', '5/1/2014', 5, 10),
('RR 004', '1001', '5/3/2014', 5, 9),
('WIR 001', '1001', '3/5/2014', -2, 10),
('WIR 002', '1001', '4/5/2014', -2, 10),
('WIR 003', '1001', '5/5/2014', -4, 10),
('WIR 004', '1001', '6/5/2014', -5, 10)
SELECT [control], stock_code, action_date, qty, qty*price AS value,
(SELECT SUM(qty) FROM #Transaction WHERE action_date <= T.action_date) AS run_qty,
(SELECT SUM(qty * price) FROM #Transaction WHERE action_date <= T.action_date) AS run_val,
price
FROM #Transaction AS T
ORDER BY action_date
This is the error:
Msg 241, Level 16, State 1, Line 10
Conversion failed when converting date and/or time from character string.
Thanks.
ASKER
Brian,
Never mind, there are no 4/31 and 6/31.
Anyway, I think my next question is how to get my Inventory level per item at the end of every month - without showing details of transactions.
Your help would be gladly appreciated.
Thanks.
Never mind, there are no 4/31 and 6/31.
Anyway, I think my next question is how to get my Inventory level per item at the end of every month - without showing details of transactions.
Your help would be gladly appreciated.
Thanks.
('IA 002', '1001', '4/31/2014', -4, 10),
('IA 004', '1001', '6/31/2014', -1, 10),
31st of April and June?
(it can't convert those to dates)
('IA 002', '1001', '4/30/2014', -4, 10),
('IA 004', '1001', '6/30/2014', -1, 10),
('IA 004', '1001', '6/31/2014', -1, 10),
31st of April and June?
(it can't convert those to dates)
('IA 002', '1001', '4/30/2014', -4, 10),
('IA 004', '1001', '6/30/2014', -1, 10),
apologies you had already caught the dates
If you are using sql server 2012 there is a more efficient way. What version of SQL Server are you using?
If you are using sql server 2012 there is a more efficient way. What version of SQL Server are you using?
--- before sql 2012
SELECT
[control]
, stock_code
, action_date
, qty
, qty * price AS value
, run_qty
, run_val
, price
FROM (
SELECT
[control]
, stock_code
, action_date
, qty
, qty * price AS value
, (
SELECT
SUM(qty)
FROM Transactions
WHERE action_date <= T.action_date
)
AS run_qty
, (
SELECT
SUM(qty * price)
FROM Transactions
WHERE action_date <= T.action_date
)
AS run_val
, price
, datepart(day,action_date)
AS act_day
, max(datepart(day,action_date)) over (partition by year(action_date), month(action_date))
AS act_max_day
FROM Transactions AS T
) AS T2
WHERE act_day = act_max_day
ORDER BY
action_date
;
-- sql 2012 +
SELECT
[control]
, stock_code
, action_date
, qty
, qty * price AS value
, run_qty
, run_val
, price
FROM (
SELECT
[control]
, stock_code
, action_date
, qty
, qty * price AS value
, SUM(qty) OVER (ORDER BY action_date) AS run_qty
, SUM(qty * price) OVER (ORDER BY action_date) AS run_val
, price
, datepart(day,action_date) AS act_day
, max(datepart(day,action_date)) over (partition by year(action_date), month(action_date)) AS act_max_day
FROM Transactions
) AS T
WHERE act_day = act_max_day
ORDER BY
action_date
;
both of those produce this result:
| CONTROL | STOCK_CODE | ACTION_DATE | QTY | VALUE | RUN_QTY | RUN_VAL | PRICE |
|---------|------------|-------------|-----|-------|---------|---------|-------|
| IA 001 | 1001 | 2014-03-31 | -1 | -10 | 2 | 20 | 10 |
| IA 002 | 1001 | 2014-04-30 | -4 | -40 | 1 | 5 | 10 |
| IA 003 | 1001 | 2014-05-31 | 5 | 50 | 12 | 110 | 10 |
| IA 004 | 1001 | 2014-06-30 | -1 | -10 | 6 | 50 | 10 |
available at: http://sqlfiddle.com/#!6/92273/16
ASKER
Hi PortletPaul,
I tried your code but it doesn't gave the correct results. Please see below with 2 item codes:
I tried your code but it doesn't gave the correct results. Please see below with 2 item codes:
CREATE TABLE #Transaction1
(
[control] VARCHAR(10),
stock_code VARCHAR(10),
action_date DATE,
qty INT,
price MONEY
)
INSERT INTO #Transaction1
(
control,
stock_code,
action_date,
qty,
price
)
VALUES
('IA 001', '1001', '3/31/2014', -1, 10),
('IA 002', '1001', '4/30/2014', -4, 10),
('IA 003', '1002', '5/30/2014', 5, 10),
('IA 004', '1001', '6/30/2014', -1, 10),
('RR 001', '1001', '3/1/2014', 5, 10),
('RR 002', '1001', '4/3/2014', 5, 9),
('RR 003', '1002', '5/1/2014', 5, 10),
('RR 004', '1001', '5/3/2014', 5, 9),
('WIR 001', '1001', '3/5/2014', -2, 10),
('WIR 002', '1001', '4/5/2014', -2, 10),
('WIR 003', '1002', '5/5/2014', -4, 10),
('IA 005', '1001', '6/30/2014', 0, 0),
('RR 005', '1001', '6/30/2014', 0, 0),
('WIR 005', '1001', '6/30/2014', 0, 0),
('WIR 004', '1001', '6/5/2014', -5, 10)
SELECT
[control]
, stock_code
, action_date
, qty
, qty * price AS value
, run_qty
, run_val
, price
FROM (
SELECT
[control]
, stock_code
, action_date
, qty
, qty * price AS value
, (
SELECT
SUM(qty)
FROM #Transaction1
WHERE action_date <= T.action_date
)
AS run_qty
, (
SELECT
SUM(qty * price)
FROM #Transaction1
WHERE action_date <= T.action_date
)
AS run_val
, price
, datepart(day,action_date)
AS act_day
, max(datepart(day,action_date)) over (partition by year(action_date), month(action_date))
AS act_max_day
FROM #Transaction1 AS T
) AS T2
WHERE act_day = act_max_day
ORDER BY
action_date
;
Drop Table #Transaction1;
Thanks.
as you are the person who decides what is correct I believe you, but what is the correct result?
please supply the expected result
also, in the real data does action_date also hold time information?
i.e. if we have to decide which record on the last day of a month, how do we do that?
please supply the expected result
also, in the real data does action_date also hold time information?
i.e. if we have to decide which record on the last day of a month, how do we do that?
here are different results because I have changed the "partition by" conditions. I don't know if any are right as I don't have that guidance from you. Refer to lines 35 and 103 for the differences
-- stock_code, control
SELECT
[control]
, stock_code
, action_date
, qty
, qty * price AS value
, run_qty
, run_val
, price
FROM (
SELECT
[control]
, stock_code
, action_date
, qty
, qty * price AS value
, (
SELECT
SUM(qty)
FROM Transactions
WHERE action_date <= T.action_date
)
AS run_qty
, (
SELECT
SUM(qty * price)
FROM Transactions
WHERE action_date <= T.action_date
)
AS run_val
, price
, datepart(day,action_date)
AS act_day
, max(datepart(day,action_date)) over (partition by stock_code, control, year(action_date), month(action_date))
AS act_max_day
FROM Transactions AS T
) AS T2
WHERE act_day = act_max_day
ORDER BY
action_date
, stock_code
, control
**[Results][2]**:
| CONTROL | STOCK_CODE | ACTION_DATE | QTY | VALUE | RUN_QTY | RUN_VAL | PRICE |
|---------|------------|-------------|-----|-------|---------|---------|-------|
| RR 001 | 1001 | 2014-03-01 | 5 | 50 | 5 | 50 | 10 |
| WIR 001 | 1001 | 2014-03-05 | -2 | -20 | 3 | 30 | 10 |
| IA 001 | 1001 | 2014-03-31 | -1 | -10 | 2 | 20 | 10 |
| RR 002 | 1001 | 2014-04-03 | 5 | 45 | 7 | 65 | 9 |
| WIR 002 | 1001 | 2014-04-05 | -2 | -20 | 5 | 45 | 10 |
| IA 002 | 1001 | 2014-04-30 | -4 | -40 | 1 | 5 | 10 |
| RR 003 | 1002 | 2014-05-01 | 5 | 50 | 6 | 55 | 10 |
| RR 004 | 1001 | 2014-05-03 | 5 | 45 | 11 | 100 | 9 |
| WIR 003 | 1002 | 2014-05-05 | -4 | -40 | 7 | 60 | 10 |
| IA 003 | 1002 | 2014-05-30 | 5 | 50 | 12 | 110 | 10 |
| WIR 004 | 1001 | 2014-06-05 | -5 | -50 | 7 | 60 | 10 |
| IA 004 | 1001 | 2014-06-30 | -1 | -10 | 6 | 50 | 10 |
| IA 005 | 1001 | 2014-06-30 | 0 | 0 | 6 | 50 | 0 |
| RR 005 | 1001 | 2014-06-30 | 0 | 0 | 6 | 50 | 0 |
| WIR 005 | 1001 | 2014-06-30 | 0 | 0 | 6 | 50 | 0 |
**Query 2**:
-- stock code
SELECT
[control]
, stock_code
, action_date
, qty
, qty * price AS value
, run_qty
, run_val
, price
FROM (
SELECT
[control]
, stock_code
, action_date
, qty
, qty * price AS value
, (
SELECT
SUM(qty)
FROM Transactions
WHERE action_date <= T.action_date
)
AS run_qty
, (
SELECT
SUM(qty * price)
FROM Transactions
WHERE action_date <= T.action_date
)
AS run_val
, price
, datepart(day,action_date)
AS act_day
, max(datepart(day,action_date)) over (partition by stock_code, year(action_date), month(action_date))
AS act_max_day
FROM Transactions AS T
) AS T2
WHERE act_day = act_max_day
ORDER BY
action_date
, stock_code
, control
**[Results][3]**:
| CONTROL | STOCK_CODE | ACTION_DATE | QTY | VALUE | RUN_QTY | RUN_VAL | PRICE |
|---------|------------|-------------|-----|-------|---------|---------|-------|
| IA 001 | 1001 | 2014-03-31 | -1 | -10 | 2 | 20 | 10 |
| IA 002 | 1001 | 2014-04-30 | -4 | -40 | 1 | 5 | 10 |
| RR 004 | 1001 | 2014-05-03 | 5 | 45 | 11 | 100 | 9 |
| IA 003 | 1002 | 2014-05-30 | 5 | 50 | 12 | 110 | 10 |
| IA 004 | 1001 | 2014-06-30 | -1 | -10 | 6 | 50 | 10 |
| IA 005 | 1001 | 2014-06-30 | 0 | 0 | 6 | 50 | 0 |
| RR 005 | 1001 | 2014-06-30 | 0 | 0 | 6 | 50 | 0 |
| WIR 005 | 1001 | 2014-06-30 | 0 | 0 | 6 | 50 | 0 |
[1]: http://sqlfiddle.com/#!6/9046e/3
goodness, this question has been answered!
sorry, but this should be a new question
apologies to all concerned.
sorry, but this should be a new question
apologies to all concerned.
I'm not sure how "item level" connects to control and stock code in your example, but you need to aggregate on a monthly grain:
SELECT /* [control], */
stock_code,
EOMONTH(action_date),
SUM(qty) AS monthly_qty,
MIN(price) AS price
FROM #Transaction AS T
GROUP BY /* [control], */
stock_code,
EOMONTH(action_date);
ASKER
Hi PortletPaul,
I have opened new question related to this at https://www.experts-exchange.com/questions/28438497/Tracking-Inventory-with-multiple-items.html
For the main time, there's an error in your proposed solution. Please refer to the image attached.
Thanks.
I have opened new question related to this at https://www.experts-exchange.com/questions/28438497/Tracking-Inventory-with-multiple-items.html
For the main time, there's an error in your proposed solution. Please refer to the image attached.
Thanks.
E.g.
Open in new window
This will result in a more optimzed execution plan: